How to create a deadlock between two transactions in SQL Server

Here is an example where you can see how to cause a deadlock between 2 transactions in SQL Server. Usually you would want to avoid deadlocks, but if you want to test catching SQL Server deadlocks, you will need to wait for one to happen, or you will need to cause one.

First we need to create tables with some data in them:

CREATE TABLE deadlock_example_table_1 (column1 int)
CREATE TABLE deadlock_example_table_2 (column1 int)

INSERT INTO deadlock_example_table_1 (column1)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
GO

INSERT INTO deadlock_example_table_2 (column1)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
GO

Open two query windows in SQL Server Management Studio.
Execute this in Query Window 1:

BEGIN TRAN
DELETE FROM deadlock_example_table_1 WHERE column1 = 2

Execute this in Query Window 2:

BEGIN TRAN
DELETE FROM deadlock_example_table_2 WHERE column1 = 2
DELETE FROM deadlock_example_table_1 WHERE column1 = 2

Execute this in Query Window 1:

DELETE FROM deadlock_example_table_2 WHERE column1 = 2

The transaction in the Query Window 1 has been chosen as the deadlock victim, and will be rolled back. You will get an error similar to this:

Msg 1205, Level 13, State 45, Line 1
Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

COMMIT or ROLLBACK transaction in Query Window 2:

COMMIT TRAN

or

ROLLBACK TRAN

Drop the tables you created for deadlock testing:

DROP TABLE deadlock_example_table_1
DROP TABLE deadlock_example_table_2

To find out how to catch SQL Server deadlock read previous blog post:
Finding SQL Server deadlocks