Deadlocks are one of the most feared issues in SQL Server production environments. They don’t always appear in development, but when they hit production, they can freeze applications, fail transactions, and frustrate users.
This blog explains what deadlocks are, why they occur, and how to prevent them effectively.
1️⃣ What Is a Deadlock in SQL Server?
A deadlock occurs when:
- Session A locks Resource 1 and waits for Resource 2
- Session B locks Resource 2 and waits for Resource 1
👉 SQL Server resolves this by killing one session (deadlock victim).
2️⃣ Deadlock Error Message
❌ Error Message
Msg 1205, Level 13, State 51
Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.
3️⃣ Simple Deadlock Example
Session 1
BEGIN TRAN;
UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1;
-- waits
UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 2;
COMMIT;
Session 2
BEGIN TRAN;
UPDATE Accounts SET Balance = Balance - 50 WHERE Id = 2;
-- waits
UPDATE Accounts SET Balance = Balance + 50 WHERE Id = 1;
COMMIT;
📌 Both sessions wait on each other → deadlock
4️⃣ Common Causes of Deadlocks
✔ Different order of table access
✔ Long-running transactions
✔ Missing indexes
✔ Cursor usage
✔ User-defined functions inside transactions
5️⃣ How SQL Server Chooses Deadlock Victim
SQL Server selects:
- Transaction with the lowest cost
- Transaction with the lowest rollback impact
The killed session receives error 1205.
6️⃣ How to Detect Deadlocks
🔍 Method 1: SQL Server Error Log
EXEC xp_readerrorlog;
🔍 Method 2: Extended Events (Recommended)
Use:
xml_deadlock_report
🔍 Method 3: SQL Server Profiler (Old)
Not recommended for production.
7️⃣ How to Prevent Deadlocks
✅ 1. Access Objects in Same Order
Always update tables in a consistent order.
✅ 2. Keep Transactions Short
❌ Avoid user input inside transactions
❌ Avoid delays
✅ 3. Use Proper Indexes
Indexes reduce lock duration and scope.
✅ 4. Use SET DEADLOCK_PRIORITY
SET DEADLOCK_PRIORITY LOW;
Lower priority session will be killed first.
✅ 5. Use Snapshot Isolation (When Possible)
ALTER DATABASE MyDB
SET READ_COMMITTED_SNAPSHOT ON;
Reduces read locks.
8️⃣ Handling Deadlocks in Stored Procedures
✅ Retry Logic Example
DECLARE @Retry INT = 3;
WHILE @Retry > 0
BEGIN
BEGIN TRY
EXEC dbo.TransferAmount;
BREAK;
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 1205
SET @Retry = @Retry - 1;
ELSE
THROW;
END CATCH
END
9️⃣ Deadlocks vs Blocking
| Blocking | Deadlock |
|---|---|
| Temporary wait | Permanent wait |
| Eventually resolves | One session killed |
| Normal behavior | Error condition |
🔟 Deadlock Best Practices Checklist
✔ Use consistent object order
✔ Keep transactions short
✔ Avoid cursors
✔ Index foreign keys
✔ Handle error 1205
✔ Monitor deadlocks
📌 Deadlock-Safe Stored Procedure Template
CREATE PROCEDURE dbo.SafeTransfer
@FromId INT,
@ToId INT,
@Amount DECIMAL(10,2)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - @Amount WHERE Id = @FromId;
UPDATE Accounts SET Balance = Balance + @Amount WHERE Id = @ToId;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
END
✍️ Conclusion
Deadlocks are not bugs; they are design problems. With correct transaction design, indexing, and error handling, most deadlocks can be completely avoided.
Understanding deadlocks makes you production-ready as a SQL Server developer.