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
Within a given transaction, completed queries would be considered as if they were committed. READ COMMITTED prevents you from seeing rows written/updated by other transactions before they finish, not by the transaction you're in.
From the Microsoft documentation Transaction Isolation Levels:
It is important to note that the transaction isolation level does not affect a transaction's ability to see its own changes; transactions can always see any changes they make.
Related product documentation:
SET TRANSACTION ISOLATION LEVELOther links: