Search code examples
sqlsql-serverlocking

SQL transactions work, but expected to be deadlocked

I have two tables Books and Journals

and I have two transactions:

BEGIN TRAN 
UPDATE Journals SET Pages = 20 

WAITFOR DELAY '00:00:07'

SELECT * FROM Books

Commit;

And

 BEGIN TRAN  
 UPDATE Books SET Pages = 30 

 WAITFOR DELAY '00:00:07'

 SELECT * FROM Journals

Commit;

Then I run these two in two separate windows and they complete just fine! I expect them to be deadlocked. My thoughts are the following:

  1. Transaction A locks Journals while Transaction B locks Books
  2. Transaction A can't read from table Books, since Books is locked for Update
  3. Transaction B can't read from table Journals, since Journals is locked as well

Where am I wrong in my thoughts??

Solution

  • Presumably your database has READ_COMMITTED_SNAPSHOT enabled, then the behaviour is expected. If your database has this enabled then whichever is the first statement to run the SELECT will return the unUPDATEd values for the other table. So, if both tables had the value 10 in every row for the column Pages, then the statement that ran first would return 10 and the other statement would return either 20 or 30.

    If, however, your database does not have READ_COMMITTED_SNAPSHOT enabled, then you will get a deadlock, with an error like so:

    Msg 1205, Level 13, State 45, Line 9 Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    And a deadlock graph like so:
    Image of Deadlock graph

    So the real question is, do you want a deadlock? If so, then is READ_COMMITTED_SNAPSHOT the right choice for you? Only you can answer that.