MySQL: ERROR 1205 Lock wait timeout exceeded — transaction waiting too long for a row lock
1205 Lock wait timeout exceeded — row lock contention
Verified against MySQL docs: InnoDB Locking — innodb_lock_wait_timeout, MySQL source: storage/innobase/lock/lock0lock.cc, MySQL docs: Using InnoDB Transaction and Locking Information · Updated June 2026
> quick_fix
Your transaction tried to acquire a row lock but another transaction has been holding it too long (default: 50 seconds). Find the blocking transaction with SHOW ENGINE INNODB STATUS, then either kill it or optimize your transactions to be shorter.
-- Find the blocking transaction:
SHOW ENGINE INNODB STATUS\G
-- Or check the process list:
SHOW PROCESSLIST;
-- Kill the blocking query if needed:
KILL <process_id>;
-- Check current timeout setting:
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';What causes this error
InnoDB uses row-level locking for INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE. When Transaction A holds a lock on a row and Transaction B tries to modify the same row, B waits. If B waits longer than innodb_lock_wait_timeout (default: 50 seconds), MySQL kills B's statement and returns error 1205. This is a protection mechanism — without it, a stuck transaction could block other transactions indefinitely. The root cause is usually a long-running transaction that's holding locks for too long, or a missing index causing InnoDB to lock more rows than necessary.
How to fix it
- 01
step 1
Find the blocking transaction
SHOW ENGINE INNODB STATUS shows the 'LATEST DETECTED DEADLOCK' and 'TRANSACTIONS' sections. Look for transactions in 'LOCK WAIT' state and trace which transaction holds the lock they're waiting for.
-- Check InnoDB lock status: SELECT * FROM performance_schema.data_lock_waits; -- See which transactions hold locks: SELECT * FROM information_schema.innodb_trx ORDER BY trx_started; - 02
step 2
Check for missing indexes
Without an index on the WHERE clause column, InnoDB does a full table scan and locks every row it examines (not just the matching rows). Add an index on the filtered column to reduce the lock scope.
-- Check if the query uses an index: EXPLAIN SELECT * FROM orders WHERE user_id = 123 FOR UPDATE; -- If type is 'ALL' (full table scan), add an index: ALTER TABLE orders ADD INDEX idx_user_id (user_id); - 03
step 3
Shorten transaction duration
Long transactions hold locks longer. Move non-database work outside the transaction. If you're doing API calls or computation between BEGIN and COMMIT, restructure to: gather data → compute → begin transaction → write → commit.
- 04
step 4
Consider increasing the timeout (temporary)
If the blocking transaction is legitimate (e.g., a batch update), you can temporarily increase the timeout for the waiting session. But this is a band-aid, not a fix.
-- Per-session override (not global): SET innodb_lock_wait_timeout = 120;
How to verify the fix
- The query completes without error 1205.
- SHOW ENGINE INNODB STATUS shows no long-running transactions.
- EXPLAIN shows the query uses an index (not a full table scan).
Why 1205 happens at the runtime level
InnoDB's lock manager (storage/innobase/lock/lock0lock.cc) maintains a lock table indexed by (space_id, page_no, heap_no) for row locks. When a transaction requests a lock that conflicts with an existing lock, the requesting transaction is placed in a wait queue. A background thread checks wait durations against innodb_lock_wait_timeout. When the timeout fires, the waiting transaction's current statement is rolled back (not the whole transaction unless innodb_rollback_on_timeout is ON, which is OFF by default) and error 1205 is returned to the client. The key detail: by default, only the current statement is rolled back, not the entire transaction — the transaction remains open with partial work, which can cause data inconsistency if the application doesn't handle this correctly.
Common debug mistakes for 1205
- Not knowing that only the current statement (not the whole transaction) is rolled back by default — after error 1205, your transaction is still open with prior statements committed to the transaction log. Either ROLLBACK explicitly or set innodb_rollback_on_timeout = ON.
- Blaming the database when the real problem is application code holding a transaction open during an HTTP call — a slow external API means your transaction holds locks for seconds or minutes.
- Adding SELECT ... FOR UPDATE everywhere for 'consistency' when plain SELECT with MVCC would work — unnecessary exclusive locks are the number one cause of lock contention in MySQL applications.
- Increasing innodb_lock_wait_timeout globally to 300+ seconds — this means every lock contention incident takes 5 minutes to surface, making the application feel frozen instead of failing fast.
- Missing indexes on foreign key columns — InnoDB locks rows via index lookups. A missing index on a foreign key causes a full table scan lock on the child table during parent row updates.
When 1205 signals a deeper problem
Persistent lock wait timeouts in a production MySQL database almost always point to one of two architectural issues: (1) the application holds transactions open across network calls (API requests, cache lookups, queue publishes), turning millisecond database operations into second-long lock-holding periods, or (2) the schema is missing indexes on columns used in WHERE and JOIN clauses, causing InnoDB to lock entire tables instead of individual rows. The fix for (1) is restructuring application code to minimize transaction scope; the fix for (2) is running EXPLAIN on every query involved in lock contention and adding the missing indexes.
Editor's take
The most insidious lock wait timeout I've debugged was caused by a Slack notification. An e-commerce checkout flow opened a transaction, deducted inventory, sent a Slack webhook notification to the sales channel, then committed. The Slack API normally responded in 200ms, but one afternoon Slack had a partial outage and responses took 45 seconds. Every checkout held an inventory row lock for 45 seconds, and every subsequent checkout on the same product timed out at the 50-second innodb_lock_wait_timeout. The fix was moving the Slack notification outside the transaction — a 30-second fix that would have prevented 3 hours of lost sales.
The critical detail most developers miss about error 1205 is that MySQL only rolls back the current statement, not the entire transaction (unless innodb_rollback_on_timeout = ON, which is OFF by default). This means after catching error 1205, your transaction is still open with previous statements' effects intact. If your code catches the error and retries just the failed statement, it may succeed but with inconsistent state. The safe pattern is: catch 1205 → ROLLBACK the entire transaction → retry from BEGIN.
Adjacent errors: 1213 'Deadlock found when trying to get lock' (true deadlock, not timeout), 1062 'Duplicate entry' (often surfaces after a retry if the first attempt partially succeeded), and 2006 'MySQL server has gone away' (the connection itself timed out while waiting for a lock).
By Bikram Nath · Curator · Updated June 2026
Frequently asked questions
Is error 1205 a deadlock?
No. A deadlock is a circular wait (A waits for B, B waits for A) — MySQL detects and resolves it immediately. Error 1205 is a unidirectional wait timeout: A is waiting for B, but B isn't waiting for A — B is just slow or stuck. Deadlocks return error 1213; lock wait timeouts return 1205.
Should I increase innodb_lock_wait_timeout?
Rarely. Increasing it means your application waits longer for stuck transactions, degrading user experience. The right fix is almost always to optimize the blocking transaction (add indexes, shorten it, or commit sooner). Increase the timeout only as a temporary measure while you fix the root cause.
Why does a SELECT cause lock wait timeout?
Plain SELECT (without FOR UPDATE or LOCK IN SHARE MODE) doesn't acquire locks in InnoDB's default REPEATABLE READ isolation — it uses MVCC snapshots. But SELECT ... FOR UPDATE explicitly acquires an exclusive lock and can trigger 1205 if another transaction holds the lock.