Submit a ticketCall us

Webinar: Web Help Desk for HR, Facilities and Accounting Departments
This webinar will focus on use cases for HR, Facilities and Accounting.

Having a unified ticketing and asset management system for all the departments in your company can provide end-users with a seamless experience and make things easier for your IT team. Yet, with different business tasks and objectives, many departments don’t fully understand the capabilities of Web Help Desk and how the software can be customized for effective use in their departments.
Register Now.

Home > Success Center > Database Performance Analyzer (DPA) > Deadlock analysis in DPA

Deadlock analysis in DPA

Created by Anthony.Rinaldi_ret, last modified by Anthony.Rinaldi_ret on Jul 21, 2016

Views: 1,665 Votes: 2 Revisions: 3

Updated July 1, 2016

Overview

This article explains what a deadlock is, and how to use DPA to identify and resolve deadlocks.

Environment

  • DPA 10.2 and later

Detail

A deadlock happens when two or more transactions block each other by holding locks on resources that each of the transactions also need.

For example:

  • Transaction 1 holds a lock on Table A.
  • Transaction 2 holds a lock on Table B.
  • Transaction 1 now requests a lock on Table B, and is blocked by Transaction 2.
  • Transaction 2 now requests a lock on Table A, and is blocked by Transaction 1.

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.

Find deadlocks

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.

Trace Flags

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.

Performance Monitor

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.

SQL Server Profiler

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.

Extended Events

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)[1]') 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;

Use DPA to find deadlocks

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.

Resolve deadlocks

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.

Covering indexes

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.

Foreign key indexes

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

Object order access

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.

Transaction isolation levels

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.

Deadlock priority

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.

Application error handling

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

Summary

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.

 

 

 

 

Last modified
15:18, 21 Jul 2016

Tags

Classifications

Public