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.

Advertisements

About Ajit Ananthram
Ajit is a Microsoft Certified Specialist in Big Data Analytics Solutions, Microsoft Certified Solutions Associate (Business Intelligence) and a Certified Scrum Master. He has a Masters degree in IT from the University of Technology Sydney and over 10 years of industry experience working with data in roles such as Data Architect and Tech Lead. He currently works as a Senior Consultant and Microsoft Ambassador at Servian in Sydney, Australia.

4 Responses to Scripts to Force a Deadlock in SQL Server

  1. Dilum Chamantha says:

    Ajit, thanks for your valueable information. After activating the dead locak trace I were able to see the exact queries are causing the issue. Currtntly Im touching some old codes which does not have proper TXN ben, ends. I would like to know through your experience, besides code enhancements is there any fine tunings can be done at DB level to control thius situation.

    • Hey Dilum, thanks for your feedback. While there is no substitute for code coding practices, you might want to explore transaction isolation levels to reduce contention in your databases. Isolation levels govern the degree to which transactions interact with each other. There are levels such as READ COMMITTED SNAPSHOT which will allow transactions performing writes to work in parallel with transactions performing reads without causing any blocking. There are of course unwanted side-effects that can occur when such isolation levels are used, although in most environments, the probability of this is extremely low.

  2. John Walker says:

    Get an overview on Deadlock in SQL Server from here: http://www.sqlserverlogexplorer.com/what-is-a-deadlock-in-sql-server/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: