How to handle deadlocks in SQL Server?

To handle deadlocks in SQL Server:

• Use TRY...CATCH with retries.

• Keep transactions small and consistent.

• Access tables in the same order.

• Tune queries and indexing.

In SQL Server, a deadlock happens when two or more sessions block each other, each waiting for a resource the other holds. SQL Server automatically detects deadlocks and kills one process (the deadlock victim) to break the cycle, but you still need to prevent or minimize them.

Here’s how you can handle and prevent deadlocks:

1. Understand and Identify Deadlocks

• Enable Trace Flags (e.g., DBCC TRACEON(1222, -1)) or use Extended Events to capture deadlock graphs.

• SQL Server Profiler can also capture deadlock events.

• Look at the deadlock graph to understand which resources and queries are involved.

2. Prevention Strategies

A. Access Objects in the Same Order

If multiple transactions must access the same set of tables, ensure they do so in the same sequence across the application.

B. Keep Transactions Short

The longer a transaction holds locks, the higher the deadlock risk.

• Avoid user input mid-transaction.

• Commit as soon as possible.

C. Reduce Lock Contention

• Use appropriate isolation levels:

  • READ COMMITTED SNAPSHOT (RCSI) reduces locking by using row versioning.

  • Avoid SERIALIZABLE unless necessary.

• Use NOLOCK (read uncommitted) carefully for non-critical reads.

D. Optimize Queries and Indexes

• Ensure good indexing to avoid long table scans that lock many rows/pages.

• Rewrite queries to touch fewer rows.

E. Break Large Batches

• Process data in smaller chunks instead of one massive transaction.

3. Handling Deadlocks in Code

Even with prevention, you can’t eliminate all deadlocks — you must retry the transaction.

Example in T-SQL:

DECLARE @RetryCount INT = 0 
DECLARE @MaxRetries INT = 3 

WHILE @RetryCount < @MaxRetries 
BEGIN 
BEGIN TRY 
BEGIN TRAN 
-- Your SQL statements here
UPDATE Orders 
SET Status = 'Processed' 
WHERE OrderID = 123 
COMMIT BREAK 
END 
TRY BEGIN 
CATCH 
IF ERROR_NUMBER() = 1205 
-- Deadlock error 
BEGIN 
SET @RetryCount += 1
IF @@TRANCOUNT > 0 
ROLLBACK WAITFOR DELAY '00:00:01'
 -- small pause before retry
END 
ELSE 
BEGIN 
IF @@TRANCOUNT > 0 
ROLLBACK THROW 
END
END 
CATCH 
END

In application code (C#, Java, etc.), catch the SQL error 1205 and retry after a short delay.

4. Monitoring & Continuous Improvement

• Regularly review deadlock reports.

• Look for patterns (e.g., same two tables or queries).

• Tune problematic queries and transactions.