Updated July 1, 2016
This article explains what a deadlock is, and how to use DPA to identify and resolve deadlocks.
A deadlock happens when two or more transactions block each other by holding locks on resources that each of the transactions also need.
Transaction 1 cannot complete until Transaction 2 is complete, and Transaction 2 cannot complete until Transaction 1 is complete. This is a cyclical dependency and results in what is called a deadlock. Deadlocks can involve more than two transactions, but two is the most common scenario.
When a deadlock happens, the end-user may see the following error message, which can be a bit scary the first time:
Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
As the error message indicates, one of the sessions was chosen as the deadlock victim, that transaction was terminated, and the results of the transaction were rolled back.
There are a handful of ways for you to know if deadlocks are happening inside your database. You can use trace flags, performance monitor (perfmon), Microsoft SQL Server profiler, extended events, application error handling, or Database Performance Analyzer.
Historically, deadlocks were captured in the SQL error log through the use of trace flags 1204 or 1222 using syntax similar to the following:
DBCC TRACEON (1222, -1)
The output to the SQL error log will look like this:
Using this trace flag requires you to have the trace flag running at all times, making it hard to proactively capture deadlock details. Fortunately, there are other methods available.
There is a performance counter available for showing the number of deadlocks happening. Inside the SQL Server:Locks object, the counter name is Number of Deadlocks/sec, and you can return the total number for the instance.
Alternately, you can query the sys.dm_os_performance_counters dynamic management object. The following query will return details about the number of deadlocks for the instance since the last server restart:
SELECT cntr_value AS NumberOfDeadLocks FROM sys.dm_os_performance_counters WHERE object_name = MSSQL$SQL2016:Locks' AND counter_name = 'Number of Deadlocks/sec' AND instance_name = '_Total'
Using either the Performance Monitor counter object or sys.dm_os_performance_counters query allows you to know that deadlocks are happening (or have happened), but you will not be able to see any details regarding the session transactions involved in the deadlock. To capture those details, you must use an alternate method.
You can use SQL Server Profiler (or a server side trace) to capture the deadlock details. The trace event class is Locks, and the event name is Deadlock Graph. The trace will then capture the deadlock details and display them in an XML graph:
You can also configure the trace to capture the deadlock details as distinct files that you can open with SQL Server Management Studio (SSMS).
Now the trace will output the deadlock files to the location you have defined:
Similar to trace flags, using a server trace requires you to have the trace running at all times, making it hard to proactively capture deadlock details.
SQL Server has an extended event trace running by default, and you can use this trace to look for deadlock details. The name of the session is called System Health and it contains a lot of information by default, including deadlocks. Because it is running by default, there is no need for an administrator to configure trace flags or server side tracing to capture deadlock details.
You can query the System Health session directly in order to mine for deadlock details:
SELECT XEvent.query('(event/data/value/deadlock)') AS DeadlockGraph FROM ( SELECT XEvent.query('.') AS XEvent FROM ( SELECT CAST(target_data AS XML) AS TargetData FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer' ) AS Data CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData ( XEvent ) ) AS src;
You can also use Database Performance Analyzer (DPA) to capture and examine deadlocks.
DPA will use a modified Extended Event session in order to capture deadlock details for SQL Server, and will use the default system health session for Amazon RDS. Users are able to configure a non-default session using the Advanced Properties page. When deadlocks are detected, they will be displayed at the bottom of the Trends tab:
This tab displays details about deadlocks, including the time the deadlock happened, the number of sessions involved in the deadlock, the objects involved, the program name, the machine name, and the user name. DPA also shows a metric named Victim Impact, which is a measure of time the deadlocked transaction was running and consuming resources. The transaction time of the deadlock victim is a waste of time and resources, making this a good measure of the impact the deadlock had on the application and the end-user.
In terms of performance loss due to deadlocks, eliminating the deadlocks with the highest victim impact (or groups of similar deadlocks with the highest cumulative victim impact) will result in the greatest improvement in performance for the application and the end-user.
During deadlock resolution, victim transactions are rolled back, causing the work of the session to be undone, and in many cases requiring entire transactions to be re-run. The rework caused by deadlocks is a hidden performance cost. The transaction time of the victim (i.e. wasted time) can be viewed as a good measure of the incurred performance cost.
For an entire deadlock, the Victim Impact is calculated by taking the sum of transaction times for all deadlock victims.
Clicking the links on the left you will be brought to the Deadlock Details page where you can view the deadlock summary showing the deadlock victim and survivor:
Scrolling down the Deadlock Details page you will also find information on the deadlocked resources:
At the top (and bottom) right of the screen you see two buttons. One button will allow for you to download the deadlock details as an XDL file to be used with SQL Server Management Studio for viewing the deadlock graph. The other button allows you to send the deadlock details as an email and include the XDL file as an attachment.
The ability to quickly send deadlock details to members of your team is a marked improvement over the features in applications such as SQL Server Profiler and Extended Events.
Database engines do not suddenly produce deadlocks on their own. Deadlocks are the result of application code combined with a database schema that results in an access pattern that leads to a cyclical dependency. It is up to the administrator working together with the application developer to resolve deadlocks.
Using a covering index can reduce the chance of a deadlock caused by bookmark lookups. Bookmark lookups can be identified inside of a query plan by this icon:
If your queries are using bookmark looks and are seeing deadlocks, try using a covering index to remove the bookmark lookup.
Creating indexes that match your foreign key columns can reduce your chances of having deadlocks caused by cascading referential integrity. Without these indexes, the cascading actions will take locks longer than necessary, increasing your chance for a deadlock.
You can check to see if your database has foreign keys defined without an appropriate index by running the following code:
SELECT RC.Constraint_Name AS FK_Constraint, RC.Constraint_Catalog AS FK_Database, RC.Constraint_Schema AS FK_Schema, CCU.Table_Name AS FK_Table, CCU.Column_Name AS FK_Column FROM information_schema.referential_constraints RC JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON RC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU2 ON RC.UNIQUE_CONSTRAINT_NAME = CCU2.CONSTRAINT_NAME LEFT JOIN sys.columns C ON CCU.Column_Name = C.name AND CCU.Table_Name = OBJECT_NAME(C.OBJECT_ID) LEFT JOIN sys.index_columns IC ON C.OBJECT_ID = IC.OBJECT_ID AND C.column_id = IC.column_id LEFT JOIN sys.indexes I ON IC.OBJECT_ID = I.OBJECT_ID AND IC.index_Id = I.index_Id WHERE I.name IS NULL ORDER BY RC.Constraint_NAME
One of the easiest ways to create deadlocks for testing purposes is by accessing database objects in different order. In the example used above, we could have had Transaction1 access TableB first, thus reducing the likelihood of a deadlock.
When writing code, it is useful to keep transactions as short as possible and access objects in the same logical order when it makes sense to do so.
SQL Server has a default transaction isolation level of READ COMMITTED. This isolation level allows for the chance that deadlocks may occur between writers and readers of data.
One way to avoid this is to consider using one of the row version-based isolation levels READ COMMITTED SNAPSHOT or SNAPSHOT. With these isolation levels, readers do not take locks on objects, but instead use row versions for isolation. No locks means no deadlocks.
This is not a comprehensive guide on transaction isolation levels. Before making such changes you should review the documentation on MSDN.
The DEADLOCK_PRIORITY session variable will specify the relative importance that the current session is allowed to continue processing if it is deadlocked with another session. It can be set to HIGH, NORMAL (the default), or LOW.
You can set the session variable with the following command:
SET DEADLOCK_PRIORITY HIGH
SQL Server will pick the deadlock victim based first on the setting of DEADLOCK_PRIORITY for each session, and second on the amount of work that it will take to roll back the open transactions.
The amount of work has no bearing on time spent by the current transactions. A SELECT statement could have been running for an hour, but rolling back would have a very low cost when compared to a statement modifying one or more rows.
You can trap for the deadlock error number using TRY...CATCH logic, and then retry the transaction. To avoid the chance of creating an infinite loop, we can also set the number of retries allowed. An example of this would be the following code sample:
DECLARE @try INT SET @try = 1 WHILE @try < 10 BEGIN BEGIN TRY BEGIN TRANSACTION <put your code here> COMMIT RETURN 0 END TRY BEGIN CATCH -- If transaction not rolled back we roll back manually IF @@TRANCOUNT <> 0 BEGIN ROLLBACK END IF ERROR_NUMBER() <> 1205 BEGIN -- if not a deadlock rethrow the error DECLARE @ErrorMessage NVARCHAR(4000) SET @ErrorMessage = ERROR_MESSAGE() RAISERROR('Error %s occurred' ,16,1,@ErrorMessage) RETURN -1 END ELSE BEGIN PRINT ‘Deadlock happened' END END CATCH SET @try = @try+ 1 END
The impact of a deadlock on end-users is a mixture of confusion and frustration. Retry logic is helpful, but having to retry a transaction simply results in longer end- user response times. This leads to the database being seen as a performance bottleneck and pressure on the DBA and application teams to track down the root cause and fix the issue.
In older versions of SQL Server, the collecting of deadlock information was a laborious process. There is no longer a need for manual collection and tracking of deadlock information for Microsoft SQL Server. Using tools like DPA makes it easy to know when a deadlock happened, why it happened, and what actions you can take to reduce the chance that a deadlock will occur again.