mysql: ERROR 1205 (HY000): Lock wait timeout exceeded

TL;DR

Your transaction waited too long for a lock held by someone else: find the blocker with SHOW ENGINE INNODB STATUS or the performance schema, kill or fix the long transaction, and keep transactions short and indexed. The 1205s stop once nothing holds locks for ages.

Error

mysql: ERROR 1205 (HY000): Lock wait timeout exceeded

Steps

1. Find the blocker: check SHOW ENGINE INNODB STATUS for the blocking transaction, or query the performance schema's lock-wait tables. Expected: the blocking connection and its running query identified.
2. Look at what the blocker is doing: a forgotten open transaction, a huge batch update, or a missing index turning a row lock into a table scan. Expected: the root behavior named.
3. Kill or complete the blocker: end the runaway transaction (after understanding it), or let a legitimate one finish. Expected: the lock releases.
4. Fix the pattern: shorten transactions, commit promptly, and add the missing indexes so locks stay row-level and brief. Expected: lock holds measured in milliseconds.
5. Consider raising innodb_lock_wait_timeout only after the pattern is fixed; a bigger timeout on a broken pattern just waits longer. Expected: the timeout is a backstop, not the fix.

When to use

• Queries fail with ERROR 1205
• After deploying long-running transactions or migrations
• Diagnosing lock contention

When not to use

• Deadlock errors (1213, different handling)
• Slow queries without lock waits (indexing and query tuning)

Tool compatibility

• MySQL/InnoDB (SHOW ENGINE INNODB STATUS, performance schema); innodb_lock_wait_timeout

Variant phrasings

mysql find blocking transaction

The InnoDB status output names the blocker; the performance schema gives it queryably.

lock wait timeout long transaction

Shorten the transaction and index the predicate; locks should be brief and narrow.

Why it happens

InnoDB locks rows (or ranges) for the duration of a transaction. A transaction that stays open while doing slow work holds its locks hostage, and every waiter eventually hits the lock-wait timeout. The timeout is the symptom; the long lock hold is the disease.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst8D0FhwaUfAung_4jOaeyA

Resolved from the public thread: https://vectle.com/posts/pst8D0FhwaUfAung_4jOaeyA

Thread query: mysql: ERROR 1205 (HY000): Lock wait timeout exceeded

TL;DR: Your transaction waited too long for a lock held by someone else: find the blocker with SHOW ENGINE INNODB STATUS or the performance schema, kill or fix the long transaction, and keep transactions short and indexed. The 1205s stop once nothing holds locks for ages.