How to detect a deadlock in DBMS

The six faces of deadlocks

SQL Server Concurrency, Part 3 3/16/2017, 12:00 AM
How to prevent two transactions from blocking each other.
In the two previous parts of this mini-series [1] [2] you have already learned that the SQL Server isolates users working at the same time depending on the selected transaction isolation level. For this he uses these locks:
  • Exclusive locks are always requested for write transactions.
  • Reading transactions can request shared locks depending on the selected transaction isolation level.
So far so good. The big problem with locks, however, is that they are not all compatible with one another. For example, a shared lock is not compatible with an exclusive lock. If you request a shared lock anyway, it will only be delivered when the exclusive lock is no longer active; a classic blocking situation arises.
It becomes critical if the second session also requests an incompatible lock. Then the first session waits for the second while the second waits for the first session. Nothing works anymore, a deadlocking situation has arisen.

Deadlock handling in the SQL server

The nice thing about SQL Server is that it can resolve deadlock situations on its own. This is done by a background process called the Deadlock Monitor, which checks for deadlocks every few seconds.
Once it has identified a deadlock situation, it reverses the cheapest transaction. This releases the requested locks and the blocked transaction can continue its work. The cheapest transaction is the one that has written the least amount of data to the transaction log. In the event of a deadlock between a reading and a writing transaction, the reading transaction is therefore always rolled back because it does not write any data to the transaction log. The transaction rolled back by the deadlock monitor is also known as deadlock victim.
The transaction selected as deadlock victim receives error number 1205. If this message occurs, the application's data access code should simply execute the transaction again. Since the deadlock on the part of the SQL Server was already resolved when the error number was output, there should no longer be a deadlock on the next attempt. However, the code should not try indefinitely to rerun a transaction acknowledged with error 1205, but should terminate the attempts after multiple failures and inform the user that there are problems accessing the database.
However, a deadlock is a situation that your application can solve in most cases without user intervention - as long as your data access code always checks for the occurrence of error number 1205 and reacts to it appropriately.

Deadlock troubleshooting

The on-board tool of the SQL Server contains three technologies for analyzing and handling deadlocking situations:
  • Trace Flag 1222
  • SQL Server Profiler
  • Extended events
A look at the logs of the SQL Server (picture 1)
In general, I recommend using extended events to deal with deadlocks. Since the SQL Server Profiler is already a deprecated function by Microsoft, it will no longer be included in one of the next versions of the database. The trace flag 1222 no longer fits the time, as only XML information about the deadlock that has occurred is written to the SQL server log (Fig. 1).
XML list to a deadlock (pic 2)
The extended events offer the most powerful option for troubleshooting deadlocks, as the SQL server returns a large amount of different information in the form of an XML document. Figure 2 shows an excerpt from it. This is the so-called deadlock graph, which contains the following XML nodes at the top level:
  • <process-list>
  • <resource-list>
The knot <process-list> describes which queries were involved in the deadlock. This makes it very easy to identify which transactions were deadlocked. In addition, the node provides information about which resources (locks) were involved in the depicted deadlock.
You see about one <owner-List>which locks have already been held by the transactions, and the <waiter-List> provides information on which locks have currently been waited for. Additionally are <owner-List> and <waiter-List> with the corresponding transaction in the <process-list> connected.
Based on this information in the deadlock graph, it is quite easy to find out why the deadlock occurred. In general, the deadlock graph is the first port of call when analyzing a deadlocking situation.
Very often I get email inquiries why two different queries ran into a deadlock. Most of the time, the associated SQL statements can be found in the attachment of the mails. But that doesn't really help, because the deadlock in the SQL Server occurs on a physical level, but the SQL statements always describe on a logical level what the database should do. Therefore, I always request the associated deadlock graph for the analysis, since only this describes what has happened on the physical level. Analysis is almost impossible without a deadlock graph!

Cycle deadlock

As indicated above, there are different types of deadlocks. The simplest of these is a cycle deadlock. It occurs when resources are accessed in a different order. Take a look at listing 1 and 2. As can be seen, the two sessions access the same two tables, but in a different order: The first session accesses the table T1 and then on table T2 to, the second session accesses the table T2 and then on table T1 to.
Listing 1: Cycle Deadlock - Session 1
USE TempDB
GO

CREATE TABLE T1
(
C1 INT
)
GO

INSERT INTO T1 VALUES (1)
GO

CREATE TABLE T2
(
C1 INT
)

INSERT INTO T2 VALUES (1)
GO

SELECT * FROM T1
SELECT * FROM T2
GO

BEGIN TRANSACTION

UPDATE T1 SET C1 = 2

UPDATE T2 SET C1 = 2

- COMMIT TRANSACTION

DROP TABLE T1
DROP TABLE T2
GO
If you alternate the statements for the two transactions step by step, you will run into a deadlock, which will be recognized a few seconds later by the deadlock monitor and finally resolved by rolling back the cheapest transaction.
Listing 2: Cycle Deadlock - Session 2
USE TempDB
GO

BEGIN TRANSACTION

UPDATE T2 SET C1 = 1

UPDATE T1 SET C1 = 1

COMMIT TRANSACTION
GO
This cyclical deadlock can be avoided very easily: It is sufficient if you always access tables in the same order. I have already worked several times with customers whose developers were required to always access tables in alphabetical order (based on naming) in order to avoid cycle deadlocks.

Bookmark lookup deadlocks

I have already written about bookmark lookups a few times in dotnetpro - for example in [3] - and reported that these are not always the best means in terms of performance and indexing strategy. Another negative side effect that can result from bookmark lookups is the fact that you can easily run into deadlocks if you execute parallel write transactions on the same table. Take a look at listing 3 and 4.
Listing 3: Bookmark Lookup Deadlock - Session 1
USE master
GO

CREATE DATABASE BookmarkLookupDL
GO

USE BookmarkLookupDL
GO

CREATE TABLE Deadlock (
Col1 INT NOT NULL PRIMARY KEY CLUSTERED,
Col2 INT NOT NULL,
Col3 INT NOT NULL
)
GO

CREATE NONCLUSTERED INDEX idx_Col3 ON Deadlock (Col3)
GO

INSERT INTO Deadlock VALUES (1, 1, 1)
GO

SELECT * FROM deadlock
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO

WHILE (1 = 1)
BEGIN
UPDATE deadlock
SET Col1 = Col1 + 1
WHERE Col3 = 1
END
GO
The listings aren't complex. In Listing 3, the clustered key of the table is updated in an endless loop. Since the clustered key is also available as a logical pointer in the non-clustered index, the SQL Server must first update the clustered index and then also the non-clustered index. Exclusive locks are required to update both indexes.
Listing 4: Bookmark Lookup Deadlock - Session 2
USE BookmarkLookupDL
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO

WHILE (1 = 1)
BEGIN
SELECT * FROM Deadlock WITH (INDEX (idx_Col3))
- The hint is necessary to overcome the tipping
- Point to produce a bookmark lookup
WHERE Col3 = 1
END
GO
The code in Listing 4 accesses the same table via bookmark lookup: in the first step it reads the non-clustered index, in the second step it reads the clustered index. Access to the resources involved is also not done in the same order, which means that a deadlock can easily be provoked!
The transaction isolation level was on in both transactions Repeatable Read set to make the deadlock easier to reproduce. In a productive environment with a corresponding workload, this deadlock can also be used in the standard isolation level Read committed reproduce. Figure 3 illustrates this problem.
Bookmark Lookup deadlocks (picture 3)
How can such a deadlock be avoided? The problem arises from the fact that the SQL Server internally accesses the resources in the wrong order. Simply rewriting the queries is therefore not an option.
However, you can prevent the deadlock by avoiding the bookmark lookup entirely - a covering non-clustered index will help! If read access to the clustered index is avoided, the deadlock can be resolved. Listing 5 shows the index required for this.
Listing 5: Covering Non-Clustered Index
- Creates a Covering Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_Col3 ON Deadlock (Col3)
INCLUDE (Col1, Col2)
WITH (DROP_EXISTING = ON)
GO
Bookmark lookup deadlocks are another reason why you should only accept bookmark lookups in special cases.

Deadlocks due to missing indexes

Deadlocking situations can also arise due to missing indexes. Imagine you have a heap table (a table without a clustered index) and, in addition, not a single non-clustered index is defined for data access for this table. In this case there is only one way to access this table - namely via a table scan operator in the execution plan.
Listing 6: Missing indexes - Session 1
USE master
GO
- Create a new database
CREATE DATABASE DeadlockingDemo
GO

- Use it
USE DeadlockingDemo
GO

- Create a table without any indexes
CREATE TABLE Table1 (
Column1 INT, Column2 INT)
GO

- Insert a few records
INSERT INTO Table1 VALUES (1, 1)
INSERT INTO Table1 VALUES (2, 2)
INSERT INTO Table1 VALUES (3, 3)
INSERT INTO Table1 VALUES (4, 4)
GO

- Create a table without any indexes
CREATE TABLE Table2 (
Column1 INT, Column2 INT)
GO
- Insert a few records
INSERT INTO Table2 VALUES (1, 1)
INSERT INTO Table2 VALUES (2, 2)
INSERT INTO Table2 VALUES (3, 3)
INSERT INTO Table2 VALUES (4, 4)
GO

BEGIN TRANSACTION
- Acquires an exclusive lock on the row
UPDATE Table1 SET Column1 = 3 WHERE Column2 = 1
- Execute the query from Session 2 ...
   -- ...

- This query now requests a shared lock, but gets
- blocked, because the other session / transaction
- has an exclusive lock on one row, that is
- currently updated
SELECT Column1 FROM Table2
--WITH (INDEX = idx_Column2)
- The index hint is necessary because in this
- tiny table, SQL Server will just scan the whole
- table ...
WHERE Column2 = 3

ROLLBACK TRANSACTION
GO
This means that you have to read the entire table every time you access the table (and request a shared lock for each data record), even if you are only interested in a specific data record. Take a closer look at listing 6 and 7. You will see that the two tables involved are accessed again in a different order.
Listing 7: Missing indexes - Session 2
- Use the previous created database
USE DeadlockingDemo
GO

BEGIN TRANSACTION
- Acquires an exclusive lock on the row
UPDATE Table2 SET Column1 = 5 WHERE Column2 = 2

SELECT * FROM sys.dm_tran_locks
WHERE request_session_id = @@ SPID

- Continue with the query from Session 2 ...
   -- ...

- This query now requests a shared lock, but
- gets blocked, because the other session /
- transaction has an exclusive lock on one row,
- that is currently updated
SELECT Column1 FROM Table1
- WITH (INDEX = idx_Column2) - The index hint
- is necessary, because in this tiny table SQL
- Server will just scan the whole table ...
WHERE Column2 = 4

ROLLBACK TRANSACTION
GO
As before, this creates a deadlock between the two transactions. You can avoid this deadlock by defining additional non-clustered indexes for both tables. By using a non-clustered index, the SQL Server can use a Seek-Operation find the desired data records in the leaf level of the non-clustered index without having to access the actual table via a table scan operator. And this eliminates the deadlock. Listing 8 shows the non-clustered indices required for this.
Listing 8: Non-Clustered Indices
- Create new indexes so that SQL Server has age
- native access paths to the data. The previous
- 2 SELECT statements can now be done through the
- Nonclustered Index without acquiring a shared
- Lock on the table itself (which currently holds
- an Exclusive Lock from the UPDATE statement).
CREATE NONCLUSTERED INDEX idx_Column2 ON Table1 (Column2)

CREATE NONCLUSTERED INDEX idx_Column2 ON Table2 (Column2)
GO

Deadlocks when accessing different data areas

If you access different data areas in different order, you are also threatened with a deadlock. Here is a concrete example: Listing 9 and 10 again show two transactions that can block each other. This is again a classic cycle deadlock, since different data areas are accessed in different order. This deadlock can also be avoided if you access the data areas in the same order.
Listing 9: Deadlock - Session 1
USE AdventureWorks2014
GO

- Causes a deadlock when we access data in the
- wrong order
BEGIN TRANSACTION
- 1st range of data
SELECT * FROM Person.Person
WHERE ModifiedDate = '20120208'

UPDATE Person.Person
SET FirstName = '...'
WHERE ModifiedDate = '20120208'
- Switch to session 2
   -- ... 
- 2nd range of data
- This statement will cause a deadlock!
SELECT * FROM Person.Person
WHERE ModifiedDate = '20120209'

UPDATE Person.Person
SET FirstName = '...'
WHERE ModifiedDate = '20120209'

ROLLBACK
GO

Deadlocks with repeatable read

The transaction isolation level Repetable Read is also very susceptible to deadlocking situations, as it is well known (see [1] [2]) that the shared locks are held until the end of the transaction so that repeatable reads can be guaranteed. Take a look at Listing 11.
Listing 10: Deadlock - Session 2
USE AdventureWorks2014
GO
- Causes a deadlock when we access data in the
- wrong order
BEGIN TRANSACTION
- 2nd range of data
SELECT * FROM Person.Person
WHERE ModifiedDate = '20120209'

UPDATE Person.Person
SET FirstName = '...'
WHERE ModifiedDate = '20120209'
- Switch to session 1
   -- ...
- 1st range of data
- This statement will cause a deadlock!
SELECT * FROM Person.Person
WHERE ModifiedDate = '20120208'

UPDATE Person.Person
SET FirstName = '...'
WHERE ModifiedDate = '20120208'

ROLLBACK
GO
If you execute this transaction in two sessions at the same time, you can also run into a deadlock here. The problem with this is that neither transaction has the exclusive lock for the UPDATEStatement because the other transaction has already requested an incompatible shared lock on the data record. This blocks both UPDATE-Statements - Deadlock!
Listing 11: Deadlock in Repeatable Read - Session 1
USE AdventureWorks2014
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO

BEGIN TRANSACTION
SELECT * FROM Person.Person
WHERE ModifiedDate = '20120208'
- Switch to session 2
   -- ...

- This statement will cause a deadlock!
UPDATE Person.Person
SET FirstName = '...'
WHERE ModifiedDate = '20120208'

ROLLBACK
GO
You can resolve this deadlock by, for example, using the SELECT-Statement explicitly request an update lock. This is compatible with a shared lock, but incompatible with itself, and also incompatible with an exclusive lock. This can only be the first SELECTStatement to be executed. The SELECT-Statement of the other session will block because the update lock cannot be requested.
Listing 12: Deadlock in Repeatable Read - Session 2
USE AdventureWorks2014
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO

BEGIN TRANSACTION
SELECT * FROM Person.Person
WHERE ModifiedDate = '20120208' WITH (UPDLOCK)
- Switch to session 1
   -- ...

- This statement will cause a deadlock!
UPDATE Person.Person
SET FirstName = '...'
WHERE ModifiedDate = '20120208'

ROLLBACK
GO
With this approach, the SQL Server serializes both transactions and executes them in sequence. The performance decreases, but in return you no longer have a deadlock, see Listing 12.

Intra-parallelism deadlocks

A very common deadlock in SQL Server is the intra-parallelism deadlock. The mean thing about it is that it is a bug in SQL Server that is deliberately not fixed. Microsoft decided to do this because fixing the bug would be too risky. It is important for you to recognize when you are dealing with an intra-parallelism deadlock. The graph of such a deadlock reveals that the session ID is always the same, and that the query itself ran into a deadlock. This is possible because an execution plan for such a deadlock is always implemented with several worker threads, which ultimately wait for each other and thereby cause the deadlock.
Nevertheless, such a deadlock can be avoided because an intra-parallelism deadlock only occurs in parallel execution plans. So make sure that such queries are executed serially with only one worker thread. This works in two ways:
  • Adjusting the indexing strategy so that it can be ensured that the total costs of the execution plan are below the Cost Threshold for Parallelism [4].
  • Use the MAXDOP 1 query hint because it forces you to have a serial execution plan.

Read committed snapshot isolation

In the previous edition of dotnetpro you have Optimistic Concurrency and the two new Transaction Isolation Levels Read committed snapshot isolation and Snapshot isolation got to know [2].
The basic idea behind Optimistic Concurrency is that reading operations - i.e. SELECTStatements - no longer request shared locks. And this behavior can also help you eliminate deadlocks.
As you have read on the previous pages, deadlocks occur very often between read and write transactions. If you eliminate the shared locks for read transactions, you also prevent deadlocks between these two types of data access.
You should therefore register a large number of deadlocks between read and write transactions and you also have no direct way of resolving them, just switch Read committed snapshot isolation for the database in question - that can work wonders.

Conclusion

The most important message of this article: The SQL Server can recognize and resolve blockages independently via its deadlock monitor. In general, you should always access tables and data in the same order, if possible. This prevents many deadlocks in advance. If this rule cannot be followed, activating Read committed snapshot isolation eliminate a large number of deadlocks.
  1. Klaus Aschenbrenner, SQL Server Concurrency, Part 1, Pessimistic Concurrency, dotnetpro 2/2017, page 84 ff.
  2. Klaus Aschenbrenner, SQL Server Concurrency, Part 2, Optimistic Concurrency, dotnetpro 3/2017, page 80 ff.
  3. Klaus Aschenbrenner, Looking up with brains, dotnetpro 3/2015, page 94 ff.,
  4. Cost Threshold for Parallelism,