A statement waited for a lock held by another transaction and gave up (error 1205)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionShort answer
Error 1205 means a statement waited longer than innodb_lock_wait_timeout (50 seconds by default) for a row lock that another transaction still holds. Raising the timeout hides the problem. Find the blocking session in sys.innodb_lock_waits, then fix why it holds locks so long: an open idle transaction, too much work per transaction, or a query that scans the whole table.
Fix it fast
1Find out which cause you have
Run this while the problem is happening, from a second session. It lists every waiting statement with the session that blocks it. Read blocking_pid first: blocking_query is empty when the blocker is idle, and an idle blocker with a large age is the typical culprit.
SELECT waiting_pid, wait_age_secs, waiting_query, blocking_pid, blocking_trx_started, blocking_trx_age, blocking_query, sql_kill_blocking_connection FROM sys.innodb_lock_waits ORDER BY wait_age_secs DESC;2Match what you see, then jump to its fix
- 1
blocking_query is NULL, blocking_trx_age is minutes or hours, and the blocker's row in SHOW PROCESSLIST shows Sleep.A session opened a transaction and went idle without committingFix - 2
Blockers are active rather than idle, trx_rows_modified is large, and the errors come in bursts when batch jobs or imports run.Transactions do too much work, or are not retried, between the first lock and the commitFix - 3
Unrelated rows or inserts wait on a statement that touches only a few rows, and EXPLAIN for the blocker shows type ALL or a huge rows estimate.A query without a usable index locks far more rows than it changesFix - 4
The statement that fails is an ALTER, DROP, TRUNCATE or LOCK TABLES, or ordinary queries pile up behind one; sys.innodb_lock_waits is empty.A schema change or LOCK TABLES is waiting on a metadata lockFix
- 1
What does the error look like?
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionSQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transactionOperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')
Cause 1 of 4
A session opened a transaction and went idle without committing
1Check
The manual states that blocking_query is NULL when the session that issued the blocking query has become idle. Look at that session's transaction and its latest statements. Typical sources are a script or a GUI client that started a transaction by hand, or application code that holds a transaction open across a slow call.
SELECT trx_id, trx_mysql_thread_id, trx_started, trx_state,
trx_rows_locked, trx_rows_modified
FROM information_schema.innodb_trx
ORDER BY trx_started;
SELECT t.PROCESSLIST_ID, s.SQL_TEXT
FROM performance_schema.threads t
JOIN performance_schema.events_statements_history s USING (THREAD_ID)
WHERE t.PROCESSLIST_ID = 123 -- the blocking_pid
ORDER BY s.EVENT_ID;2Fix
End the blocking transaction. Ask its owner to COMMIT or ROLLBACK if you can; otherwise kill the connection, which rolls its transaction back and discards its uncommitted changes. Then find why it was left open and close it in code, for example in a finally block.
-- the blocker's own session
COMMIT; -- or ROLLBACK;
-- from another session, using the id from blocking_pid
KILL 123;3Verify
The waiting statements finish and sys.innodb_lock_waits returns no rows.
SELECT COUNT(*) FROM sys.innodb_lock_waits;Confirmed by dev.mysql.com
Cause 2 of 4
Transactions do too much work, or are not retried, between the first lock and the commit
1Check
A row stays locked until the transaction ends, so the time a lock is held equals the time from the first change to COMMIT. The manual's advice is to keep that window short and to split work into separate transactions. Also note what the timeout does: by default only the waiting statement is rolled back, and the transaction stays open with its earlier locks.
SELECT trx_mysql_thread_id, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_secs,
trx_rows_locked, trx_rows_modified, trx_query
FROM information_schema.innodb_trx
ORDER BY age_secs DESC;
SELECT @@GLOBAL.innodb_lock_wait_timeout, @@GLOBAL.innodb_rollback_on_timeout;2Fix
Split big jobs into small batches that each commit, do slow work (network calls, file writes) outside the transaction, and have the application catch error 1205 and retry the statement, or the whole transaction when innodb_rollback_on_timeout is on or when you cannot be sure which. Raising innodb_lock_wait_timeout only moves the failure later; use it as a stopgap, never as the fix.
-- one batch per transaction
START TRANSACTION;
UPDATE orders SET archived = 1
WHERE archived = 0 AND created_at < '2026-01-01'
ORDER BY id LIMIT 1000;
COMMIT;
-- stopgap for one session only (seconds)
SET SESSION innodb_lock_wait_timeout = 120;3Verify
The longest open transaction stays short while the job runs.
SELECT MAX(TIMESTAMPDIFF(SECOND, trx_started, NOW())) AS longest_secs FROM information_schema.innodb_trx;Confirmed by dev.mysql.com
Cause 3 of 4
A query without a usable index locks far more rows than it changes
1Check
The manual says UPDATE, DELETE and locking reads set locks on every index record they scan, whether or not the WHERE clause excludes the row, and that with no suitable index every row of the table is locked. Explain the blocking statement (a SELECT with the same WHERE gives the same plan).
EXPLAIN SELECT id FROM orders WHERE status = 'pending';
SHOW INDEX FROM orders;2Fix
Add an index that matches the WHERE clause so the statement scans only the rows it needs. Build it with the online DDL options the manual describes for your table, and test the plan again.
ALTER TABLE orders ADD INDEX idx_status (status), ALGORITHM=INPLACE, LOCK=NONE;3Verify
EXPLAIN now shows the new index in key and a small rows estimate.
EXPLAIN SELECT id FROM orders WHERE status = 'pending';Confirmed by dev.mysql.com
Cause 4 of 4
A schema change or LOCK TABLES is waiting on a metadata lock
1Check
A transaction keeps a metadata lock on every table it used until it ends, and DDL on that table waits for it. These waits are governed by lock_wait_timeout, not innodb_lock_wait_timeout, and also end in error 1205 on expiry. The metadata_locks table (instrument enabled by default) shows holders and pending requests.
SELECT ml.OBJECT_SCHEMA, ml.OBJECT_NAME, ml.LOCK_TYPE, ml.LOCK_STATUS, t.PROCESSLIST_ID
FROM performance_schema.metadata_locks ml
JOIN performance_schema.threads t ON t.THREAD_ID = ml.OWNER_THREAD_ID
WHERE ml.OBJECT_TYPE = 'TABLE' AND ml.OBJECT_NAME = 'orders';
SELECT @@lock_wait_timeout;2Fix
Commit or end the transaction that holds the GRANTED lock, then run the DDL. For future changes, give the DDL session a short lock_wait_timeout so it fails fast instead of queueing every other query behind it, and run it when no long transactions are open.
SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders ADD COLUMN note VARCHAR(200) NULL;3Verify
The ALTER finishes, or fails within five seconds without stalling other queries.
SELECT LOCK_STATUS, COUNT(*) FROM performance_schema.metadata_locks WHERE OBJECT_NAME = 'orders' GROUP BY LOCK_STATUS;Confirmed by dev.mysql.com
How do I stop it coming back?
- Keep transactions short: no network calls, user waits or sleeps between the first write and COMMIT.
- Catch error 1205 and 1213 in the application and retry; both are expected on busy servers.
- Index every column that appears in the WHERE clause of an UPDATE or DELETE.
- Give migration sessions a small lock_wait_timeout and run them away from long reports and batch jobs.
Sources
- MySQL 8.4: Using InnoDB transaction and locking information
- MySQL 8.4: sys.innodb_lock_waits view
- MySQL 8.4: InnoDB startup options (innodb_lock_wait_timeout)
- MySQL 8.4: InnoDB error handling
- MySQL 8.4: Locks set by different SQL statements
- MySQL 8.4: Metadata locking
- MySQL 8.4: metadata_locks table
- MySQL 8.4: Server system variables (lock_wait_timeout)
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
The connection to MySQL dropped while a query was running (error 2013)
ERROR 2006 (HY000): MySQL server has gone away
Error 2013 means the client sent a query or was waiting for the answer and the connection ended.
4 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix