SQL Server Deadlock – the dead can speak

Can the dead speak, certainly not! But our ancestors, the Egyptians, believed that after death they will have an afterlife where they will speak with the gods and goddesses. From this belief, Ancient Egyptians developed “The Book of the Dead” – a book of funerary manuscripts and formulas. The text consists of a number of magic spells intended to assist a dead person's journey through the underworld, and into the afterlife.

Our SQL Server provides us a similar kind of funerary text - when a process was chosen as a deadlock victim. This is usually known as “deadlock graph”. In SQL Server deadlock scenario - YES, the dead can speak.

What is deadlock?

A deadlock occurs when two or more threads permanently block each other by each thread having a lock on a resource which the other threads are trying to lock so no one can proceed.  When the deadlock monitor of SQL Server detects a circular blocking chain, it selects one of the participants as a victim and rolls backs its transaction in order to lets the other process continue with their work.  The deadlock victim will get a 1205 error message.

Why deadlock happens?

Deadlock is not a lack of SQL Server’s Database Engine efficiency, rather inefficient application design, database schema design and database maintaining issue. Following are some well accepted deadlock reasons:

1.       Accessing tables in different order.

2.       Interactive transaction.

3.       Long duration transaction.

4.       Triggers – note that triggers are transactional.

5.       In-efficient indexes.

How are deadlock victims chosen?

By default, the Database Engine chooses the least expensive process as the deadlock victim. Rolling back victim’s transaction releases all locks held by the transaction. This allows other transactions to become unblocked and continue.

In a deadlock scenario, if all sessions have the same deadlock priority, the session with the transaction that is least expensive to roll back is chosen to be the deadlock victim. If sessions involved in the deadlock cycle have the same deadlock priority and the same cost, a victim is chosen randomly. Deadlock priority setting is a controlling factor that can be used in a T-SQL Query to choose a victim beforehand by the developer.

What to monitor:
There are many critical areas we can monitor, below are some:

1.            Patterns of SQL Blocking –writers block writers, readers block writers

2.            Number of Deadlocks/sec

3.            Query duration and Index usages

4.            Table and Index scans/lookups

5.            Efficiency of SELECT statement

6.            Memory grant for query

7.            Parallel execution (CXPACKET) of query

8.            Index fragmentation and page split

9.            Skewed statistics and wrong cardinality estimate

10.        Login/sec, Logout/sec

To reduce deadlock:

Deadlock or SQL blocking can’t be eliminated completely, but it can be minimized by taking some preventive actions. Below are some guidelines which may reduce deadlock occurrences.

1.             Changing the front-end application and T-SQL transaction to access resources in a similar order.

2.             Removing or optimizing triggers. By default, triggers are transactional.

3.             Keeping transactions as short as possible.

4.             Braking down the long transaction into multiple transactions.

5.             Using TRY/CATCH in the transaction while handling the deadlock error 1205 and applying a retry mechanism.

6.             Using ROWLOCK or PAGELOCK hints.

7.             Process the Database Engine thrown error 1205 in the end-user application.

8.             Adding, dropping and consolidating indexes.

9.             Adding index hints to force Optimizer to use index.

10.          Using “Indexed view”.

11.          Partitioning table.

12.          Using READ_COMMITTED_SNAPSHOT database option (row-versioning).

13.          Using SETTRANSACTION ISOLATION LEVEL SNAPSHOT in Stored Procedure or in SQL batch. (ALLOW_SNAPSHOT_ISOLATION database option must be set to ON).

14.          LOCK ESCALATION setting in non-partitioned table to DISABLE. Setting LOCK ESCALATION to TABLE when table is partitioned.

15.          Using server wide Trace Flag -if both trace flag 1211 and 1224 are set, 1211 takes precedence over 1224. Microsoft recommends using 1224 Trace flag. DBCC TRACEON (1211, -1), DBCC TRACEON (1224, -1)

16.          Using DEADLOCK_PRIORITY which can be set to LOW, NORMAL, or HIGH, or alternatively can be set to any integer value in the range (-10 to 10). If two sessions have different deadlock priorities, the session with the lowest priority is chosen as the deadlock victim.

17.          Using bound connections

How to capture diagnostic data?

1.       SQL Profiler Trace by choosing “deadlock graph” event.

2.       Using trace flag 1204 or 1222, or both to get output in ERROR LOG. Trace flag 3605 is required to send the output to error log.

DBCC TRACEON (3605, 1204, -1), DBCC TRACEON (3605, 1222, -1)

3.       Using SQL Server Extended Event

Starting from SQL 2008, deadlock events are automatically captured in the system_health extended event session. It can be queried to get historical deadlock information and the following query is for SQL 2012.

/*************************************************

** Deadlock extended events query

************************************************/

DECLARE @xml XML

SELECT @xml = cast(t.target_data AS XML)

FROM

  sys.dm_xe_session_targets t

  INNER JOIN sys.dm_xe_sessions s

    ON s.address = t.event_session_address

  INNER JOIN sys.server_event_sessions es

    ON s.name = es.name

WHERE

  s.name = 'system_health'

  AND t.target_name = 'ring_buffer'

SELECT @xml.query('/RingBufferTarget/event [@name="xml_deadlock_report"]') AS DeadlockGraph

Producing a deadlock situation - Analysis and resolving deadlock:

In our following example, we will be producing a deadlock scenario. We will capture SQL Profiler Trace and then review it and learn how to resolve deadlock.

Lets’ get started:

1.       Create a sample database “TestDB”.

-- Database TestDB

CREATE DATABASE TestDB

GO

USE TestDB

GO

2.       Create two tables and populate with some data.

-- Table A

CREATE TABLE TableA ( col1 INT )

INSERT  TableA

VALUES  ( 1 ),

        ( 2 ),

        ( 3 )

-- Table B

CREATE TABLE TableB ( col1 INT )

INSERT  TableB

VALUES  ( 10 ),

        ( 20 ),

        ( 30 )

3.       Start the SQL Server profiler, from the trace properties, and select the blank template. From the Event Selects tab, Select LOCKS->”deadlock graph” and then run the trace.

 4.       In SSMS, open a new query window (first connection) and run the following command:

-- 2. Run in the first connection

BEGIN TRAN

UPDATE TestDB.dbo.TableA SET col1 = 1

5.       In SSMS, open another query window (second connection) and run the following command:

-- 3. Run in the second connection

BEGIN TRAN

UPDATE TestDB.dbo.TableB SET col1 = 1

UPDATE TestDB.dbo.TableA SET col1 = 1

At this point, we will have blocking where the first connection is blocking the second connection.

6.       Now go back to the first connection and then run following command:

-- 4. Run in the first connection

UPDATE TestDB.dbo.TableB SET col1 = 1

7.       After a few seconds (default deadlock monitor duration is 5 seconds) the first connection will be chosen as the deadlock victim. We will see the following message:

Msg 1205, Level 13, State 45, Line 2

Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

8.       Switch to the SQL Profiler, we will see a deadlock event. To save the trace file in XDL, go to the File menu and choose

(a)    Export->Extract SQL Server Events-> Extract Deadlock Events

(b)   Save the SQL Trace as XDL file and then rename it to XML

Deadlock Error Message (error 1205):

Deadlock Graph from SQL Profiler Trace:

Deadlock XML:

We can open the saved XML file in SSMS or an internet browser and we will see a similar picture as follows.

Useful Explanation about Deadlock Graph and XML:

There are MSDN article where we can find all relevant information about deadlock XML. However, the following nicely formatted table has been taken from Clas Hortien’s (Microsoft Germany) deadlock’s blog for readability purpose.

SQL Profiler deadlock graph:

Line	Content
Server process id	The Session ID (SPID) of the request
Server batch id	An internal reference number for the batch, where the statement is running in.
Execution context id	When the query is executed in parallel, this shows the ID of one of the parallel running threads.
Deadlock priority	If the thread has an associated deadlock priority.
Log Used	The amount of transaction log, that was already generated by this thread
Owner ID	An internal reference number for the transaction.
Transaction descriptor	An internal reference number for the state of the transaction.

In the middle of the graph (rectangles)

Line	Content
Locktype	Type of lock the thread holds or requests. Common values are “Key Lock”, “Page Lock” or ”RID Lock”
HoBt ID	Heap or B-Tree ID, the ID of the internal allocation structure of the table or partition
Associated ObjId	The ObjectID that is associated to the HoBt ID. The name of the object can be retrieved via the OBJECT_NAME function.
Index name	The name of the index, on which the locks are held or requested

Deadlock XML output

XML Tag	Description
Deadlock-list	The surrounding bracket around all deadlocks
Deadlock	First deadlock victim, with Victim Process ID
Process-list	The list of involved processes
Process	Process number one, the victim.
ExecutionStack	The statement that causes the deadlock, which SQL Handle
Inputbuffer	The complete input buffer of the process
Process	Process number two, the survivor or winner of the deadlock
ExecutionStack	The same information as for the victim.
Inputbuffer	The same information as for the victim.
Resource-List	All the resources that are involved in the deadlock
Keylock/PageLock	Detailed information about the first lock itself.
Owner-List	The list of owner of this resource.
Owner	The owner with process id and lock mode (X, U, S etc.)
Waiter-List	The list of waiter of this resource
Waiter	The waiter with process id and lock mode (X, U, S etc.) and request type (WAIT).
Keylock/PageLock	Detailed information about the second lock.
Owner-List	The same information as for the first lock.
Owner	The same information as for the first lock.
Waiter-List	The same information as for the first lock.
Waiter	The same information as for the first lock.

Victim selection in our sample test:

In our example, the first connection was chosen as the deadlock victim. The reason is that it was the cheapest i.e. least expensive process to rollback. Also notice that it was using less transaction log (logused = “352”) as compared with the first one (logused=”456”).

What is wrong with our code?

If we look at our sample code, we will notice that we were not accessing the resources (table) in the same order. In our first connection, we update table “TableA” and then table “TableB”. In the second connection we updated table “TableB” first then table “TableA”. So our accessing method to SQL Server resources was completely opposite in order.

If we could access both tables in the same order from both queries, then we wouldn’t see any deadlocks rather a SQL blocking. So more clearly both queries must be written as follows:

-- First connection should be

UPDATE TestDB.dbo.TableA SET col1 = 1

UPDATE TestDB.dbo.TableB SET col1 = 1

-- Second connection should be

UPDATE TestDB.dbo.TableA SET col1 = 1

UPDATE TestDB.dbo.TableB SET col1 = 1

How to use DEADLOCK_PRIORITY in a Query effectively:

Say for example we have identified two transactions that are occasionally involved in a deadlock situation and any one of them became deadlock victims. We would like to increase the winning chance of one of the transactions while choosing the other one as a possible victim. Hence the “DEADLOCK_PRIORITY” comes into the picture.

1.       If the DEADLOCK_PRIORITY for one of the processes is lower (LOW) than the other, the lower priority process will be chosen as the victim.

2.       If the DEADLOCK_PRIORITY is the same for both, the process that is less expensive to rollback will be chosen as the victim.

Here is the trick:

The process we like to see as looser in deadlock situation, we can use “SET DEADLOCK_PRIORITY LOW” and add retry logic inside the T-SQL batch to complete its transaction in consequent loop.

The process we like to see as winner, we can use “SET DEADLOCK_PRIORITY HIGH” to complete its transaction at the first attempt and add the same retry logic in case of failure.

T-SQL Technique to reduce deadlock.

/*************************************************************

** TER: Transaction handling, Error catching and Retry
** Source: Jonathan Kehayias's tutorial

*************************************************************/

SET XACT_ABORT ON

SET DEADLOCK_PRIORITY HIGH -- ( LOW )

DECLARE @nRetries INT = 5

WHILE (@nRetries > 0)

BEGIN

  BEGIN TRY

    BEGIN TRANSACTION

    -- User transaction

    UPDATE TestDB.dbo.TableA

    SET

      col1 = 1

    UPDATE TestDB.dbo.TableB

    SET

      col1 = 1

    COMMIT TRANSACTION

    SET @nRetries = 0

    -- debug message

    PRINT 'Transaction successful'

  END TRY

  BEGIN CATCH

    IF (error_number() = 1205)

    BEGIN

      SET @nRetries = @nRetries - 1

      -- debug message

      PRINT 'Transaction unsuccessful'

      PRINT 'Deadlock occured. Will try ' + cast(@nRetries AS VARCHAR) + ' more times'

    END

    -- handle all errors including deadlock in application

    ELSE

    BEGIN

      DECLARE @ErrorMessage NVARCHAR(4000)

      DECLARE @ErrorSeverity INT

      DECLARE @ErrorState INT

      SELECT @ErrorMessage = error_message()

           , @ErrorSeverity = error_severity()

           , @ErrorState = error_state()

      RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)

      SET @nRetries = 0

      IF xact_state() <> 0 OR @@TRANCOUNT > 0

        ROLLBACK TRANSACTION

    END

  END CATCH

END 

Summary:

There is no magic to solve deadlock issues –we just need a little understanding about how database engines work and design the solution accordingly. I hope you found this post useful.

Read more:

Minimizing Deadlocks

http://msdn.microsoft.com/en-us/library/ms191242(v=sql.105).aspx

Watch Jonathan Kehayias's Excellent Tutorial -"SQL Server: Deadlock Analysis and Prevention"

http://pluralsight.com/training/Courses/TableOfContents/sqlserver-deadlocks