Detailed explanation
A deadlock occurs when two or more transactions form a circular dependency: each holds a lock that another needs, so none can proceed. For example, transaction A updates account 1 and holds its row lock, while transaction B updates account 2 and holds that lock. If A then updates account 2 and B then updates account 1, both wait indefinitely unless the database intervenes. Unlike ordinary blocking, where one transaction can finish and release its locks, a deadlock contains a cycle with no independent path forward.
Database engines commonly detect deadlocks by examining a wait-for graph, where transactions are nodes and waiting relationships are edges. A cycle indicates a deadlock. The engine chooses a victim and aborts work to break the cycle; the exact rollback scope and victim-selection rules depend on the database. SQL Server and InnoDB typically roll back the victim transaction, allowing another transaction to continue. Applications must handle the resulting error rather than assume every write succeeds. Deadlocks can involve row, page, table, range, or other locks, depending on the engine and operation.
The strongest practical prevention technique is consistent resource ordering across all code paths. In the account example, both transactions should explicitly update account 1 before account 2. Keep transactions short, commit promptly, and avoid user interaction or network calls while locks are held. Suitable indexes can reduce scanned records, lock footprints, and execution time, although their precise effect depends on the engine and isolation level. Choose isolation levels deliberately: row-versioning approaches can reduce some reader-writer conflicts, but do not eliminate writer-writer deadlocks. Also, do not assume a multi-row UPDATE acquires locks in ascending key order merely because its input appears sorted.
In production, investigate deadlock reports to identify the statements, resources, access paths, and transaction boundaries involved. Add bounded retries with backoff for deadlock failures, restarting the complete logical transaction after rollback and preventing duplicate external side effects. Interviewers usually expect you to explain the cycle, distinguish deadlock from blocking, describe consistent ordering and short transactions, and recognise that prevention reduces risk rather than guaranteeing zero deadlocks.
Key points to remember
- A deadlock is a circular wait involving two or more transactions.
- The database typically selects a victim and aborts work to release locks.
- Access shared resources in the same explicit order across transaction paths.
- Keep transactions short and use appropriate indexes to reduce lock contention.
- Inspect deadlock reports and implement bounded, transaction-level retries.
Common mistakes candidates make
- Calling every long-running lock wait a deadlock without checking for a cycle.
- Assuming indexes, row-versioning isolation, or sorted input guarantee deadlock-free execution.
- Retrying only the failed statement without checking rollback state and transaction correctness.
Likely follow-up questions
How is a deadlock different from a lock timeout?
A deadlock requires a cycle of waiting transactions; a lock timeout means a wait exceeded a configured duration. A timeout can happen during ordinary blocking even when no cycle exists.
Does using an MVCC database eliminate deadlocks?
No; MVCC allows many reads to use row versions rather than conflict with writes. Writers can still wait on incompatible locks and form deadlock cycles.
How should an application retry after a deadlock?
Recognise the database-specific deadlock error, ensure rollback is complete, and retry the entire logical transaction with bounded backoff. Keep external actions such as payments or notifications idempotent, or coordinate them through a transactional outbox.
Interview tip: Walk through the two-account circular wait, show the consistent-order fix, and finish with deadlock diagnostics and safe retries.