SQL Server Deadlock Detection only kills one victim per each run?

Question

My company has a production SQL Server 2019 Enterprise edition CU16. We found it has so many deadlocks that the oldest pair of deadlock processes have existed for one hour. I know SQL Server has an internal process called "deadlock detection" or "lock monitor", which periodically (as I know, runs every 5 secs) check for deadlock and automatically resolve deadlock by killing one of the deadlocked processes (the victim). My question is does this "deadlock detection" background process only kill one and only one victim each time it runs? So if there are so many concurrent deadlocks, then the detection process will not be fast enough to kill?

Answer 1

Yes, I've seen this. I recall, we had situations with, say, 12 processes in a deadlock. SQL Server selected one as a deadlock victim. Which left 11 processes deadlocked and so on.

It kind of make sense. With some luck, the deadlock will be really resolved. when you take out one of the players But there can also be other complications. Maybe there is some other processes that was blocked by the deadlock victim, but not part of the deadlock. That process can now proceed - but only to be entangled into that deadlock.

Note that if you have multiple deadlocks that are independent of each other, SQL Server on each round will kill one process in each deadlock.

Also, I believe that SQL Server may decrease that five-second interval if there are plenty of deadlocks and check for deadlocks more often. But when we had our 12-process deadlocks, that did not happen.

Answer 2

Hi @Peter Lee ,

You are correct that SQL Server is able to detect deadlocks with its deadlock detection mechanism. Deadlock detection is performed by the Lock Monitor, a system thread that periodically searches for all tasks in SQL Server. The default time interval is 5 s.
When a deadlock is detected, the database engine ends the deadlock by selecting one of the threads as the deadlock victim. The database engine terminates the thread's currently executing batch, rolls back the deadlock victim's transaction, and returns the 1205 error to the application. Rolling back the deadlock victim will release all locks held by the transaction. This will allow the other thread's transaction to unlock and continue running. By default, the database engine selects the session running the transaction with the least rollback overhead as the deadlock victim.
Although deadlocks are mutual waits between two or more processes, the impact on the end user is often less severe in terms of performance impact as SQL Server has a database engine deadlock detection scheme that eliminates an existing deadlock for at least 5 s.

Best regards
Niko

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.