Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

MySQL: Lock wait timeout exceeded, and how to find the blocker

The waiter is the symptom. Find the blocker, shorten the transaction that holds locks, and stop raising the timeout as a fix.

~5 min readSQL
#MySQL#InnoDB#Locking#Performance
Two database row blocks with one held by an amber lock plate beside a waiting loop and countdown dial

Introduction & Quick Summary

Symptom. A write fails after a pause of roughly fifty seconds, and the application retries into the same wall:

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

The statement that timed out is usually an ordinary UPDATE ... WHERE id = ? or an INSERT. Nothing in it is slow. Other sessions keep working, and a retry sometimes succeeds, which makes it look like a transient network problem.

Cause. Another transaction holds an exclusive lock on a row (or a range of rows) the statement needs. InnoDB waits innodb_lock_wait_timeout seconds, 50 by default, then aborts the waiting statement with 1205. Unlike a deadlock (1213), InnoDB does not detect a cycle here; it simply runs out of patience. The blocker is frequently an idle session that opened a transaction and never committed.

Fix. Find the blocker before changing any timeout:

SELECT wait_age_secs, locked_table, waiting_pid, waiting_query, blocking_pid, blocking_query FROM sys.innodb_lock_waits;
-- MySQL 8.0+, without the sys schema SELECT w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx, w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx, b.trx_mysql_thread_id AS blocking_thread, b.trx_started AS blocking_since, r.trx_query AS waiting_query FROM performance_schema.data_lock_waits w JOIN information_schema.innodb_trx b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID JOIN information_schema.innodb_trx r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID;

A blocking_query of NULL with a live blocking_thread is the classic idle-in-transaction case: the session holds locks and runs nothing. KILL that connection after checking what it is, then fix the code path that left it open.

Core Mechanics, Signatures & Reproducible Setup

InnoDB locks rows for the length of a transaction, not the length of a statement. A transaction that takes a lock and then waits on an HTTP call, a queue, or a user's input keeps every lock it took. The timeout is a property of the waiting session, so raising it globally extends the wait for everyone without removing the conflict.

Reproduce the whole thing with two sessions and a one-row table:

CREATE TABLE accounts (id INT PRIMARY KEY, balance INT NOT NULL); INSERT INTO accounts VALUES (1, 100), (2, 100); -- session A BEGIN; UPDATE accounts SET balance = balance - 10 WHERE id = 1; -- leave it open -- session B SET SESSION innodb_lock_wait_timeout = 3; BEGIN; UPDATE accounts SET balance = balance + 10 WHERE id = 1; -- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- from a third session SHOW PROCESSLIST; SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx;

trx_state of RUNNING with an empty trx_query is the idle blocker. LOCK WAIT is the victim. That pair is the entire diagnosis, and both come from one view.

The settings that shape the behaviour:

SELECT @@innodb_lock_wait_timeout, @@transaction_isolation, @@innodb_rollback_on_timeout;
sql
-- per session, for a known batch job; not a global change SET SESSION innodb_lock_wait_timeout = 300;

Key Comparison Table

ErrorMeaningBlocker still activeCorrect response
1205a statement waited too longyesfind and release the blocker
1213deadlock detected, one transaction rolled backresolved by InnoDBreorder access, retry
1205 repeated on the same rowa long transaction holds ityesshorten the transaction
1205 only on INSERTgap or insert-intention lock conflictpossiblycheck indexes and isolation level
1205 only under loadlock queue, not one blockermanybatch the writes, reduce hold time
Lock wait timeout in a migrationDDL waiting on metadata locksyesrun DDL in a low-traffic window
CauseSignalFix
idle transactionblocking_query NULL, Command: Sleepcommit promptly, add timeouts
bulk updatelong UPDATE over many rowsbatch by primary key
missing index on the WHERElock on all scanned rowsadd the index
foreign key checkshared lock on the parent rowsame access order, or drop the FK
SELECT ... FOR UPDATE held across callsopen transaction during I/Omove I/O outside the transaction
gap locks in REPEATABLE READconflicts on non-existent rowsREAD COMMITTED, or a covering index

Practical Edge Cases, Error Fixes & Deep Dive Code Examples

1. A missing index turns one row into thousands

An UPDATE without a usable index scans the table and locks every row it examines, so unrelated statements block behind it. This is the single most common cause of "it worked until the table grew":

EXPLAIN UPDATE orders SET status = 'archived' WHERE customer_email = 'a@example.com'; -- type: ALL, rows: 2400000, key: NULL
CREATE INDEX idx_orders_customer_email ON orders (customer_email);

Confirm the lock footprint with performance_schema.data_locks for the transaction's thread before and after adding the index.

2. Batch jobs that hold locks for minutes

A single DELETE over a million rows is one transaction with a million row locks. Batch it by primary key so each transaction is short and the lock queue drains between batches:

DELETE FROM events WHERE created_at < '2025-01-01' LIMIT 5000; -- repeat until 0 rows affected, sleeping briefly between batches
bash
# from the shell, so each statement is its own transaction while true; do mysql -e "DELETE FROM events WHERE created_at < '2025-01-01' LIMIT 5000" app || break mysql -e "SELECT ROW_COUNT()" app sleep 0.2 done

The gentler variant for huge tables is to partition by date and drop partitions, which takes metadata locks only.

3. Idle-in-transaction from connection pools

An application that opens a transaction, calls a slow service, and only then commits keeps locks for the duration of that call. Fix the ordering, and add a server-side guard for the cases you cannot fix today:

sql
SET GLOBAL innodb_lock_wait_timeout = 50; -- keep the default SET GLOBAL wait_timeout = 300; -- idle connection SET GLOBAL interactive_timeout = 300; -- MySQL 8.0.24+: kill idle transactions automatically SET GLOBAL innodb_rollback_on_timeout = OFF;
sql
-- MySQL 8.0: start a transaction with a bounded lifetime SET SESSION idle_transaction_timeout = 30; -- MariaDB equivalent of the same idea

MySQL itself has no idle-transaction timer in 8.4; MariaDB does. On MySQL, the pool is the place to enforce it: many pools can reset a connection returned to the pool and roll back an open transaction, so configure that rather than hoping.

4. Foreign keys and shared locks

InnoDB takes a shared lock on the referenced parent row when a child row is inserted or updated. Two transactions touching parent and child in opposite orders wait on each other's shared locks, and the timeout arrives without any obvious long statement:

-- both transactions must take the parent lock first UPDATE orders SET total = ? WHERE id = ?; INSERT INTO order_items (order_id, sku) VALUES (?, ?);

Consistent access order removes most of this class of conflict, and it is the same discipline that prevents deadlocks.

5. Reading locks on MySQL 5.7 and MariaDB

performance_schema.data_lock_waits replaced the old information_schema.INNODB_LOCK_WAITS in MySQL 8.0. On 5.7 and MariaDB the older views are what exists, and a query copied from an 8.0 answer fails with an unknown table:

SELECT r.trx_mysql_thread_id AS waiting_pid, r.trx_query AS waiting_query, b.trx_mysql_thread_id AS blocking_pid, b.trx_started AS blocker_started FROM information_schema.INNODB_LOCK_WAITS w JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id;

SHOW ENGINE INNODB STATUS still works everywhere and prints the latest deadlock, which is the fallback when neither view is available.

6. Retries that make it worse

Retrying immediately re-enters the queue and adds load to a database that is already waiting. Retry with jittered backoff, a small attempt budget, and only for 1205 and 1213:

python
for attempt in range(4): try: with conn.begin(): conn.execute(update_stmt) break except OperationalError as e: if e.args[0] not in (1205, 1213) or attempt == 3: raise time.sleep((2 ** attempt) * 0.05 * (1 + random.random()))

A retry that succeeds changes nothing about the blocker, so pair it with an alert on repeated 1205 for the same table.

Common Compilation/Runtime Mistakes & Version Gotchas

  • Raising innodb_lock_wait_timeout globally as the fix. It hides the conflict and lengthens every wait.
  • Ignoring blocking_pid. The waiter is the symptom; the blocker is the cause.
  • Assuming 1213 and 1205 are the same. Deadlocks are rolled back by InnoDB, timeouts are not.
  • Running DDL during traffic. Metadata locks block even reads.
  • Leaving transactions open across HTTP calls. The pool returns a connection that still holds locks.
  • Using information_schema.INNODB_LOCK_WAITS on MySQL 8.0. The view is gone.
  • Retrying in a tight loop. It amplifies contention on the same rows.
  • Batching by an unindexed column. The batch is as slow as the original statement.

Inspect the statements and locking clauses with the SQL formatter and draft the schema change with the SQL schema builder before adding an index to a hot table.

Reference: innodb_lock_wait_timeout, InnoDB locking, and the sys.innodb_lock_waits view.

Official references

primary documentation

The SQL documentation that defines the behaviour this guide explains.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.