All 7 Deadlocks questions
More in Deadlocks
How does SQL Server decide which transaction becomes the deadlock victim? What error does an application receive when its transaction is chosen as a deadlock victim, and how should the application respond? How do you capture detailed information about deadlocks that are happening on a server so you can investigate their root cause? What is the difference between a deadlock and normal blocking, and how do you tell them apart when troubleshooting a slow application? How would you read a deadlock graph to identify the exact queries and resources that caused a specific deadlock? What design changes can you make to application code and database access patterns to reduce the frequency of deadlocks?
Advertisement
SQL Server › Deadlocks

What is a deadlock in SQL Server, and why does it happen?

Beginner

Answer

A deadlock happens when two or more transactions each hold a lock that the other transaction needs, creating a cycle where neither can move forward, so SQL Server must step in and end one of them so the other can continue.
-- Transaction 1 locks Row A, then wants Row B
-- Transaction 2 locks Row B, then wants Row A
-- Neither can proceed, so this is a deadlock
Real-world example Two warehouse update jobs run at the same time, each locking a different product row before trying to update the other one, causing SQL Server to cancel one of them automatically to break the cycle.

Common follow-ups: Does a deadlock ever resolve itself without SQL Server intervening?;Is a deadlock the same thing as normal blocking?

Isolation & Locking;Transactions & ACID
All 7 Deadlocks questions