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).

CREATE TABLE Tbl1 (id INT NOT NULL PRIMARY KEY CLUSTERED, col INT)

CREATE TABLE Tbl2 (id INT NOT NULL PRIMARY KEY CLUSTERED, col INT REFERENCES dbo.Tbl1(id))

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.

BEGIN TRAN
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.

BEGIN TRAN
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.

Foreign Keys – Dependency Tracking

Every now and again, we come across the need to determine the relationships between a set of tables, and the order in which data should be inserted or deleted in them, in accordance with the rules of referential integrity. There is a dearth of information on the internet regarding this subject, so I decided to write my own script and publish it.

The script accepts a list of tables as an input parameter. It then works through the relationships between these tables and the tables they refer in the database. If the latter tables reference additional tables, then they get pulled in as well. Therefore, when the script completes, it prints the entire referential integrity ‘spider web’ of tables linked to the tables supplied in the input list.

It also prints the tables in order, i.e. the order in which data should be inserted. Therefore, the order in which data should be deleted is the opposite of the printed order.

The script’s code is shown below. The output is from the AdventureWorks database. You can download the script’s code from the attachment at the end of the article.

Multiple tables can be fed into the script. The example shown below uses only 1 table (Sales.Customer) from the AdventureWorks database.

The output of the script is shown below. It contains the list of referenced tables listed in order. The ‘lvl’ column indicates the level of depth in the referential integrity chain. The table passed in as the input (Sales.Customer) is not listed in the output. It can be assumed that it is present at level zero.

The script’s code can be downloaded from here –> Script.