What a Deadlock Is in InnoDB
A deadlock is a mutual lock: transaction A waits for a row held by transaction B, while transaction B at the same time waits for a row held by transaction A. Neither can continue, and InnoDB's built-in detector aborts one of the transactions with a Deadlock found when trying to get lock error. This is not a server failure — it is a normal protection mechanism against a permanent freeze.
Unlike a regular lock, which simply makes a transaction wait, a deadlock requires the application to react: the code must catch the error and retry the transaction.
Shared and Exclusive Row Locks
InnoDB does not lock the whole table, only individual rows and the gaps between them. Understanding lock types helps when reading the diagnostic output.
- Shared lock (S) — set by SELECT ... FOR SHARE, allows other transactions to also read the row.
- Exclusive lock (X) — set by UPDATE, DELETE, and SELECT ... FOR UPDATE, blocks any access by other transactions.
- Gap lock — a lock on the gap between index values, needed to prevent phantom rows during repeatable reads.
How to Find the Latest Deadlock on the Server
Information about the last recorded deadlock is stored in the InnoDB engine status. The command prints both transactions, their locks, and which one the server rolled back.
SHOW ENGINE INNODB STATUS\GIn the LATEST DETECTED DEADLOCK block you see the query text of each transaction, the list of held and waiting locks, and at the end a WE ROLL BACK TRANSACTION line with the number of the cancelled transaction. Reviewing the plan of these queries with EXPLAIN often shows that both queries reach the same rows through different indexes.
Table of Typical Deadlock Causes
| Cause | Example | What to Do |
|---|---|---|
| Different lock order | Transaction A updates rows 1→2, transaction B — 2→1 | Fix a single consistent order for changing rows |
| Long transaction | An open transaction waits for a reply from an external service | Do not keep a transaction open during network calls |
| Missing index | UPDATE runs against an unindexed column | Add an index so a smaller range gets locked |
Common Causes of Deadlocks in Real Applications
In practice, most deadlocks repeat the same scenario.
- Two order handlers deduct stock at the same time in a different row order.
- A trigger on the table inserts into another table, creating a hidden second lock.
- A bulk update without sorting rows by primary key — the lock order becomes random.
- A long transaction holds a lock while the application waits for a reply from a queue or an external API.
How to Reduce the Number of Deadlocks
You cannot eliminate deadlocks completely — they are part of normal MySQL operation under load, but several practices reduce how often they happen.
-- Sort rows by primary key before modifying them
SELECT id FROM orders WHERE status = 'new' ORDER BY id FOR UPDATE;- Update rows in the same order everywhere in the code.
- Keep transactions short: do not perform external calls between BEGIN and COMMIT.
- Add indexes for the WHERE conditions in UPDATE and DELETE, so only the needed range gets locked.
- Retry the transaction automatically on error 1213 (deadlock); two or three attempts are usually enough.
Summary: Deadlock Diagnostics Checklist
- Read LATEST DETECTED DEADLOCK in SHOW ENGINE INNODB STATUS.
- Determine the lock order in both transactions.
- Check whether a suitable index exists for the query conditions.
- Shorten the transaction to the minimum necessary operations.
- Add an automatic transaction retry on error 1213 to the code.