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:
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.
-- 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
** 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
Watch Jonathan Kehayias's Excellent Tutorial -"SQL Server: Deadlock Analysis and Prevention"
