skip to content

A MySQL service logs InnoDB's ERROR 1205 Lock wait timeout exceeded; what state is its transaction left in, and how do you find the blocking session?

level: seniorimportance: should knowfreq 50%

answer

  1. statement, not transaction
  2. earlier locks still held
  3. a startup-only rollback switch
  4. a sys schema view joins waits
  5. an idle blocker shows NULL

basics

~10 s

By default InnoDB rolls back only the timed-out statement, leaving the transaction open with its earlier locks; sys.innodb_lock_waits, built on performance_schema.data_lock_waits, names the blocking session, often an idle one holding an uncommitted transaction.

solid answer

~40 s

`innodb_lock_wait_timeout` (default 50 seconds, global and session, dynamic) bounds how long a statement waits for an InnoDB **row** lock. When it expires InnoDB returns `ERROR 1205` and rolls back **only the current statement**; the transaction stays open and keeps every lock it already had, so the application must either retry the statement or roll back explicitly. Starting the server with `innodb_rollback_on_timeout` (off by default, not dynamic) makes it roll back the whole transaction. To find the blocker, query `sys.innodb_lock_waits` for `waiting_pid`, `blocking_pid` and `blocking_query`, or join `performance_schema.data_lock_waits` to `information_schema.INNODB_TRX`. A `NULL` blocking query usually means an idle session with an open transaction; trace its last statements through `performance_schema.threads` and `events_statements_history`.

code

sql · 12 lines
sql
SELECT waiting_pid, waiting_query, blocking_pid, blocking_query,
       wait_age_secs, locked_table_name, locked_index,
       sql_kill_blocking_connection
FROM sys.innodb_lock_waits;

-- blocking_query is NULL: the blocker is idle inside an open transaction.
-- With blocking_pid = 6 from the row above:
SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;

-- With the THREAD_ID returned, here 28:
SELECT SQL_TEXT FROM performance_schema.events_statements_history
WHERE THREAD_ID = 28 ORDER BY EVENT_ID;

go deeper

for a junior

Recall that 1205 is a lock wait timeout, distinct from the 1213 deadlock error, and that the default wait is 50 seconds.

for a middle

Explain that only the statement is rolled back by default, what innodb_rollback_on_timeout changes, and which variable covers metadata locks instead.

for a senior

Use sys.innodb_lock_waits and the Performance Schema to find an idle blocker, then fix the code path that leaves transactions open.

for a principal

Set per-workload timeout and retry policy, and make idle open transactions a monitored, alertable condition rather than a periodic surprise.

## What the timeout is and is not In MySQL, `innodb_lock_wait_timeout` is the number of seconds an InnoDB transaction waits for a **row lock** before giving up with `ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction`. The default is **50 seconds**; the scope is global and session, and it is dynamic, so one session can shorten its own wait with `SET SESSION innodb_lock_wait_timeout = 5`. Three boundaries matter: - It covers **InnoDB row locks only**. A MySQL table lock does not happen inside InnoDB and is not bounded by it, and metadata-lock waits (DDL, `LOCK TABLES`) fall under a different variable, `lock_wait_timeout`, whose default is 31536000 seconds, one year. - It does **not** resolve deadlocks while `innodb_deadlock_detect` is on (the default): those are detected immediately and reported as error 1213. - A timeout is not a deadlock: the blocker is usually just a transaction that has not finished. ## What state the transaction is left in This is the part that bites in production. 1. By default InnoDB rolls back **only the statement** that waited and timed out. 2. The transaction is **still open**. Its earlier changes are intact and uncommitted. 3. **Locks are not released** by a single-statement rollback, so the transaction still blocks whoever was waiting on it. 4. If the application swallows the error and carries on, a later `COMMIT` commits a partial transaction. The manual's guidance is to retry the statement under the default behaviour, or the whole transaction if the server runs with `innodb_rollback_on_timeout`. That option defaults to `OFF`, is global and is **not dynamic**: it can only be set at startup. Many teams simply roll back and retry the whole unit of work on 1205, which behaves the same under either setting. | Error | Code / SQLSTATE | What InnoDB rolls back | Locks afterwards | |---|---|---|---| | Lock wait timeout | 1205 / `HY000` | the waiting statement (whole transaction if `innodb_rollback_on_timeout`) | earlier locks still held | | Deadlock | 1213 / `40001` | the whole victim transaction | all released | | `NOWAIT` on a locked row | 3572 | the statement fails at once | earlier locks still held | ## Finding the blocker The Performance Schema exposes InnoDB lock state without any setup: there are no instruments to enable for `data_locks` and `data_lock_waits`. - **`sys.innodb_lock_waits`** is the quickest view: one row per waiting/blocking pair with `waiting_pid`, `waiting_query`, `blocking_pid`, `blocking_query`, `wait_age_secs`, `locked_table_name`, `locked_index`, the two lock modes and ready-made `sql_kill_blocking_query` and `sql_kill_blocking_connection` statements. - **`performance_schema.data_lock_waits`** pairs `REQUESTING_ENGINE_TRANSACTION_ID` with `BLOCKING_ENGINE_TRANSACTION_ID`; join it to `information_schema.INNODB_TRX` for `trx_mysql_thread_id`, `trx_started` and `trx_query`, and to `performance_schema.data_locks` for the exact index, `LOCK_MODE` and `LOCK_DATA`. - **`information_schema.INNODB_TRX`** on its own lists every open InnoDB transaction with `trx_state` (`RUNNING` or `LOCK WAIT`), `trx_started`, `trx_wait_started` and `trx_rows_locked`, which is how you spot a transaction that has been open for an hour. - **A `NULL` blocking query** means the blocking session is idle: it ran something, did not commit, and is now sitting there holding locks. Take its `blocking_pid`, look up its `THREAD_ID` in `performance_schema.threads`, then read `performance_schema.events_statements_current` and `events_statements_history` for its last statements. The idle blocker is the classic root cause: a code path that opens a transaction, takes locks, then waits on a remote call or forgets to commit. Killing it with the generated `KILL` statement unblocks everyone but rolls its work back, so fix the code path as well. ## Reducing waits - **Shorter transactions.** Do as little as possible between the first change and the commit; never hold row locks across network calls. - **Indexes on locking predicates.** An `UPDATE` or `FOR UPDATE` without a usable index locks every row it scans. - **Fail fast where waiting is pointless.** `SELECT ... FOR UPDATE NOWAIT` fails immediately with error 3572 if a row is locked; `SKIP LOCKED` leaves locked rows out, which suits queue-like tables but gives an inconsistent view for general work. Both apply only to row locks and are unsafe for statement-based replication. - **Tune the timeout per workload.** A short session timeout for interactive requests, a longer one for batch jobs, rather than a single global compromise. Why lock waits happen at all, and how timeouts compare with deadlock detection in principle, belongs with the engine-neutral material on deadlocks; this is MySQL's own error behaviour and diagnostics.

  • Why can retrying only the timed-out statement still leave other sessions blocked?
    A single-statement rollback does not release the locks the transaction took earlier, so while it retries or the application decides what to do, everything waiting on those locks keeps waiting. If the retry also times out, the stall compounds. Rolling back the whole transaction releases all its locks at once.
  • When would you use SKIP LOCKED instead of waiting for a lock?
    For queue-like tables where several workers each claim a pending row: `SELECT ... FOR UPDATE SKIP LOCKED` returns only rows no one else holds, so workers never queue behind each other. The result is deliberately inconsistent, so it is wrong for general transactional reads, and like `NOWAIT` it applies only to row locks and is unsafe for statement-based replication.

  • Error 1205 rolls back the whole transaction and releases its locks.
  • innodb_lock_wait_timeout also bounds waits for metadata locks behind an ALTER TABLE.
  • innodb_rollback_on_timeout can be switched on at runtime with SET GLOBAL.
  • A NULL blocking_query means nothing is really blocking the waiter.
  • Raising innodb_lock_wait_timeout is the fix for frequent lock waits.