What is a deadlock in SQL Server, and how do you prevent it?
Quick Answer
A deadlock is when two transactions each hold a lock the other needs, so neither can proceed. SQL Server's deadlock monitor detects the cycle, picks the cheaper transaction as the victim, kills it with error 1205, and lets the other finish. Prevent them with consistent lock ordering, short transactions, the right indexes, and lower isolation where safe.
A deadlock is a cycle: transaction A holds a lock B wants, and B holds a lock A wants. SQL Server detects it, picks the cheaper one to roll back — the victim, with error 1205 — and the other one completes. The classic cause is two code paths that touch the same tables in a different order. So the prevention is: always access tables in the same order, keep transactions short — no HTTP calls or user input inside a transaction — and make sure the queries have proper indexes, because a scan locks far more rows than a seek. Turning on read-committed snapshot removes a lot of reader/writer deadlocks. And since they're transient, I catch 1205 and retry with a small backoff.
Typical cause: transaction A updates Orders then Customers; transaction B updates Customers then Orders. Each grabs the first lock, then waits forever for the second.
Prevention:
- Access objects in the same order everywhere.
- Keep transactions short — don't do app logic, user prompts or HTTP calls inside one.
- Right indexes — a missing index causes a scan that locks more rows than needed, widening the collision surface.
- Lower isolation — RCSI removes many reader/writer deadlocks.
- Retry the victim — deadlocks are transient; catch 1205 and retry with backoff.
Diagnose with the deadlock graph (Extended Events / system_health session) — it names the two statements and the resources.