For the complete documentation index, see llms.txt. This page is also available as Markdown.

Error 1205: Lock wait timeout exceeded; try restarting transaction

Error Code
SQLSTATE
Error
Description

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:

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

spinner

Last updated

Was this helpful?