Deadlocks and NOLOCK, and Transaction Isolation Level…Oh My! (Part 1)

Deadlocks and NOLOCK, and Transaction Isolation Level…Oh My! (Part 1)

Ok, not quite in the same league as L. Frank Baum (sorry!).

The next few posts will cover three topics that are linked and which are essential when building or maintaining a Data Warehouse.

What is a Deadlock?

A deadlock occurs when two processes are competing for exclusive access to a resource but they are unable to obtain exclusive access to it, because the other process is preventing it. This results in a standoff where neither process can proceed. The only way out of a deadlock is for one of the processes to be terminated. SQL Server automatically detects when deadlocks have occurred and takes action by killing one of the processes – known as the victim.

Deadlocks do not only occur on locks, from SQL Server 2012 onward, deadlocks can also happen with memory, MARS (Multiple Active Result Sets) resources, worker threads and resources related to parallel query execution.

However, for the purpose of this basic understanding we will be concentrating on the first which are known as Cycle Locks.

How does a Deadlock Occur?

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. The following graph presents a high level view of a deadlock state where:

  • Task T1 has a lock on resource R1 (indicated by the arrow from R1 to T1) and has requested a lock on resource R2 (indicated by the arrow from T1 to R2).
  • Task T2 has a lock on resource R2 (indicated by the arrow from R2 to T2) and has requested a lock on resource R1 (indicated by the arrow from T2 to R1).
  • Because neither task can continue until a resource is available and neither resource can be released until a task continues, a deadlock state exists.

How SQL Server handles deadlocks

The lock manager in SQL Server automatically searches for deadlocks, this thread which is called the LOCK_MONITOR looks for deadlocks every 5 seconds. It looks at all waiting locks to determine if there are any cycles. When it detects a deadlock it chooses one of the transactions to be the victim and sends a 1205 error to the client which owns the connection. This transaction is then terminated and rolled back which releases all the resources on which it held a lock, allowing the other transaction involved in the deadlock to continue.

If there are a lot of deadlocks SQL Server automatically adjusts the frequency of the deadlock search, and back up to 5 seconds if deadlocks are no longer as frequent.

How do I know if I have a deadlock?

The first sign you will have of a deadlock is the following error message which will be displayed to the user who own the process that was selected as the deadlock victim.

Msg 1205, Level 13, State 51, Line 6
Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The other user whose process was not selected as the victim, will be most likely be completely unaware that their process participated in a deadlock.

In Part 2…

Preventing deadlocks WITH (NO LOCK) or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Leave a Reply

Your email address will not be published. Required fields are marked *