HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Could I have an undetected deadlock?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
haveundetectedcoulddeadlock

Problem

Upon running sp_whoisactive on a server for the first time, I was greeted by something unexpected:

Two sessions had been running for 13 days but both of them seemed to be blocking each other. Taking a look at sys.dm_tran_locks:

The configuration value for the blocked process threshold setting is 10 seconds. Other deadlocks are being successfully resolved on the server via the deadlock monitor.

Information from the @get_locks parameter:


  
    
  
  
    
      
        
        
        
      
    
  

    
        
    
    
        
            
                
            
        
        
            
                
                
                
            
        
    


I've occasionally seen the term "undetected deadlock" but I don't have any direct experience with them. My questions are:

  • Could this be an example of an undetected deadlock? I can't see how the situation gets resolved without intervention from the deadlock monitor thread, but for some reason that hasn't happened yet.



  • Is there anything to do other than update to the latest CU and hope the issue doesn't occur again? The server is currently on 2017 CU10 which I know is a bit out of date.

Solution

From a comment left by David Browne - Microsoft

"Undetected deadlocks" usually means "undetectable deadlocks", eg where session is blocked by a SQL Server lock, and the other is blocked in the client application, or where a session calls xp_cmdshell which a runs a program that connects to the database and becomes blocked by the session waiting on the completion of xp_cmdshell. Having an ordinary deadlock not resolved automatically looks like a bug.

Context

StackExchange Database Administrators Q#299736, answer score: 2

Revisions (0)

No revisions yet.