About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.
The full message is ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction. In MySQL it means a statement asked InnoDB for a row lock that another transaction held, and waited innodb_lock_wait_timeout seconds (default 50) without getting it. The statement that timed out is not the problem; the transaction holding the lock is. Find it, decide whether it is working, idle or stuck, and fix that. Raising the timeout only makes the queue longer.
What the error means
The MySQL 8.4 Error Reference lists error 1205, symbol ER_LOCK_WAIT_TIMEOUT, SQLSTATE HY000, with the message Lock wait timeout exceeded; try restarting transaction.
The manual describes innodb_lock_wait_timeout as the number of seconds an InnoDB transaction waits for a row lock before giving up. The default is 50 seconds, the minimum is 1, and the variable can be set globally or per session at runtime. Three details matter in practice:
- When the timeout fires, the current statement is rolled back, not the entire transaction. To roll back the whole transaction, the server has to be started with
--innodb-rollback-on-timeout; that variable is not dynamic. So an application that only logs the error and carries on may be running the rest of a half-finished transaction. - The timeout applies to InnoDB row locks. The manual says a MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks.
- A deadlock is a different event. With
innodb_deadlock_detectenabled (the default) InnoDB detects deadlocks immediately and rolls back one of the transactions, so a deadlock does not wait for this timeout.
Likely causes
| Cause | What you see | How to confirm |
|---|---|---|
| A long transaction that is still running | A blocking session with a running statement and a large number of locked rows | sys.innodb_lock_waits shows a blocking_query and a high blocking_trx_rows_locked |
| An idle session holding an open transaction | blocking_query is NULL because the session is sleeping; locks stay until commit or rollback |
The performance_schema.threads and events_statements_current steps below |
| A batch job updating many rows in one transaction | Many short requests time out together while the job runs | The blocking transaction started long ago (blocking_trx_age) |
| Two workloads updating the same rows at the same time | Intermittent timeouts under load | Repeated pairs of waiting and blocking transactions in the same table and index |
| Timeout set too low for the workload | Errors appear after someone lowered the value | SHOW VARIABLES shows a value well under 50 for the session |
| The wait is not an InnoDB row lock | No rows in the lock-wait view while queries still wait | The manual says the timeout does not cover table locks; check the process list for other waits |
Prove the cause before changing anything
All of these are read-only, and they must be run while the wait is still happening. Once the blocked statement has timed out, the wait rows disappear.
1. Read the timeout in force.
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
SHOW SESSION VARIABLES LIKE 'innodb_lock_wait_timeout';
SHOW VARIABLES needs no privilege beyond the ability to connect. The two values can differ because a client can change its own session value.
2. List who waits for whom. The manual's own query uses the sys schema view:
SELECT
waiting_trx_id,
waiting_pid,
waiting_query,
blocking_trx_id,
blocking_pid,
blocking_query
FROM sys.innodb_lock_waits;
The manual describes the view as summarising the InnoDB locks that transactions are waiting for, sorted by descending lock age by default. waiting_pid and blocking_pid are processlist IDs. The same view also has wait_age_secs, locked_table_schema, locked_table_name, blocking_trx_started, blocking_trx_age, blocking_trx_rows_locked and blocking_trx_rows_modified, and ready-made sql_kill_blocking_query and sql_kill_blocking_connection columns.
A good result is one or a few rows naming the same blocking_pid. A bad result is a chain where one blocker is itself waiting, which means you need to find the head of the chain, the session that is not waiting for anyone.
3. If blocking_query is NULL. The manual explains that the value is NULL when the session that issued the query has become idle. In that case:
SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;
Use your own blocking_pid in place of 6. Then, with the THREAD_ID returned (the manual uses 28 as its example):
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current
WHERE THREAD_ID = 28\G
For more history, the manual points to performance_schema.events_statements_history, which holds the last 10 statements executed by the thread.
4. Cross-check the transaction. INFORMATION_SCHEMA.INNODB_TRX has TRX_STATE (a waiting transaction shows LOCK WAIT), TRX_STARTED, TRX_WAIT_STARTED, TRX_MYSQL_THREAD_ID and TRX_QUERY. It confirms which session owns the transaction and how long it has been open.
5. See what the blocking session is. SHOW FULL PROCESSLIST lists the threads. Seeing the blocking session's Command as Sleep while it holds locks is a sign of an application that started a transaction and never committed. You need the PROCESS privilege to see other users' threads.
Fix
Work from least to most disruptive.
1. Let the owner finish or end the transaction cleanly. If the blocker is a legitimate job, wait for it, or ask the application owner to commit or roll back. As general practice (the MySQL manual does not prescribe it), keep application transactions short: acquire locks late, commit early, and never hold a transaction open while waiting on a user, a queue or another system.
2. Handle the error in the application. The message tells the client to restart the transaction. Because only the statement was rolled back, a sensible application design (this is advice, not a MySQL requirement) is to roll back the whole transaction, then retry it a limited number of times with a short pause.
3. Give a one-off batch job a longer wait for itself only. This changes the session value and no other connection. Save the current value first:
SHOW SESSION VARIABLES LIKE 'innodb_lock_wait_timeout';
Risk: a longer wait ties up a connection and application thread for longer, and hides a blocker instead of removing it. Back-out: SET SESSION innodb_lock_wait_timeout = 50; using the value you noted, or simply close the session.
Note the value printed. Then:
SET SESSION innodb_lock_wait_timeout = 120;
A global change affects clients that connect afterwards, so avoid it as a first response.
4. End the blocking session (last resort). The KILL page says KILL CONNECTION terminates the connection after terminating any statement it is executing, and KILL QUERY stops only the statement and leaves the connection intact. The KILL page does not say what happens to the work in an open transaction, so agree with the application owner that the changes may be lost before you do this. You also need the CONNECTION_ADMIN privilege, or the deprecated SUPER, to kill another user's thread.
Select the exact object first. Print the row so you can see the blocker's user, age and rows locked:
SELECT blocking_pid, blocking_trx_started, blocking_trx_age, blocking_trx_rows_locked, sql_kill_blocking_connection
FROM sys.innodb_lock_waits;
Risk: killing a connection ends a session that may be mid-transaction, and the application may fail or retry unexpectedly. This cannot be undone. Back-out: none. Because the KILL page does not say what happens to the open transaction, treat its uncommitted work as lost and ask its owner to check and redo it. Run the exact KILL statement printed in the sql_kill_blocking_connection column for the one blocking_pid you identified, not a guess.
Example (illustrative): on db01 (192.0.2.20) the order system on web01 starts failing with ERROR 1205 about once an hour. During a failure, sys.innodb_lock_waits shows waiting_pid 311, blocking_pid 214 and blocking_query as NULL, with blocking_trx_age of 00:47:10. SHOW FULL PROCESSLIST shows pid 214 in Sleep. The thread lookup returns a last statement of UPDATE orders SET status = 'hold' WHERE id = 5521. A nightly script on web01 started a transaction, ran that update, and then waited for a slow HTTP call before committing. The fix is in the script: commit before the HTTP call. Until it is deployed, the team kills connection 214 once, after the script owner agrees.
Confirm the fix
Repeat the sys.innodb_lock_waits query during peak activity: it should return no rows for more than a few seconds at a time. Watch the application log for ERROR 1205; the count should fall to zero or to a rare, retried event. If you changed a session timeout, confirm it reverted with SHOW SESSION VARIABLES LIKE 'innodb_lock_wait_timeout';.
What to watch afterwards
- The age of the oldest open transaction in
INFORMATION_SCHEMA.INNODB_TRX. - Sessions in
Sleepthat hold locks. - The count of
ERROR 1205in application logs after each release. - Batch jobs that update large row ranges in one transaction: split them into smaller commits.
Read next
Related articles you may want next.
- PerformanceMySQL Replica Lag: Separate Receiver Delay from Apply DelayLocate the receiver or applier bottleneck and state what replica freshness is actually evidenced.Read article
- TroubleshootingMySQL ERROR 1040 "Too many connections": find who holds them before raising max_connectionsMySQL ERROR 1040 Too many connections? Use the reserved admin connection to see who holds them, fix the cause, then raise max_connections safely.Read article
- Backup & RecoveryMySQL Point-in-Time Recovery: Match the Backup to Binary Log CoordinatesPlan a consistent MySQL recovery set using recorded coordinates or GTID context and a deliberate transaction stop point.Read article
Risks and limits
The behaviour described is from the MySQL 8.4 manual. Raising innodb_lock_wait_timeout treats the symptom. Killing a session may lose its uncommitted work; the KILL page does not say what happens to an open transaction. The lock-wait views show waits at the moment you query them, so a timeout that has already happened leaves no row. These commands have not been run or lab-tested.
Sources
- Server Error Reference — Oracle, MySQL 8.4 Error Reference, no date shown, accessed 20 November 2026
- 17.14 InnoDB Startup Options and System Variables (innodb_lock_wait_timeout, innodb_rollback_on_timeout) — Oracle, MySQL 8.4 Reference Manual, no date shown, accessed 20 November 2026
- 17.15.2.1 Using InnoDB Transaction and Locking Information — Oracle, MySQL 8.4 Reference Manual, no date shown, accessed 20 November 2026
- 30.4.3.9 The innodb_lock_waits and x$innodb_lock_waits Views — Oracle, MySQL 8.4 Reference Manual, no date shown, accessed 20 November 2026
- 29.12.13.2 The data_lock_waits Table — Oracle, MySQL 8.4 Reference Manual, no date shown, accessed 20 November 2026
- 28.4.28 The INFORMATION_SCHEMA INNODB_TRX Table — Oracle, MySQL 8.4 Reference Manual, no date shown, accessed 20 November 2026
- 15.7.8.4 KILL Statement — Oracle, MySQL 8.4 Reference Manual, no date shown, accessed 20 November 2026
- 15.7.7.31 SHOW PROCESSLIST Statement — Oracle, MySQL 8.4 Reference Manual, no date shown, accessed 20 November 2026
- 15.7.7.41 SHOW VARIABLES Statement — Oracle, MySQL 8.4 Reference Manual, no date shown, accessed 20 November 2026
This article was originally published on .