Diagnosing and preventing them.
WHAT AN APPLICATION SEES
An error saying a deadlock was detected and the transaction rolled back.
WHAT TO DO ABOUT IT IN CODE
Catch it, and retry the whole transaction.
WHY THE WHOLE TRANSACTION
Part of it was undone, so resuming is not possible.
HOW MANY TIMES TO RETRY
A small number, with a short delay.
WHAT TO DO IF IT PERSISTS
Treat it as a fault and investigate.
HOW TO SEE WHAT HAPPENED
The engine status output, which records the most recent deadlock in detail.
WHAT IT SHOWS
Both transactions, what they held, and what they waited for.
WHAT PATTERNS CAUSE THEM
Two transactions updating the same rows in opposite order A transaction updating many rows without an index Long transactions overlapping
WHAT PREVENTS THEM
Consistent ordering of updates Short transactions Indexes reducing how many rows are locked
WHY INDEXES MATTER SO MUCH HERE
An unindexed condition locks rows it does not even match.
WHAT ELSE TO CHECK
Whether the application opens a transaction earlier than necessary.
WHAT TO AVOID
Updating a summary row from every transaction.
WHY
It becomes a single contended row, and everything queues on it.
WHAT TO DO INSTEAD
Aggregate periodically, or use a counter designed for contention.
