SQL Server deadlock on read uncommitted isolation level

Question

I get error message "Transaction (Process ID 60) was deadlocked on lock resources with another process ...". I have two simple queries like:

Query 1:

BEGIN try    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN tran  
 
    update RestaurantAccount set CreatedOn = GETDATE()
        where Id = 1
    
    WAITFOR DELAY '00:00:6'
    
    update RestaurantInvoice set CreatedOn = GETDATE()
        where Id = 1
 
commit tran    

END try    
BEGIN catch    

IF(@@TRANCOUNT > 0)    
rollback tran    
 
          SELECT  
             cast(1 as bit) as hasError
            ,ERROR_LINE() AS ErrorLine  
            ,(isnull(ERROR_MESSAGE(),'') + isnull(ERROR_PROCEDURE(),'')) AS ErrorMessage
END catch

Query 2:

BEGIN try    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN tran  
 
    update RestaurantInvoice set CreatedOn = GETDATE()
        where Id = 1
    
    WAITFOR DELAY '00:00:6'
    
    update RestaurantAccount set CreatedOn = GETDATE()
        where Id = 1

commit tran    

END try    
BEGIN catch    

IF(@@TRANCOUNT > 0)    
rollback tran    

         SELECT  
             cast(1 as bit) as hasError
            ,ERROR_LINE() AS ErrorLine  
            ,(isnull(ERROR_MESSAGE(),'')  ) AS ErrorMessage

END catch

I set the transaction isolation level to read uncommitted for both, I run the first query, and immediately run the second one, but I still get the deadlock error. As I know, there should be no lock with read uncommitted isolation level. So, what is the reason of deadlock? (I use SQL Server 2014)

Check the deadlock graph to see what resources are causing the issue. — Dale K, Jun 01 '23 at 09:01
*"As I know, there should be no lock with read uncommitted isolation level."* This isn't true. You attempt to `UPDATE` the same rows in both statements, read uncommitted doesn't help with that. The second batch's `UPDATE` won't be able to `UPDATE` `RestaurantAccount` because the first batch has locked the row, and the first batch can't `UPDATE` `RestaurantInvoice` because the second batch has locked it. The behaviour is completely expected because neither statement can continue until the other does, and so are at an impasse. — Thom A, Jun 01 '23 at 09:04
What are you expecting `READ UNCOMMITTED` to do when it tries to `UPDATE` a row that is locked by another transaction? Just *not* do the `UPDATE` or something..? — Thom A, Jun 01 '23 at 09:07
Read uncommitted only "helps" with reader/writer deadlocks. Not writer/writer deadlocks. And snapshot isolation is the preferred way of avoiding reader/writer deadlocks these days — Martin Smith, Jun 01 '23 at 09:10
Related reading: [Update statements with set transaction isolation level read uncommitted](https://dba.stackexchange.com/questions/147624/update-statements-with-set-transaction-isolation-level-read-uncommitted) — Thom A, Jun 01 '23 at 09:16
Why on earth would you ever use `READ UNCOMMITTED` anyway? It can cause all manner of strange and incorrect results (for example it could double read a row, which you then join to something else and double up your whole resultset) — Charlieface, Jun 01 '23 at 12:02

score 2 · Answer 1 · edited Jun 01 '23 at 17:37

As the name says "READ UNCOMMITTED" apply to READ no writes (INSERT, UPDATE, DELETE, TRUNCATE, MERGE...).

What Thom says is very true. NOLOCK does not mean that a lock will not be put.

And even in READs, under certain circumstances, NOLOCK is ignored, like when there is ENCRYPT or DECRYPT functions used.

And by the ways, READ UNCOMMITTED can give you false positive rows in the results set. Read the paper I wrote (but it is in french)...

SQL Server deadlock on read uncommitted isolation level

1 Answers1