Deadlock in SQL Server

Deadlock in SQL Server is one of the most common problems in database-driven applications, especially in large systems where multiple users access the same data at the same time. Understanding deadlock is very important for developers, DBAs, and backend engineers because it directly affects performance and data consistency.

This article explains deadlock in simple language, with examples, causes, and solutions.

1. What is Deadlock?

A deadlock happens when two or more transactions block each other by holding locks on resources and waiting for the other transaction to release its lock.

In simple words:

Transaction A is waiting for Transaction B
Transaction B is waiting for Transaction A
Both are stuck → This is Deadlock

SQL Server automatically detects this situation and kills one transaction. The killed transaction is called Deadlock Victim.

2. Example of Deadlock

Consider two tables:

Users
Orders

Transaction 1

BEGIN TRAN

UPDATE Users SET Name = 'A' WHERE Id = 1

WAITFOR DELAY '00:00:05'

UPDATE Orders SET Amount = 100 WHERE Id = 1

COMMIT

Transaction 2

BEGIN TRAN

UPDATE Orders SET Amount = 200 WHERE Id = 1

WAITFOR DELAY '00:00:05'

UPDATE Users SET Name = 'B' WHERE Id = 1

COMMIT

What happens?

SQL Server detects deadlock and kills one transaction.

Error:

Transaction (Process ID 55) was deadlocked on lock resources
with another process and has been chosen as the deadlock victim.

3. Why Deadlock Happens

Deadlock usually happens because of:

1. Different order of table access

Bad:

T1 → Users → Orders
T2 → Orders → Users

Good:

T1 → Users → Orders
T2 → Users → Orders

Same order = No deadlock

2. Long transactions

If transaction runs too long, locks stay longer → higher chance of deadlock.

Bad:

BEGIN TRAN

SELECT *
WAIT 10 sec
UPDATE

COMMIT

Good:

BEGIN TRAN

UPDATE

COMMIT

3. Missing indexes

If index not present, SQL Server scans full table → more locks → deadlock possible.

Bad:

UPDATE Users WHERE Email = 'abc@test.com'

Good:

CREATE INDEX IX_Email ON Users(Email)

4. Using SELECT without proper isolation

Example:

SELECT * FROM Orders WITH (UPDLOCK)

Locks stay longer → deadlock possible

5. Multiple users updating same rows

High concurrency systems like:

• Banking
• E-commerce
• Chat apps
• Order systems
• CMS / Blog systems
• Deadlock chances are high.

4. How SQL Server Handles Deadlock

SQL Server automatically:

• Detect deadlock
• Select victim
• Kill one transaction
• Allow other to continue

Victim selection based on:

• Less cost transaction
• DEADLOCK_PRIORITY
• Log usage

Example:

SET DEADLOCK_PRIORITY LOW

5. How to Prevent Deadlock

Rule 1 — Access tables in same order

Good:

Users → Orders → Payments

Always same order in all SP / queries.

Rule 2 — Keep transactions short

Bad:

BEGIN
SELECT
WAIT
UPDATE

Good:

BEGIN
UPDATE
COMMIT

Rule 3 — Use proper indexes

CREATE INDEX IX_UserId ON Orders(UserId)

Less scan = less lock

Rule 4 — Use TRY CATCH and retry

BEGIN TRY

BEGIN TRAN

UPDATE Users SET Name='A' WHERE Id=1

COMMIT

END TRY

BEGIN CATCH

IF ERROR_NUMBER() = 1205
BEGIN
    PRINT 'Deadlock detected, retry'
END

END CATCH

Error 1205 = Deadlock

Rule 5 — Use NOLOCK carefully

SELECT * FROM Users WITH (NOLOCK)

Pros:

• No blocking

Cons:

• Dirty data
• Use only for reports.

6. How to Detect Deadlock

Method 1 — SQL Server Profiler

Check event:

Deadlock graph

Method 2 — Extended Events

system_health session

Method 3 — Query

SELECT * FROM sys.dm_tran_locks

7. Real Life Example

Deadlock common in:

• Order placing
• Payment processing
• Inventory update
• Chat message save
• Article update
• Bulk email system

Large websites like:

• Amazon
• Flipkart
• Facebook
• Banking systems
• All must handle deadlock.

8. Conclusion

Deadlock is not a bug.
Deadlock is a normal situation in multi-user systems.

Good developer should:

• Write short transactions
• Use indexes
• Maintain lock order
• Handle retry logic
• Monitor deadlocks

If handled correctly → system becomes stable and fast.