Creating a Deadlock

Previously we have looked at how to capture a deadlock, but how do we go about creating a deadlock?

I think the following is the simplest method:

1. Create two tables with a single row in each and update a row in the first table (note transaction is started with BEGIN TRAN, but not committed.)

USE [tempdb]
IF EXISTS(SELECT * FROM sys.tables WHERE [Name] = 'J1')
DROP TABLE [J1];
IF EXISTS(SELECT * FROM sys.tables WHERE [Name] = 'J2')
DROP TABLE [J2];
CREATE TABLE [J1]([ID] INT);
CREATE TABLE [J2]([ID] INT);
INSERT INTO [J1]VALUES (1);
INSERT INTO [J2]VALUES(1);
GO
BEGIN TRAN
UPDATE [J1] SET [ID] = 1
--UPDATE [J2] SET [ID] = 1

2. Open a query in a new window  and run:
USE [tempdb]
BEGIN TRAN
UPDATE [J2] SET [ID] = 1
UPDATE [J1] SET [ID] = 1

3. Finally in the query window opened in step 1. run the commented code by highlighting everything after the comment marks then pressing F5.
--UPDATE [J2] SET [ID] = 1

This will result in our first window being the victim of a deadlock:
Msg 1205, Level 13, State 45, Line 13

Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Note that the second query will have an open transaction, which you can either ROLLBACK or COMMIT.
In step 1 (spid 53) a lock was taken on the row of table J1

In step 2 (spid 54) a lock was taken on the row in table J2, and then it tries to take a lock on J1 but has to wait as the first query already has it locked.

In step 3 (spid 53) the first query attempts to lock the row that the second query has already locked, while holding a lock on the row that the second query wants.

ExampleDeadlock.xdl save this file to your desktop stripping off the .txt extension to leave .xdl which will allow opening in SSMS.