How to Simulate and Prevent SQL Server Deadlocks: A Step-by-Step Guide

SQL Server deadlocks occur when two or more transactions each hold resources the other needs, causing all involved transactions to wait indefinitely.

Deadlocks typically involve multiple tables or rows locked in different orders; when transactions acquire locks in opposite sequences, a deadlock can arise.

SQL Server uses locks to maintain data consistency. When a transaction modifies a resource, it locks it, preventing other concurrent modifications. If another transaction needs a locked resource, it must wait until the lock is released.

When a deadlock is detected, SQL Server selects one transaction as a victim, rolls it back, and allows the remaining transactions to continue, usually choosing the victim with the lowest cost.

To reduce deadlocks, adopt measures such as designing transactions wisely, avoiding long lock durations, and limiting lock scopes. Optimizing database design and queries also helps lower deadlock risk.

Common Causes of SQL Server Deadlocks

Resource contention: multiple transactions competing for the same tables, rows, or pages.

Lock order: transactions request locks in different sequences, e.g., Transaction A locks Table X then Table Y, while Transaction B locks Table Y then Table X.

Long lock holding time: transactions that hold locks for extended periods while performing other work increase deadlock probability.

Inappropriate isolation level: high isolation levels (e.g., SERIALIZABLE) expand lock ranges, raising deadlock chances.

High concurrency: many simultaneous transactions amplify resource competition, leading to more deadlocks.

Sample Deadlock Simulation

-- Create database
CREATE DATABASE DeadlockDemo;
GO

-- Use the database
USE DeadlockDemo;
GO

-- Create table
CREATE TABLE DemoTable (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50)
);
GO

-- Insert data
INSERT INTO DemoTable (ID, Name) VALUES (1, 'Record 1');
INSERT INTO DemoTable (ID, Name) VALUES (2, 'Record 2');
GO

-- Simulate deadlock with two transactions
-- Transaction 1
BEGIN TRANSACTION;
UPDATE DemoTable SET Name = 'Updated Record 1' WHERE ID = 1;
WAITFOR DELAY '00:00:05'; -- simulate wait

-- Transaction 2
BEGIN TRANSACTION;
UPDATE DemoTable SET Name = 'Updated Record 2' WHERE ID = 2;
WAITFOR DELAY '00:00:05'; -- simulate wait

-- Continue Transaction 1
UPDATE DemoTable SET Name = 'Updated Record 1' WHERE ID = 2;
COMMIT; -- finish Transaction 1

-- Continue Transaction 2
UPDATE DemoTable SET Name = 'Updated Record 2' WHERE ID = 1;
COMMIT; -- finish Transaction 2

In this example, each transaction updates records in opposite order, causing each to hold a lock the other needs, which leads SQL Server to choose one as the deadlock victim and roll it back.

Additional session scripts illustrate the same pattern for each transaction.

To avoid deadlocks, consider the following best practices:

Design the database schema wisely to minimize cross‑table updates in differing orders.

Use appropriate indexes to speed up row location and reduce lock duration.

Write efficient queries and keep transactions short and focused.

Limit the time a transaction holds locks; release them as soon as possible.

Lock only the necessary resources, avoiding overly broad lock scopes.

Select suitable transaction isolation levels; lower levels often reduce lock ranges.

Monitor system load and adjust configurations to prevent high contention.

Leverage SQL Server’s deadlock detection and monitoring tools to identify and resolve deadlocks promptly.