SQL Server: T-SQL script to generate a DeadLock in a Database

 The DeadLock in a database system is a common thing and all Database Administrators are responsible for detecting and managing a DeadLocks.

In this post, I am sharing T-SQL script to generate a DeadLock situation in a SQL Server.

Most of the time, we are facing DeadLock problem in our Production Database Server.
But What happens, when Database Administrator wants to generate DeadLock situation and intends to create a script for detecting a DeadLock.

First, create two tables with sample data:

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATE TABLE tbl_SampleTable_A (ID INT IDENTITY(1,1), Name VARCHAR(50)) GO   CREATE TABLE tbl_SampleTable_B (ID INT IDENTITY(1,1), Name VARCHAR(50)) GO   INSERT INTO tbl_SampleTable_A(Name) VALUES ('dbrnd') GO 20   INSERT INTO tbl_SampleTable_B(Name) VALUES ('dbrnd') GO 20

Step 1: Open first connection and Execute below query.

1 2	BEGIN TRAN UPDATE tbl_SampleTable_A SET Name = 'Anvesh' WHERE ID=1

Step 2: Open second connection and Execute below queries.

1 2 3

BEGIN TRAN UPDATE tbl_SampleTable_B SET Name = 'Anvesh' WHERE ID=2 UPDATE tbl_SampleTable_A SET Name = 'Anvesh' WHERE ID=2

Step 3: Execute below query in first connection again.

1	UPDATE tbl_SampleTable_B SET Name = 'Anvesh' WHERE ID=1

Up to Step 2, you can find that queries are running and waiting to complete Step 1. Here, Step 1 became a blocker step for Step 2.

Once you execute the query of Step 3 on first connection, you will get DeadLock situation and will get the message.

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