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

Hi, it's BlueByte. An UPDATE that normally returns in milliseconds sits there for most of a minute and then fails with ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction. Nothing is broken here — another transaction is holding the row lock your statement needs, and InnoDB waited exactly as long as it was configured to wait before giving up. We'll walk through the message and the shapes it arrives in, why that lock is still held, how to name the blocking session before it slips away, the fix per cause, and what was actually rolled back before you retry.

What 1205 reports, and how drivers reshape it

In the client it is one line:

mysql> UPDATE orders SET status = 'paid' WHERE id = 4711;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The MySQL error reference lists it as error number 1205, symbol ER_LOCK_WAIT_TIMEOUT, SQLSTATE HY000, message Lock wait timeout exceeded; try restarting transaction. Drivers wrap that text rather than replace it, so the same failure reaches you as SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded from PDO, or buried in a connection-pool stack trace from an ORM. Two things survive every wrapper: the number 1205 and the SQLSTATE HY000. A deadlock carries a different pair — 1213 with SQLSTATE 40001 — and the two want different responses, which is where most of the confusion in this area starts.

Why the row is still locked when your statement gives up

InnoDB takes row locks as a transaction touches rows and releases them at COMMIT or ROLLBACK. Your wait expired because some other transaction reached neither:

• A session left a transaction open and idle — an explicit START TRANSACTION, or autocommit switched off by a client or framework, and then a screen, a network hop, or a person who stepped away.
• A long statement holds its locks for its entire run: a multi-million-row UPDATE, a wide SELECT ... FOR UPDATE, an import wrapped in a single transaction.
• An UPDATE ... WHERE or SELECT ... FOR UPDATE without a usable index locks the rows it scanned rather than only the rows it matched, so a small-looking statement pins a large range.
• Two jobs touch the same rows in a different order and one waits behind the other for longer than the timeout allows.

The clock is innodb_lock_wait_timeout, in seconds. The manual's property table gives Default Value 50, Minimum Value 1, Maximum Value 1073741824, Scope Both, Dynamic Yes. Fifty seconds of waiting is the default behaviour, not a hang.

Name the blocking session with sys.innodb_lock_waits

Ask while the wait is still on — the row exists only while someone is waiting:

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

*************************** 1. row ***************************
                 waiting_pid: 812
               waiting_query: UPDATE orders SET status = 'paid' WHERE id = 4711
                    wait_age: 00:00:41
                blocking_pid: 774
              blocking_query: NULL
sql_kill_blocking_connection: KILL 774

A blocking_query of NULL throws people off, and it is documented behaviour: the column returns NULL if the blocking session becomes idle. That is the answer, not a dead end — the blocker is not running a statement, it is sitting on an open transaction. The view also hands you two prepared statements: sql_kill_blocking_query kills the blocking statement, sql_kill_blocking_connection kills the session running it.

Cross-check INNODB_TRX before you kill anything

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

trx_state reads LOCK WAIT for a transaction waiting on a lock and RUNNING for one that is not. Read it together with trx_started: a blocker that began twenty minutes ago with an empty trx_query is the idle-transaction case, while one that started seconds ago with a large trx_rows_locked is the batch case. For lock-by-lock detail, performance_schema.data_locks holds the pending locks queued for a row or table, and performance_schema.data_lock_waits shows which held lock blocks which request.

Fix it by cause, cheapest move first

Idle blocker: have that session COMMIT or ROLLBACK, or run the KILL the view generated. Killing the connection rolls its transaction back, which is harmless on an idle session and expensive on a half-finished batch, because that rollback has to undo everything it did.

KILL 774;

Query OK, 0 rows affected (0.00 sec)

Unindexed predicate: the manual's own advice for lock contention is to create indexes on the columns used in SELECT ... FOR UPDATE and UPDATE ... WHERE statements. Check the plan first, then add the index.

EXPLAIN UPDATE orders SET status = 'paid' WHERE customer_ref = 'C-91823';
ALTER TABLE orders ADD INDEX idx_orders_customer_ref (customer_ref);

Long transaction: keep transactions that insert or update data small enough that they do not stay open for long periods, and commit batches in chunks instead of in one span. If you genuinely need a longer wait for one job, raise the timeout for that session only and leave the server default alone:

SET SESSION innodb_lock_wait_timeout = 120;

Read this before you write the retry loop

The manual is precise about scope: a lock wait timeout causes InnoDB to roll back the current statement, the one that was waiting for the lock and encountered the timeout. To have the entire transaction roll back, the server must be started with --innodb-rollback-on-timeout enabled. Retry the statement under the default behaviour, or the entire transaction when that option is on.

So by default your transaction is still open after 1205, still holding every lock it took earlier. A retry loop that re-runs only the failed statement inside that same transaction keeps those locks and often times out again. Check which behaviour you are on:

SELECT @@innodb_lock_wait_timeout, @@innodb_rollback_on_timeout;

+----------------------------+------------------------------+
| @@innodb_lock_wait_timeout | @@innodb_rollback_on_timeout |
+----------------------------+------------------------------+
|                         50 |                            0 |
+----------------------------+------------------------------+

innodb_rollback_on_timeout is global and not dynamic, so changing it means a restart, not a SET GLOBAL.

A worked example: reconciliation against checkout

A nightly reconciliation job starts at 01:00 and updates orders by customer_ref, a column with no index. Checkout writes start failing at 01:02 with 1205. sys.innodb_lock_waits shows waiting_pid: 812 with wait_age: 00:00:41, blocking_pid: 774, and a NULL blocking_query. information_schema.innodb_trx shows session 774 in RUNNING state, started at 01:00:07, with trx_rows_locked in the hundreds of thousands — the reconciliation scanned the whole table and locked what it scanned. The on-call engineer does not kill it, because the rollback would take as long as the run; they let it finish, checkout recovers at 01:09, and the next morning the fix goes in: an index on customer_ref and commits every 5,000 rows. The following night trx_rows_locked peaks in the hundreds and checkout never notices the job.

Confirm it is gone, and keep it gone

Re-run the statement that failed; it should return immediately. Then check that nobody is queued behind anything:

SELECT COUNT(*) AS waiters FROM sys.innodb_lock_waits;
SELECT trx_mysql_thread_id, trx_state, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s
FROM information_schema.innodb_trx WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;

An empty first result and no rows in the second is what healthy looks like. Keep it that way by alerting on that second query — transactions older than a minute are the raw material of every 1205 — and by taking rows in the same order in every job that touches the same tables.

How this differs from 1213 and from a metadata lock wait

A deadlock is a cycle, not a queue: two transactions each hold what the other needs. InnoDB detects it, picks a victim, and rolls that transaction back whole, reporting 1213 with SQLSTATE 40001. Retry the entire transaction there. One wrinkle connects them: if deadlock detection is turned off with innodb_deadlock_detect, InnoDB falls back to innodb_lock_wait_timeout to roll transactions back when a deadlock occurs, so genuine deadlocks then surface as 1205. A third case looks similar and is not InnoDB row locking at all: DDL waiting behind an open transaction is waiting on a metadata lock, which MySQL exposes through performance_schema.metadata_locks rather than the InnoDB lock tables.

Next time a write dies at 1205, walk these checks back in order: which error number and SQLSTATE you actually got, who sys.innodb_lock_waits names as the blocker, whether that blocker is idle or busy in innodb_trx, and only then decide between waiting, killing, and indexing.