Last month, one of my client consult me for an incident in its production sql server. They found a stored procedure that being called by multiple concurrent application threads raising deadlock errors frequently, and more seriously the deadlock errors were accumulated. Which means the sql server Lock Monitor (deadlock detection) resolve the deadlocks (by choosing one of the thread, which is the least expensive to roll back, as a deadlock victim, and kill it) not fast enough as the new threads spawned by the client application. (Another ref.: https://learn.microsoft.com/en-us/answers/questions/988682/sql-server-deadlock-detection-only-kills-one-victi.html). This case was very hard to troubleshoot, because the it is a single stored procedure, meaning the threads are accessing tables/objects in exactly the same logical order. Below shows the deadlock graph that being captured:
wSo I asked my client why the application need to set repeatable read to call that stored procedure. And you guess what they said? They told me that application was developed by outsource software vendor, and it's end-of-support now. In order to convince my client that repeatable read is the problem cause, I showed them a simple demo provided in Microsoft Tech Community:
