SQL Server - Exclusive lock taken by a SELECT statement - Why?



  • I'm trying to make sense of why SQL Server (2014) is placing an eXclusive key lock during a deadlock scenario. I've pasted the entire deadlock graph below.

    I'm confused because the deadlock is happening between two SELECT statements, both running as a single READ COMMITTED statement, and NOT within a transaction (so there're no UPDATES etc occurring elsewhere in the same transaction).

    I believe the deadlock occurs because each process is creating a range of key locks on the index and due to the order they are acquired, there is a deadlock occurring. However, if the processes were creating only Shared locks, there should be no deadlock (based on my understanding)!

    So the fundamental question is - why would a eXclusive key lock be acquired by these SELECT statements?

    I expect this just comes down to a misunderstanding on my part about locking. Any advice would be much appreciated.

        
      
        
      
      
        
          
            
    SELECT ID, Message FROM [dbo].[Messages] WHERE MessageType = @Type    
          
          
    Proc [Database Id = 32 Object Id = 1686297067]   
        
        
          
            
    SELECT ID, Message FROM [dbo].[Messages] WHERE MessageType = @Type   
          
          
    Proc [Database Id = 32 Object Id = 1686297067]   
        
      
      
        
          
            
          
          
            
          
        
        
          
            
          
          
            
          
        
      
    
    


  • The first thing you should do is run this:

    SELECT sp = OBJECT_NAME(1686297067, 32);
    

    That'll get you the name of the stored procedure that's part of the deadlock, which SQL Server couldn't be bothered to figure out on its own.

    Which should get you to the statement(s) that were more intimately involved with the deadlock graph you're looking at.

    I've talked about this before here: https://www.erikdarlingdata.com/sql-server/deadlock-graph-frustrations/ , where seemingly unrelated statements are in the deadlock XML.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2