Understanding, Analysing and Troubleshooting Deadlocks – Part1

A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.

In simple language, Deadlock is a situation when two or more processes are waiting to acquire lock on resource(s) which are already blocked by opposite participant process, hence results in a never ending locking situation. A deadlock will never resolve itself and the processes will keep on waiting forever.

That’s the reason SQLServer has Deadlock monitor\detector, It keep on checking such situation and if a deadlock is found it identifies one process as victim and kill that process so that the lock(s) can be released and the other process can acquire the required locks & complete the job. Deadlock monitor is an internal process which runs every 5 second by default and if your system is experiencing frequent deadlocks the time interval between different checks can be changed internally by SQL Engine and it can go as low as 100 MS.

Now you must be wondering which process will be identified as victim and on what basis?Deadlock Detector does not select a process randomly, there are three rules\conditions:

1. A process cannot be identified as victim and killed if the deadlock priority is set to High.
2. Log Generation: the process with least amount of log generation during the transaction will be identified as victim.
3. It also checks whether the process is a user process or a system process.

Generally a deadlock occurs when different transactions try to access same set of objects\resources in different order. For Example:

• User A acquires a share lock on Table1.
• UserB acquires a share lock on Table2.
• UserA now requests an exclusive lock on Table2, and is blocked until UserB finishes and releases the share lock it has on Table2.
• UserB now requests an exclusive lock on Table1, and is blocked until UserA finishes and releases the share lock it has on Table1.
• UserA cannot complete until UserB completes, but UserB is blocked by UserA.
• UserA has a dependency on UserB, and UserB closes the circle by having a dependency on UserA.

 

Don’t get confused between Deadlock with Blocking – in blocking the resources wait in a queue and the queue gets cleared once the task completes hence the first transaction releases the held locks so that second transaction can acquire lock but in deadlock it becomes a cyclic lock.

Once a deadlock occurred on a transaction\Application SQL Server throws an ugly 1205 Error with the following message: Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

If you are new to SQLServer or you don’t have any prior experience with Deadlocks you may go crazy after reading the error, since the message tells that an error has occurred but still suggests\prompts you to rerun your transaction without asking you to fix something.

The reason is that once the deadlock victim was killed the other transaction got a chance to acquire the required locks and complete the transaction, so if you rerun the transaction after a small delay probably you won’t get another deadlock and this time your transaction can complete.

In next Parts we’ll see how to get more info about Deadlock that just occurred in your environment (yes you can actually get more info), how to get same Deadlock Graph (XML Information as well as a GUI Representation) using Profiler  and how to interpret it.

Happy Learning 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox
Follow @SQLDB