Scripts to Force a Deadlock in SQL Server

As DBAs, we are constantly looking to proactively find and fix issues before they become major problems. And on most occasions, we are at the mercy of software vendors or in-house application developers who believe it is their God-given right to leave complex database issues to ‘someone else’. That’s where we come in.

Troubleshooting deadlocks is one such example. Unless you have a system in place for detecting and analysing deadlocks, you’re probably going to get an email with a snapshot of a screen in some application where a user has encountered a cryptic message which has the word ‘deadlock’ in it. You would then try and find ways to log details when the next deadlock occurs.

There are many possible ways to log the details of a deadlock such as setting trace flags 1204 & 1222, running a SQL trace with the TSQL_Locks template, or using third party detection tools like SQL Deadlock Detector from Lakeside SQL. There are plenty of posts on the internet which will give you adequate information on all of these techniques.

After you’ve decided on a particular technique, you should look to test the results of the chosen technique in a non-production environment. However, deadlocks are rarely easily reproducible through users running applications. More often than not, deadlocks require a certain level of concurrency and timing which users are rarely able to simulate, requiring you as the DBA to find programmatic ways to force a deadlock.

This post contains scripts I wrote recently to simulate a deadlock. The logic behind why the deadlock occurs has also been described below. I hope this assists you in evaluating your chosen method to log details on deadlocks.

Scripts to create required objects in the database

The script shown below contains the logic to create 2 simple tables containing 2 integer columns each. The id column in each table is the primary key (with a clustered index) and the col field in the second table references the id field in the first (i.e. there is a foreign key between the 2 tables on the col field in the second table).



Script to be used in Session #1

Once you’ve created the above mentioned tables, open up a New Query window and execute the following script.

INSERT dbo.Tbl1 (id, col) VALUES (2, 999)

This script will start a transaction and insert a record into table Tbl1. The new record will contain a primary key field value of 2. The transaction will remain open, i.e. it won’t be committed or rolled back (yet).

Script to be used in Session #2

Open up another New Query window and execute the following script.

INSERT dbo.Tbl2 (id, col) VALUES (111, 2)

This script will also start a new transaction. It will then insert a record into table Tbl2 with a value of 2 for the ‘col’ field, which references the primary key in Tbl1. Since the transaction which inserted a record with a primary key value of 2 into Tbl1 hasn’t committed or rolled back yet, the transaction on Tbl2 waits for the first transaction to finish. This transaction will also be left uncommitted. The primary key field value being inserted into Tbl2 is 111.

Script to be added to Session #1

Add the script shown below to the first Query window and execute it.

INSERT dbo.Tbl2 (id, col) VALUES (111, 555)

This script attempts to create a record with a primary key field value of 111 in Tbl2 as part of the open transaction in Session #1. At this stage, the SQL Engine will detect a deadlock and roll back one of the two open transactions. You should see the deadlock error message (such as the one shown below) in one of the two session windows.

The reason the deadlock occurred is quite simple. Session #1 was hanging on to a record in Tbl1 with a primary key field value of 2. Session #2 was hanging on to a record in Tbl2 with a referencing field value of 2 while it was waiting on the record in Tbl1 to be committed. Session #1 then executed a new SQL which inserted the primary key field value of 111 in Tbl2, which had to wait for Session #2 to either commit or rollback since it was also attempting to insert a record in Tbl2 with the same primary key value. Since both sessions were waiting on each other to proceed, the result was a deadlock which had to be resolved by SQL Server’s automatic deadlock detection feature.

I hope this blog post helps you in reviewing your chosen deadlock detection methodology, and eventually in smashing those pesky deadlocks in your production environment. If you have any questions or need any additional help for detecting and analysing deadlocks, leave a comment on this post.