MySQL Error 1205: Lock Wait Timeout Exceeded

MySQL Error 1205 (HY000, ER_LOCK_WAIT_TIMEOUT) means an InnoDB statement waited too long for a row lock. The message is Lock wait timeout exceeded; try restarting transaction. By default, InnoDB rolls back the statement that timed out, not the whole transaction. See the MySQL 8.4 error reference and InnoDB error handling.

Find the session holding the lock

Another transaction may have changed a row and left its transaction open. Check which lock request is waiting and which transaction is blocking it:

SELECT blocking_thread.PROCESSLIST_ID AS blocking_connection_id,
       blocking_thread.PROCESSLIST_INFO AS blocking_sql,
       waiting_thread.PROCESSLIST_ID AS waiting_connection_id,
       waiting_thread.PROCESSLIST_INFO AS waiting_sql
FROM performance_schema.data_lock_waits AS waits
JOIN performance_schema.threads AS waiting_thread
  ON waiting_thread.THREAD_ID = waits.REQUESTING_THREAD_ID
JOIN performance_schema.threads AS blocking_thread
  ON blocking_thread.THREAD_ID = waits.BLOCKING_THREAD_ID;

This query reports active InnoDB lock waits. SHOW ENGINE INNODB STATUS\G can also show transaction and lock information. You may need privileges to read Performance Schema or InnoDB status; ask a database administrator if these queries are denied. See InnoDB lock-wait information.

Look for an application session that started a transaction but did not commit or roll back, a long-running update, or two transactions that lock rows in conflicting orders. Do not terminate a blocking session until you know which work it is doing and what a rollback would discard.

Reduce the lock wait

Resolve the blocking transaction according to the application’s data rules. Then reduce the time rows remain locked:

• Commit or roll back each transaction promptly; avoid user interaction or slow external work while a transaction is open.

• Keep the set of changed rows small. Check the WHERE and JOIN columns used by the statement and add an index when it helps MySQL locate rows without scanning a large range.

• Make transactions that update multiple tables or rows acquire them in a consistent order.

• Check the session’s current timeout before changing it:

  SELECT @@SESSION.innodb_lock_wait_timeout;
  

innodb_lock_wait_timeout applies to InnoDB row-lock waits, not waits for MySQL table locks. Increasing it may be appropriate for a known long-running operation, but it can also leave requests waiting longer while a blocking transaction remains open. See the innodb_lock_wait_timeout variable.

Retry with the correct transaction boundary

With the default innodb_rollback_on_timeout setting, Error 1205 rolls back only the failed statement. Earlier statements in the same transaction remain pending, and their locks may remain held. If the application needs all changes to succeed or fail together, explicitly roll back the transaction before retrying the complete unit of work. If the server was started with innodb_rollback_on_timeout enabled, InnoDB rolls back the entire transaction instead.

Error 1213 is different: an InnoDB deadlock rolls back the whole transaction, which the application can retry. Do not treat Error 1205 and Error 1213 as identical; see how to fix MySQL Error 1213 and InnoDB deadlock handling. For transaction boundaries, see the MySQL transaction guide, or browse all MySQL error troubleshooting guides.