MySQL — Lock wait timeout exceeded

Diagnose MySQL lock waits from long transactions, missing indexes, or conflicting update order, and fix the wait instead of only raising the timeout.

advanceddebuggingmysqldatabase

Symptoms

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
One transaction hangs while another holds a row or metadata lock
The error appears under concurrent writes and disappears at low traffic
Retries sometimes succeed and sometimes fail again

Possible causes

A transaction stays open while waiting for user input or a network call
Two transactions update the same rows in different orders
A missing index makes an UPDATE scan and lock many more rows
A DDL operation holds a metadata lock while waiting for an open transaction
The lock wait timeout is shorter than the legitimate batch duration

Diagnosis steps

Inspect InnoDB transactions and locks

SHOW ENGINE INNODB STATUS\G

Interpretation

The LATEST DETECTED DEADLOCK and TRANSACTIONS sections show the waiting statement, the blocking transaction, and the locks held.

Query performance_schema for the current wait

SELECT * FROM performance_schema.data_locks; SELECT * FROM performance_schema.data_waits_current;

Interpretation

The waiting thread and the blocking thread are visible with the index and lock mode involved.

List long-running transactions

SELECT trx_id, trx_state, trx_started, trx_query FROM information_schema.innodb_trx ORDER BY trx_started;

Interpretation

A transaction open for minutes is holding locks while doing work that should be outside the transaction.

Root cause

A transaction waited longer than innodb_lock_wait_timeout for a row or metadata lock held by another transaction. The underlying issue is usually transaction length, lock order, or an unindexed predicate.

Fix

Keep transactions short, move network calls and user interaction outside them, index the predicates used by updates, acquire rows in a consistent order, and add a bounded retry for transient conflicts. Raise the timeout only when the wait is legitimate and understood.

Risk note

Raising innodb_lock_wait_timeout hides contention and can make an outage last longer. A DDL waiting on metadata locks can block every query on the table.

Verify the fix

Reproduce the two-session conflict, confirm the waiting statement completes after the fix, and monitor lock wait time and long transactions. Add a concurrency test for the update path.

A wait is not the root cause

Lock wait timeout exceeded means one transaction waited too long for a lock. The fix is not necessarily a larger timeout; it is usually a shorter transaction, a better index, or a consistent access order. Start with SHOW ENGINE INNODB STATUS or performance_schema and identify both the waiter and the blocker.

SELECT trx_id, trx_state, trx_started, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

Look for long transactions and broad locks

A transaction that calls an external API holds locks for the duration of the call. A missing index on an UPDATE ... WHERE status = ... can lock many rows instead of a few. A DDL statement can wait for a metadata lock held by an idle-in-transaction session, blocking all queries on the table.

Fix and verify

Shorten transactions, index predicates, and sort the ids before updating. Add a bounded retry for deadlocks (1213) and lock waits when the operation is idempotent. Verify with a two-session reproduction and a concurrency test. Monitor innodb_row_lock_time and the oldest transaction age; a rising oldest transaction is an early warning.