All 7 Isolation & Locking questions
More in Isolation & Locking
What is a lock in SQL Server, and why does the database engine use locks? What are the four standard transaction isolation levels in SQL Server, and what does isolation level actually control? What is the difference between an optimistic and a pessimistic approach to handling concurrency, and where does SQL Server's snapshot isolation fit into this? What are lock escalation and its potential impact on a busy system? How would you diagnose and resolve a blocking problem where many queries are waiting on a single long running transaction? How does Read Committed Snapshot Isolation differ from regular Read Committed isolation, and what tradeoff does enabling it introduce?
Advertisement
SQL Server › Isolation & Locking

What is a deadlock, and how does SQL Server automatically handle one when it occurs?

Intermediate

Answer

A deadlock happens when two or more transactions are each waiting for a lock held by the other, creating a cycle where neither can proceed, and SQL Server automatically detects this situation, choosing one transaction as the deadlock victim, rolling it back and returning an error, while allowing the other transaction to continue and complete successfully.
-- Transaction 1 locks OrderId 1, then waits for OrderId 2
-- Transaction 2 locks OrderId 2, then waits for OrderId 1
-- SQL Server detects this cycle and kills one transaction
Real-world example An order processing application encounters an occasional deadlock during a busy sales event, and after SQL Server automatically rolls back one of the conflicting transactions, the application simply retries it and the order completes successfully on the second attempt.

Common follow-ups: How do you write application code that properly handles and retries a deadlock error?;What tools help you investigate the actual cause of a deadlock after it happens?

Transactions & ACID;Error Handling with TRY CATCH
All 7 Isolation & Locking questions