The Executioner - how is a deadlock victim chosen?

As database professionals we have all read at least a little bit about locks that the database engine uses.  The word locks in database engines have sort of become a swear word but unfairly.  Maybe because locks sounds like blocks we tend to think of locks as a bad thing when really it isn’t.  It is locks that ensure that our data remains consistent which of course is the C in A-C-I-D which of course is essential in ensuring an RDBMS is a viable solution.

The purpose of this blog is not to once again go over all the types of locks because there is plenty of material out there for that but to talk about something that I never really thought about much in the past.  This has to do with the dreaded deadlock.  Again, as database professionals, Im sure we have all seen the classic example of the deadlock.

Ill go over it quickly so we are all on the same page:

• Transaction 1 gets a shared lock on a row.  
• Transaction 2 gets a shared lock on a different row.
• Transaction 1 asks for an exclusive lock on the other row but is blocked by the shared lock that Transaction 2 has on it.
• Transaction 2 ask for an exclusive lock on the other row but is blocked by the shared lock that Transaction 1 has on it
•  Transaction 1 can’t finish until Transaction 2 finishes but  Transaction 2 can’t finish because it is blocked by Transaction 1. 

-Technet

Now the reason I have chosen this topic is because recently an SSIS project that I support failed over night and when I examined the All Executions report found in the Information Services Catalogs node of SSMS, I discovered from the error messages that my package had been the victim of a deadlock that was detected.  The error message goes like this: 

Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I’d never really given it much thought about how it had been chosen as a victim but having recently passed my Oracle Certified Associate certification, I had read a lot about all the various processes that run in an Oracle instance and perform a variety of jobs.  So it occurred to me that there must be a similar type of process that is running automatically in Sql Server that handles deadlock situations so I did a quick google and looked it up.

There is indeed a dedicated thread that performs deadlock detection. 

The following is from Technet:

'Deadlock detection is performed by a lock monitor thread that periodically initiates a search through all of the tasks in an instance of the Database Engine. The following points describe the search process:

• The default interval is 5 seconds.
• If the lock monitor thread finds deadlocks, the deadlock detection interval will drop from 5 seconds to as low as 100 milliseconds depending on the frequency of deadlocks.
• If the lock monitor thread stops finding deadlocks, the Database Engine increases the intervals between searches to 5 seconds.
• If a deadlock has just been detected, it is assumed that the next threads that must wait for a lock are entering the deadlock cycle. The first couple of lock waits after a deadlock has been detected will immediately trigger a deadlock search rather than wait for the next deadlock detection interval. For example, if the current interval is 5 seconds, and a deadlock was just detected, the next lock wait will kick off the deadlock detector immediately. If this lock wait is part of a deadlock, it will be detected right away rather than during next deadlock search.

By default, the Database Engine chooses as the deadlock victim the session running the transaction that is least expensive to roll back. Alternatively, a user can specify the priority of sessions in a deadlock situation using the SET DEADLOCK_PRIORITY statement. DEADLOCK_PRIORITY can be set to LOW, NORMAL, or HIGH, or alternatively can be set to any integer value in the range (-10 to 10). The deadlock priority defaults to NORMAL.'

So if you are the victim of a deadlock anytime soon you will know you aren't being picked on because Sql Server just doesn't like you but there is a good reason for it and it wasn't just an unfair random occurrence.

Thanks for reading,

SQL Canuck