QueryKings | InnoDB lock waits and deadlocks: reading the evidence

Two error messages account for most of the lock-related pages we get called about:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

They are different problems. A lock wait timeout means one transaction held a row lock longer than innodb_lock_wait_timeout (50 seconds by default) while another waited. A deadlock means two transactions each held a lock the other needed, and InnoDB broke the cycle by rolling one back — immediately, by design, in microseconds. Deadlocks are cheap and self-healing; lock waits are the ones that pile up connections until the application falls over.

Both are diagnosable from evidence the server already collects. The mistake is treating them as noise and adding a retry loop.

Capture the deadlock, not the exception

SHOW ENGINE INNODB STATUS holds only the most recent deadlock, so on a busy system the one you care about is usually gone by the time you look. Turn on persistent logging:

SET GLOBAL innodb_print_all_deadlocks = ON;

Every deadlock then lands in the error log with both transactions, their held and requested locks, and the victim choice. The overhead is a log line per deadlock; on RDS and Aurora it is a parameter-group change and the output goes to the error log stream. Leave it on. We have never seen this setting cause a problem, and we have often seen its absence cost a week of guessing.

Reading the LATEST DETECTED DEADLOCK section

The block names two transactions. For each, read three things in this order:

The statement — the query text under *** (1) TRANSACTION:. This is the statement that was waiting, not necessarily the one that caused the problem.
WAITING FOR THIS LOCK TO BE GRANTED — names the index, and the lock mode: X, S, gap before rec, insert intention, rec but not gap.
HOLDS THE LOCK(S) — what that transaction already had. This is the half people skip, and it is where the ordering bug lives.

The index name matters more than the table name. lock_mode X locks gap before rec on a secondary index tells you the transaction is locking a range, not a row, and any insert into that range will block. That single line explains most "but they touch different rows" deadlocks.

A worked pattern: transaction A updates order 100 then order 200; transaction B updates 200 then 100. Both hold one lock and want the other. There is no server setting that fixes this. The fix is to make both code paths acquire rows in a deterministic order — sort the ID list before the loop — which is a two-line change once you can see the cycle.

Live lock waits: the Performance Schema view

For a lock wait happening right now, on 8.0 use performance_schema.data_locks and data_lock_waits (the old information_schema.innodb_locks tables were removed in 8.0):

SELECT r.trx_id            AS waiting_trx,
       r.trx_mysql_thread_id AS waiting_thread,
       r.trx_query         AS waiting_query,
       b.trx_id            AS blocking_trx,
       b.trx_mysql_thread_id AS blocking_thread,
       b.trx_query         AS blocking_query,
       TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_secs
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id
ORDER BY wait_secs DESC;

sys.innodb_lock_waits wraps the same data with a suggested KILL statement. Note the frequent surprise: blocking_query is often NULL. That means the blocking transaction is open but idle — it ran a statement, took locks, and then the application went off to call an HTTP API or wait for a user. The lock is held until commit, no matter how long the connection sits quiet.

That is the single most common cause of lock wait timeouts we find: a transaction spanning a network call. No amount of index work fixes it.

Gap locks, and why REPEATABLE READ surprises people

Under the default REPEATABLE READ isolation level, InnoDB takes next-key locks — a row lock plus a lock on the gap before it — on the index records a locking read or write scans. This is what prevents phantom rows, and it is why:

DELETE FROM events WHERE created_at < '2026-01-01' can block inserts of new events, if the scan walks an index range that reaches the end.
A SELECT ... FOR UPDATE on a non-indexed column locks effectively every row it scanned, because it locked index entries, not the rows you meant.
Two concurrent INSERT ... ON DUPLICATE KEY UPDATE statements on the same non-existent key deadlock on insert-intention locks.

The practical consequences are worth stating plainly:

Locks follow the access path, not the result set. A statement that modifies one row but scans a million locks along the scan. Fixing the index is a locking fix, not only a speed fix. This is the most direct connection between query tuning and concurrency, and the reason we run EXPLAIN on write statements too.
READ COMMITTED removes most gap locks and releases non-matching row locks after each statement. It is a real option for high-concurrency OLTP workloads, and it is the default many teams should have chosen. The cost: statement-based replication is unsafe under it (you need binlog_format = ROW, which most people already run), and your application must tolerate non-repeatable reads inside a transaction. Change it deliberately, per session first, with a load test — not globally on a Friday.

The four fixes that hold

In rough order of how often they are the right answer:

Shorten transactions. Open late, commit early, and never hold a transaction across an external call, a message publish, or a user interaction. Most lock wait timeouts die here.
Index the predicate of the write. Add the index that turns a locking scan into a locking point lookup. Verify with EXPLAIN on the SELECT equivalent of the UPDATE.
Impose a deterministic ordering. Sort IDs before batched updates; make every code path touch parent then child, never the reverse.
Right-size the batch. A 500,000-row DELETE is a half-million locks held to commit. Chunk it into a few thousand rows per transaction — which also keeps replicas from stalling behind a single giant event group.

Retries are a fifth item, not a first one. Deadlock retry logic with jitter is legitimate and every OLTP application should have it, because deadlocks can never be reduced to zero. But retries applied to a lock-ordering bug simply convert a visible error into latency and a mysterious throughput ceiling.

What to measure afterwards

Track Innodb_row_lock_time_avg and Innodb_row_lock_waits from SHOW GLOBAL STATUS, plus the deadlock count from the error log, on the same dashboard as query latency. Lock contention is one of the few database problems that gets abruptly worse rather than gradually — throughput climbs fine until concurrency crosses a threshold and the wait queue goes vertical. Having the before-number is what lets you prove a fix worked rather than believe it did.

If you are chasing lock waits under load and the evidence above is not adding up, that is a good hour to spend with someone who reads these dumps weekly. Send us the deadlock block and the schema for the tables involved; the shape of the problem is usually visible in both.