ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

What it means

Another session holds a lock your statement needs, and your statement waited the maximum time for it and then stopped. There are two kinds of wait, with the same error:

• Row locks (InnoDB): an UPDATE, DELETE or locking read waits for a row another transaction has changed or locked. The limit is innodb_lock_wait_timeout, 50 seconds by default on both MySQL and MariaDB.
• Metadata locks: an ALTER TABLE, DROP or other DDL waits until no open transaction is using the table. The limit is lock_wait_timeout: a year (31536000 s) on MySQL 8.4, a day (86400 s) on MariaDB 11.4.

Unlike a deadlock, nothing is wrong with the order of locks: someone is holding one for a long time. And unlike a deadlock, only the statement that timed out is undone. Earlier statements in your transaction still stand, uncommitted, and your transaction is still open (unless the server runs with innodb_rollback_on_timeout, off by default). Roll back or retry deliberately.

Common causes

1. A transaction left open: an app that ran START TRANSACTION (or turned off autocommit) and never committed, a crashed worker whose connection lingers, or someone’s SQL client with an uncommitted change.
2. A long write (a big UPDATE or DELETE, a batch job) holding row locks for minutes.
3. A migration waiting for a metadata lock behind a long-running query or an open transaction that read the table. While it waits, new queries on that table queue behind it too.
4. UPDATE/DELETE without a usable index, which locks many more rows than it changes.

How to fix it

Find the blocking session

While the wait is happening, the sys schema (MySQL, and MariaDB too) shows who waits for whom:

SELECT waiting_pid, waiting_query, blocking_pid, blocking_query, wait_age,
       sql_kill_blocking_connection
FROM sys.innodb_lock_waits\G

                 waiting_pid: 28526
               waiting_query: UPDATE accounts SET balance = balance + 1 WHERE id = 1
                blocking_pid: 28524
              blocking_query: SELECT SLEEP(7)
                    wait_age: 00:00:02
sql_kill_blocking_connection: KILL 28524

blocking_query is what the blocker is running now, often nothing or something unrelated: the lock was taken by an earlier statement in its transaction. For metadata locks on MySQL, use sys.schema_table_lock_waits (filter out rows where waiting_pid = blocking_pid). On either server, SHOW PROCESSLIST shows the waiters with the state Waiting for table metadata lock; the blocker is usually the oldest session with an open transaction:

SELECT trx_mysql_thread_id, trx_started, trx_state, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started;

End it

Ask whoever owns it to commit or roll back. If it’s abandoned, end the session; its transaction rolls back:

KILL 28524;

Then retry your statement or transaction

If your code gets 1205, decide explicitly: retry the statement, or ROLLBACK and retry the whole transaction. Don’t carry on as if it succeeded.

Don’t wait for locks you can skip

For job queues and similar, MySQL 8 and MariaDB can fail fast or skip locked rows:

SELECT * FROM jobs WHERE state = 'ready' ORDER BY id LIMIT 10 FOR UPDATE SKIP LOCKED;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

Give migrations a short timeout

So a migration that can’t get its metadata lock gives up quickly instead of stalling every query on the table behind it:

SET SESSION lock_wait_timeout = 5;
ALTER TABLE accounts ADD COLUMN note varchar(20);

Retry it later if it fails. See adding a column for what the ALTER itself locks.

Raising the timeout

SET SESSION innodb_lock_wait_timeout = 120 helps a batch job that is expected to wait. Raising it for everyone only makes requests hang longer.

Reproduce it

On MySQL 8.4.11, session A ran START TRANSACTION; UPDATE accounts SET balance = 0 WHERE id = 1; and then stayed open (SELECT SLEEP(7)). Session B:

SET SESSION innodb_lock_wait_timeout = 4;
START TRANSACTION;
UPDATE accounts SET balance = balance + 1 WHERE id = 2;
UPDATE accounts SET balance = balance + 1 WHERE id = 1;
SELECT * FROM accounts WHERE id = 2;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
id	balance
2	101.00

The second update timed out after four seconds; the first was still in place in B’s transaction (101.00), which shows only the statement was undone. The sys.innodb_lock_waits output above is from this test.

Metadata lock: with session A in a transaction that had read accounts, session B ran SET SESSION lock_wait_timeout = 4; ALTER TABLE accounts ADD COLUMN note3 varchar(20);, and a third session ran a plain SELECT COUNT(*) FROM accounts. The process list showed both waiting:

id	time	state	info
30215	3	User sleep	SELECT SLEEP(6)
30217	2	Waiting for table metadata lock	ALTER TABLE accounts ADD COLUMN note3 varchar(20)
30219	1	Waiting for table metadata lock	SELECT COUNT(*) FROM accounts

The ALTER failed with 1205 after four seconds, and the SELECT ran as soon as it did.

MariaDB 11.4.13 gave the same results and message. One difference: with the row locked, FOR UPDATE NOWAIT fails with 1205 on MariaDB but with its own error on MySQL:

ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

In Inlet

Inlet’s Activity monitor shows the server’s sessions and which one is waiting on which, so you can see the blocker while it’s happening. If you use manual-commit transactions in Inlet’s query editor, commit or roll back when you’re done: an open transaction keeps its locks.