How to handle deadlocks in SQL Server?

Question

Cynthia Sathuragiri · Answer

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.

Vijay Bansode · Answer

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 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.

How to handle deadlocks in SQL Server?

Answers (2)