How do you handle deadlocks in SQL?

It comes to handling deadlocks in SQL, my goal is twofold — first, to prevent them through good design, and second, to detect and resolve them quickly when they do occur.

A deadlock happens when two or more transactions hold locks on resources that each other needs, resulting in a circular wait — neither can proceed, so the database engine detects this and automatically chooses one transaction as the victim to roll back, allowing the other to continue.

For example, imagine Transaction A locks the Orders table and needs access to Customers, while Transaction B locks Customers and needs Orders. Both wait for each other — that’s a deadlock.

In one of my projects involving order processing and billing, we encountered frequent deadlocks during peak load. We resolved them by systematically identifying the root cause and applying a mix of design and query-level fixes.

My approach generally involves these steps:

1. Detecting deadlocks:
   I start by enabling deadlock tracing using tools like:
   • Extended Events or SQL Profiler to capture deadlock graphs.
   • System Health Session in SQL Server, which automatically logs deadlocks.
   • Querying DMVs like sys.dm_tran_locks and sys.dm_exec_requests to identify blocked sessions.
     These tools visually show which queries and resources were involved in the deadlock, making it easier to pinpoint patterns.
2. Analyzing deadlock patterns:
   Once I have the deadlock graph, I analyze which queries and tables are involved. In one case, I noticed two long-running update queries accessing tables in different orders. The fix was simple — standardize the locking order so that both transactions acquire resources in the same sequence. This eliminated the circular dependency.
3. Optimizing transactions:
   I always try to keep transactions short and narrow — committing as soon as possible and avoiding user input or unnecessary logic between BEGIN TRAN and COMMIT. For instance, in an ETL process, breaking large updates into smaller batches (like 10,000 rows at a time) reduced the duration locks were held, significantly lowering deadlock frequency.
4. Using appropriate isolation levels:
   I tune isolation levels based on the workload. For read-heavy systems, enabling READ COMMITTED SNAPSHOT ISOLATION (RCSI) allows readers to access a versioned snapshot of the data, eliminating contention between readers and writers. In one analytics system, this reduced both blocking and deadlocks dramatically.
5. Index and query optimization:
   Sometimes deadlocks occur because queries scan too much data, holding locks on more rows than necessary. By adding proper indexes and ensuring selective WHERE clauses, I reduce the lock footprint. For example, adding an index on CustomerID in a billing update query stopped full table scans that previously caused contention.
6. Graceful error handling in code:
   Even with best practices, occasional deadlocks are unavoidable in high-concurrency systems. I handle this by retrying the transaction in application logic. SQL Server returns error code 1205 for deadlocks, so I wrap critical transactions in retry logic with a small delay before resubmission. This ensures user experience isn’t affected.

A challenge I’ve faced is balancing isolation levels — while snapshot isolation helps prevent deadlocks, it increases tempdb usage. So I monitor tempdb closely and size it properly before enabling snapshot-based strategies.

In summary, I handle deadlocks by combining detection tools, consistent locking order, shorter transactions, proper indexing, and retry logic. The key is prevention through design and continuous monitoring — once queries are structured and isolation levels tuned properly, deadlocks drop to near zero even under high concurrency.