ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ID: database/mysql-lock-wait-timeout
Version Compatibility
| Version | Status | Introduced | Deprecated | Notes |
|---|---|---|---|---|
| 8 | active | — | — | — |
Root Cause
A transaction waited longer than innodb_lock_wait_timeout (default 50 seconds) to acquire a row-level lock held by another transaction. The waiting transaction is rolled back. Caused by long-running transactions, missing indexes causing table scans to hold excessive locks, or application-level queuing issues.
genericWorkarounds
-
88% success Identify and kill the blocking transaction, then optimize to prevent recurrence
Run: SELECT * FROM information_schema.INNODB_TRX; and SELECT * FROM performance_schema.data_locks; to find the blocking transaction. Kill it with: KILL <thread_id>; Then investigate why it held locks so long - often a missing COMMIT or long-running query.
-
85% success Add indexes to reduce lock scope and shorten transaction durations
Without proper indexes, InnoDB locks more rows than necessary (gap locks on full table scans). Add indexes on columns used in WHERE clauses of UPDATE/DELETE statements. Also break large transactions into smaller batches to reduce lock hold time.
Dead Ends
Common approaches that don't work:
-
Increasing innodb_lock_wait_timeout to very large values (e.g., 3600 seconds)
80% fail
This only delays the timeout without fixing the underlying contention. Transactions will hold locks for longer, causing other transactions to queue up, making the overall problem worse and potentially leading to connection exhaustion.
-
Setting transaction isolation to READ UNCOMMITTED globally
75% fail
READ UNCOMMITTED avoids some read locks but introduces dirty reads, phantom reads, and non-repeatable reads. It does not prevent write lock contention, which is the usual cause of this error. It trades correctness for a minimal reduction in lock contention.