Error 1205: Lock wait timeout exceeded; try restarting transaction
1205
HY000
ER_LOCK_WAIT_TIMEOUT
Lock wait timeout exceeded; try restarting transaction
Possible Causes and Solutions
The statement waited for a lock that another transaction holds, and gave up when the time limit passed. There are two kinds of locks, with their own time limits:
Row locks. InnoDB waits innodb_lock_wait_timeout seconds, 50 by default, for a row that another transaction has locked with an
UPDATE,DELETE,INSERTor a locking read.Metadata locks. A statement such as ALTER TABLE waits lock_wait_timeout seconds, one day by default, while any open transaction has used the table. See Metadata Locking.
The same error comes right away from a locking statement with NOWAIT, or after n seconds with WAIT n, when the lock isn't free.
By default, only the statement that timed out is rolled back. The transaction stays open, with its earlier changes, so you have to commit it or roll it back. With innodb_rollback_on_timeout set, InnoDB rolls back the whole transaction instead.
To solve the error:
Find the transaction that holds the lock. SHOW ENGINE INNODB STATUS lists the open transactions, and the Information Schema INNODB_TRX table shows how long each has been running. Often it is a session that started a transaction and never committed it, for example a client with autocommit turned off.
Commit or roll back that transaction, or end the session with KILL.
Keep transactions short, and don't wait for user input while a transaction is open.
Add indexes, so that statements lock fewer rows.
Retry the statement or the transaction in the application.
Increase the time limit only when long waits are expected, for example for a batch job.
To skip rows that other transactions have locked, instead of waiting for them, use SKIP LOCKED.
A deadlock, where transactions wait for each other, gives error 1213 right away, unless innodb_deadlock_detect is OFF.
This page is licensed: CC BY-SA / Gnu FDL
Last updated
Was this helpful?

