How do you investigate a database deadlock?

Direct answer

What a strong answer covers

How it works

A deadlock is two or more transactions each waiting for a lock the other holds, and the engine breaks it by killing one, which surfaces as an error in the application. Investigation starts from the deadlock graph the engine logs: it names the transactions, the statements they were running, the resources locked and wanted, and which one was chosen as the victim. From that, the competing code paths are identified and the acquisition order compared. Fixes are structural: acquire resources in the same order everywhere, shorten transactions so locks are held briefly, add indexes so updates lock only the intended rows, or lower the isolation where the invariant allows. Retrying the victim is a stopgap that only helps if deadlocks are rare and the transaction is idempotent.

Example

The deadlock log showed transaction A updating orders then order_items and transaction B, a bulk price update, touching order_items then orders. The bulk job was changed to lock orders first in the same order as the checkout path, its batch size was reduced from 50,000 to 1,000 rows, and deadlocks went from dozens a day to zero. A retry with backoff remained in the checkout path for the rare case.

Common mistakes

• Wrapping everything in retry loops instead of reading the deadlock graph.
• Raising the lock timeout, which turns deadlocks into long stalls.
• Assuming the victim transaction was the one at fault.

A useful answer structure

Answer from your own work.

Do not memorise a script: use only examples you can explain honestly in follow-up questions.