// MySQL error reference

MySQL error 1205: Lock wait timeout exceeded

A statement waited longer than innodb_lock_wait_timeout for a row lock another transaction holds. Only that statement is rolled back by default.

Error number
1205
Name
ER_LOCK_WAIT_TIMEOUT
SQLSTATE
HY000
Category
Transactions and locking
Error message
Lock wait timeout exceeded; try restarting transaction

What it means

InnoDB makes a statement wait when it needs a row lock that another transaction holds. If the wait exceeds innodb_lock_wait_timeout (50 seconds by default), it gives up with 1205. By default only the waiting statement is rolled back, not the whole transaction (unless innodb_rollback_on_timeout is on), so your transaction is still open and you must decide whether to retry the statement or roll back.

The real issue is whichever transaction held the lock that long.

Common causes

  • A transaction left open: an interactive session or an application that started a transaction and didn't commit.
  • Long-running updates or batch jobs that lock many rows.
  • An UPDATE or DELETE without a usable index, which locks far more rows than it changes.
  • Hot rows (counters, queues) updated by many sessions at once.

How to fix it

  1. Find who is blocking whom (MySQL 8.0 and later).

    SELECT * FROM sys.innodb_lock_waits;
  2. Look for long-open transactions and kill the culprit if appropriate.

    SELECT trx_mysql_thread_id, trx_started, trx_query
    FROM information_schema.innodb_trx ORDER BY trx_started;
    KILL 12345;
  3. Index the columns used in the WHERE of updates and deletes so they lock only the rows they change, and keep transactions short.

  4. If waits are legitimately long, raise the timeout for that session.

    SET SESSION innodb_lock_wait_timeout = 120;

Related errors

More transactions and locking errors

The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.

// fewer errors, faster fixes

Work on MySQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.