Deadlock

What is a Deadlock?

Deadlocks happen when conflicting locks are taken by two or more processes.  In rare scenarios, I have heard of deadlocks caused by SQL Statements executing in parallel however I cannot reproduce it. Neither side is willing to step aside for the other processes.  Think of going shopping for furniture and two people (Person A and Person B) go in at same time.  Unknown to them they both want the same goods (Good A and Good B).  One starts searching from right another person starts searching from left side of the store.  Person A finds good A (Exclusive Lock) and starts looking for Good B (Shared Look).  But while Person A was searching for Good A, Person B found Good B (Exclusive Lock) and starts looking for Good A (Shared Lock).  When they find the other good they realize it is already reserved and cannot have it, neither Person A nor Person B is willing to let the other person have both the Goods. Therefore we are stuck in a deadlock.

Another Deadlock definition in SQL Server

In terms of SQL Server, a deadlock occurs when two (or more) processes lock the separate resource. Under these circumstances, each process cannot continue and begins to wait for others to release the resource. However, the SQL engine understands that this contention would never end with the help of the lock manager warning and then it decides to kill one process to solve this conflict problem so that the other process can be completed. The killed process is called the deadlock victim.

In SQL Server to prevent this stalemate (i.e. a deadlock) from filling up the system, we have a Deadlock Monitor thread that is running in the background to “help” resolve deadlocks.

What is termed as a deadlock victim?

In case of deadlock, SQL Server will pick one task/session, whichever will be least expensive to rollback and kill it. This session/task is termed as a deadlock victim.

How do we monitor deadlocks?

Process	Comments
Windows Performance Monitor Object: SQLServer: Locks Counter: Number of Deadlocks/sec Instance: _Total	This provides all the deadlocks that have happened on your server since the last restart.  We can look at this counter using the following SQL Statement: SELECT cntr_value AS NumOfDeadLocks   FROM sys.dm_os_performance_counters  WHERE object_name = 'SQLServer:Locks'    AND counter_name = 'Number of Deadlocks/sec'    AND instance_name = '_Total'
Trace Flags 1204 and 1222	Trace flag 1204 has existed since at least SQL Server 2000.  And Trace Flag 1222 was introduced in SQL Server 2005.Both output Deadlock Information to the SQL Server ERRORLOG.
SQL Server Profiler/Server Side Trace Event Class: Locks Event Name: Deadlock Graph	Gives an XML Graph like the example above.  Very easy to read and figure out what is going on.
Extended Events	The new way to do monitoring in SQL Server.  Extended Events are eventually going to replace the SQL Server Profiler all together (i.e. SQL Server Profiler is on the Deprecated Feature list).  It produces the same XML graph as SQL Server Profiler, and is lighter in performance impact
System Health	This is a “new” default trace, but it’s unlike the default trace that had limited the amount of information being tracked and could not be modified.  We can modify a system health definition, which is built on a Extended Events.  But unlike the default trace, the System Health tracks the Deadlocks that have occurred recently.  So we can pull this information from the System Health for analysis instead of implementing our own Extended Events monitoring.

Deadlock Monitoring Using the Extended Events Trace

1.      Navigate to SQL Server

2.      Management.

3.      Extended Events

4.      Sessions.

5.      Right Click on Sessions, Go to New Session Wizard.

6.      Enter in Session Name “Deadlock_Monitor” click Next.

7.      Select Do not use a template and click Next (Templates are like SQL Server Profiler Templates, where you have defaults to start with, but none of the templates fit our needs).

8.      This is where we define what events we want to capture. Type “deadlock” in Event Library.  We get following image.

9.      Click on “xml_deadlock_report” and click “>” to add it.  Click Next to continue.

10.    Now we need to define what columns we want to capture, for now just click Next.

11.    Define any filters needed. For now we’ll ignore this setting.  Click Next to continue.

12.    Select “Save data to file”, enter file name, size, etc.  Click Next to continue.

13.    Review all the configurations and click Finish to setup and start the session.

14.    Now you can start the capture and watch it live.

 Deadlocks can be traced by turning on two specific flags:

-- Activate deadlock tracing

dbcctraceon (1204, 3605, -1)

go

dbcc tracestatus(-1)

go

-- Turn off deadlock tracing

Dbcc traceoff(1204, 3605)

go

DEADLOCK PREVENTION

Deadlocks can be prevented by one or more of the following methods:

·        Adding missing indexes to support faster queries

·        Dropping unnecessary indexes which may slow down INSERTs for example

·      Redesigning indexes to be "thinner", for example, removing columns from composite indexes or making table columns "thinner" (see below)

·        Adding index hints to queries

·        Redesigning tables with "thinner" columns like smalldatetime vs. datetime or smallint vs. int

·        Modifying the stored procedures to access tables in a similar pattern

·        Keeping transactions as short and quick as possible: "mean & lean"

·        Removing unnecessary extra activity from the transactions like triggers

·        Removing JOINs to Linked Server (remote) tables

·     Implementing regular index maintenance; usually weekend schedule suffices; use FILLFACTOR = 80 for dynamic tables

·        Setting MAXDOP=1 solves deadlocking in some cases

“Deadlock graph”, “Lock:Deadlock” and “Lock:Deadlock Chain” events  from profiler

Capturing the Deadlocks with Extended Events

Extended events are used to collect and monitor various events and information from SQL Server. With the help of the extended events, we can easily capture details when a deadlock occurred.

At first, we will launch SQL Server Management Studio (SSMS) and navigate to Session, which is located under the Management folder. Right-click on the Sessions and select the New Session Wizard.

We will click the Next button and skip to the next screen on the Introduction screen.

In the Set Session Properties screen, we will give a name to the extended event and click the Next button.

On the Choose Template screen, we will select the Do not use a template option and click the Next button.

On the Select Events To Capture screen, we will add the following events from the Event library to Selected events list.

• database_xml_deadlock_report
• lock_deadlock
• lock_deadlock_chain
• scheduler_monitor_deadlocks_ring_buffer_recorded
• xml_deadlock_report
• xml_deadlock_report_filtered

On the Capture Global Fields screen, we will select global events that will be captured with the events:

• client app name
• client connection id
• client hostname
• database id
• database name
• nt username
• sql text
• username

On the Specify Session Data Storage screen, we will set the target_file path that the events will be stored and we also set the maximum size of the event file.

In this step, we will click the Finish button and create an extended event.

On the final step, we will check the Start the event session immediately option and click the Close button. This will cause the extended event to capture deadlocks occurring on the SQL Server.

Now, we will re-execute deadlock simulating queries at the same time on the separated query windows and generate a deadlock error again.

We will right-click on the created extended event and select the View Target Data… to analyze the captured deadlock details.

On this screen, we can display the deadlock details.

When we click the xml_report field on the xml_deadlock_report event, the XML report of the deadlock will be opened. This report can be very helpful in understanding the details of the deadlock.

Also, in the xml_deadlock_report event, we can see the deadlock graph and it offers a virtual representation of the deadlock.

Preventing Deadlocks in SQL Server

There is no exact and clear resolving formula for the deadlocks because all deadlocks might have unique characteristics. However, it is significant to understand the circumstances and the situation under which these deadlocks have occurred because this approach will broadly help to resolve them. After then, the following solution recommendations might help.

• Access the resources in the same order
• Write the shortest transactions as much as possible and lock the resource for the minimum duration
• Consider using READ COMMITTED SNAPSHOT ISOLATION and SNAPSHOT ISOLATION levels
• Limiting the usage of the cursors
• Design more normalized databases
• Avoid poorly-optimized queries