patternsqlMinor
Could I have an undetected deadlock?
Viewed 0 times
haveundetectedcoulddeadlock
Problem
Upon running
Two sessions had been running for 13 days but both of them seemed to be blocking each other. Taking a look at
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
I've occasionally seen the term "undetected deadlock" but I don't have any direct experience with them. My questions are:
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
"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.