UPDATE-SELECT Consistency Issue In Read Committed In Read Committed isolation level, I would expect the following to deadlock since UPDATE statement will mark the row dirty and SELECT should not be able to read the row before transaction is committed or rolled back. But it seems to work without a deadlock. How does this happen?

BEGIN TRAN

UPDATE TableA
SET Col2 = Col2 + 3
WHERE ID = 50

SELECT Col2 from TableA WHERE ID = 50

COMMIT TRAN
Your SELECT is running in the same process (spid) as your UPDATE, so you can freely select the updated rows - as long as you're in the same process.

Here is a classic deadlock example that you can play with: 

--Classic deadlock example:

Create table dbo.Test1 (col1 int);
Create table dbo.Test2 (col1 int);

Insert into dbo.test1 values(1)
Insert into dbo.test1 values(2) 
Insert into dbo.test1 values(3) 


Insert into dbo.test2 values(1)
Insert into dbo.test2 values(2)
Insert Into dbo.test2 values(3)

--Open a SSMS query session 1 and run these commands
begin tran 
update  dbo.test1 set col1 = 2


--Open another SSMS query session 2 and run these commands
begin tran
update dbo.test2 set col1 = 1 
update  dbo.test1 set col1 = 3


--Go to SSMS query session 1 and run these commands
update dbo.test2 set col1 = 4

rollback