0

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
  • What are trying to accomplish? – Max Vernon Aug 15 '17 at 21:34
0

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:

Other links:

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.