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 responsible for detecting and managing a DeadLock situation.

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 happen, 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:

Step 1: Open first connection and Execute below query.

Step 2: Open second connection and Execute below queries.

Step 3: Execute below query in first connection again.

Up to Step 2, you can find that queries are running and waiting to complete Step 1. Here, Step 1 became a blocking step for Step 2.
Once you execute the query of Step 3 on first connection, you will get DeadLock situation and will get below message.

Anvesh Patel

Leave a Reply

2 Comments on "SQL Server: T-SQL script to generate a DeadLock in a Database"

Notify of
avatar
Sort by:   newest | oldest | most voted
Ramakrishna
Guest

Hi Amit,as per you,you have a script to find deadlock information, but i didn’t get such type of script at any where…. Ex: SP_who2 will tell Lock information by that way any script to findout Dead lock details..

wpDiscuz