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.

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;-- per session, for a known batch job; not a global change
SET SESSION innodb_lock_wait_timeout = 300;Key Comparison Table
| Error | Meaning | Blocker still active | Correct response |
|---|---|---|---|
| 1205 | a statement waited too long | yes | find and release the blocker |
| 1213 | deadlock detected, one transaction rolled back | resolved by InnoDB | reorder access, retry |
| 1205 repeated on the same row | a long transaction holds it | yes | shorten the transaction |
1205 only on INSERT | gap or insert-intention lock conflict | possibly | check indexes and isolation level |
| 1205 only under load | lock queue, not one blocker | many | batch the writes, reduce hold time |
Lock wait timeout in a migration | DDL waiting on metadata locks | yes | run DDL in a low-traffic window |
| Cause | Signal | Fix |
|---|---|---|
| idle transaction | blocking_query NULL, Command: Sleep | commit promptly, add timeouts |
| bulk update | long UPDATE over many rows | batch by primary key |
missing index on the WHERE | lock on all scanned rows | add the index |
| foreign key check | shared lock on the parent row | same access order, or drop the FK |
SELECT ... FOR UPDATE held across calls | open transaction during I/O | move I/O outside the transaction |
| gap locks in REPEATABLE READ | conflicts on non-existent rows | READ 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: NULLCREATE 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# 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
doneThe 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:
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;-- MySQL 8.0: start a transaction with a bounded lifetime
SET SESSION idle_transaction_timeout = 30; -- MariaDB equivalent of the same ideaMySQL 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:
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_timeoutglobally 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_WAITSon 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 documentationThe SQL documentation that defines the behaviour this guide explains.
Try matching browser tools
All toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSQLite: database is locked (SQLITE_BUSY) under concurrent writes
SQLite allows one writer. Set a busy timeout, switch to WAL, start write transactions as IMMEDIATE, and reduce to a single writer connection.
MySQL: ERROR 1040 (HY000) Too many connections
The server is out of slots because pools are too large or leaking. Read the counters, size pools against replicas, then raise the limit.
MySQL 8: Public Key Retrieval is not allowed over JDBC
The driver never sent the password. Use verified TLS, or allow key retrieval only where the network is trusted.
Postgres: invalid input syntax for type uuid
An empty filter or a legacy id reaches a uuid parameter. Treat absent as no filter and validate before the query.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.