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
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
UPDATEorDELETEwithout 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
-
Find who is blocking whom (MySQL 8.0 and later).
SELECT * FROM sys.innodb_lock_waits;
-
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;
-
Index the columns used in the
WHEREof updates and deletes so they lock only the rows they change, and keep transactions short. -
If waits are legitimately long, raise the timeout for that session.
SET SESSION innodb_lock_wait_timeout = 120;
Related errors
1213Deadlock found when trying to get lock3572Lock couldn't be acquired with NOWAIT1206Locks exceed the lock table size1222SQL Server: Lock request time out period exceeded55P03PostgreSQL: lock not availableORA-00054Oracle: resource busy and acquire with NOWAIT specifiedSQLITE_BUSYSQLite: database is locked
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.
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.