Deadlocks are scary.
Suddenly, your application throws this error:
Transaction (Process ID 52) was deadlocked on lock resources with another process
and has been chosen as the deadlock victim.
But here’s the truth:
👉 Deadlocks are not bugs.
👉 Deadlocks are not crashes.
👉 Deadlocks are SQL Server protecting your data.
Let’s understand them properly.
1️⃣ What is a Deadlock?
A deadlock happens when:
- Session A holds Lock 1 and waits for Lock 2
- Session B holds Lock 2 and waits for Lock 1
Neither can proceed.
SQL Server automatically kills one transaction (victim) to resolve it.
In Microsoft SQL Server, this happens instantly.
2️⃣ Simple Deadlock Example
Session 1:
BEGIN TRAN;
UPDATE Orders SET Amount = 100 WHERE OrderId = 1;
WAITFOR DELAY '00:00:05';
UPDATE Customers SET Name = 'A' WHERE CustomerId = 1;
COMMIT;
Session 2:
BEGIN TRAN;
UPDATE Customers SET Name = 'B' WHERE CustomerId = 1;
WAITFOR DELAY '00:00:05';
UPDATE Orders SET Amount = 200 WHERE OrderId = 1;
COMMIT;
Boom 💥 Deadlock.
3️⃣ Why Deadlocks Happen
✔ Different table access order
✔ Long transactions
✔ Missing indexes
✔ High isolation level (SERIALIZABLE)
✔ Large updates
4️⃣ Deadlock vs Blocking
| Feature | Blocking | Deadlock |
|---|---|---|
| Waits? | Yes | Yes |
| Automatically resolved? | No | Yes |
| Error thrown? | No | Yes |
Deadlock throws error.
Blocking just waits.
5️⃣ Capturing Deadlocks
Option 1: Extended Events (Best Way)
CREATE EVENT SESSION DeadlockMonitor
ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file;
Option 2:
Check system health session (default).
6️⃣ Reading Deadlock Graph
Deadlock graph shows:
✔ Victim process
✔ Lock types
✔ Resources involved
✔ Execution details
In SSMS, open .xel file and view the graph visually.
7️⃣ How SQL Server Chooses Victim
SQL Server picks the transaction with:
- Lower rollback cost
- Or based on DEADLOCK_PRIORITY
Set priority:
SET DEADLOCK_PRIORITY LOW;
Options:
- LOW
- NORMAL
- HIGH
- Numeric (-10 to 10)
8️⃣ Fixing Deadlocks – Proven Solutions
✅ 1. Access Tables in Same Order
Always update tables in consistent order across procedures.
Example:
- First Orders
- Then Customers
Never reverse it.
✅ 2. Add Proper Indexes
Missing index = longer locks = more deadlocks.
Check execution plans.
✅ 3. Keep Transactions Short
❌ Avoid:
BEGIN TRAN
-- user input
-- complex logic
-- delays
COMMIT
✔ Move logic outside transaction.
✅ 4. Use READ COMMITTED SNAPSHOT
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON;
Reduces read-write deadlocks significantly.
✅ 5. Retry Logic in Application
Deadlocks can still happen.
Best practice:
- Catch error 1205
- Retry transaction
Example (pseudo logic):
If deadlock
Retry up to 3 times
9️⃣ Real Production Scenario
❌ Issue
Payment API randomly failing.
🔍 Root Cause
Two stored procedures updating:
- Payments
- Accounts
in opposite order.
✅ Fix
Standardized table access order.
Deadlocks disappeared.
🔟 Monitoring Deadlocks
SELECT *
FROM sys.dm_tran_locks;
Also monitor:
- Wait stats
- Blocking sessions
- Long-running transactions
1️⃣1️⃣ Deadlock Prevention Checklist
✔ Keep transactions short
✔ Access tables in consistent order
✔ Use proper indexing
✔ Avoid unnecessary SERIALIZABLE isolation
✔ Enable RCSI in high concurrency systems
✔ Implement retry logic
1️⃣2️⃣ Important Truth
Deadlocks are normal in high-concurrency systems.
Goal is not to eliminate them completely.
Goal is:
✔ Minimize
✔ Detect
✔ Handle gracefully
✍️ Conclusion
If you understand deadlocks, you understand:
- Locking
- Isolation
- Concurrency
- Real production behavior
That makes you a serious backend professional 💪
Your blog series now covers:
✔ Security
✔ Query Store
✔ Isolation Levels
✔ Deadlocks