mysql error: Lock wait timeout exceeded - try restarting transaction - how to find the blocker

mysql error: Lock wait timeout exceeded - try restarting transaction - how to find the blocker

TL;DR

Your transaction waited for a row lock longer than innodblockwaittimeout and gave up. Find the blocker with performanceschema.datalocks and datalock_waits, or SHOW ENGINE INNODB STATUS. Kill or fix the blocker, then shorten the transactions that hold locks.

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

Steps

1. While the problem is happening, query the waiters: SELECT * FROM performanceschema.datalock_waits. Expected: You see which transaction is waiting and which lock it wants.

2. Find the blocker: join to data_locks on the blocking engine transaction id, or run SHOW ENGINE INNODB STATUS and read the TRANSACTIONS section. Expected: You have the blocking thread id and its current SQL.

3. Check what the blocker is doing: SELECT * FROM performanceschema.threads WHERE PROCESSLISTID = [blocker id]. Expected: Usually it is an idle-in-transaction session holding locks, or a long-running update.

4. Kill the stuck blocker (KILL [thread id]) or commit it if it is yours. Then retry the waiting transaction. Expected: The waiting transaction proceeds immediately.

5. Fix the root cause: commit faster, lock rows in a consistent order, and keep transactions short. Consider lowering innodblockwait_timeout from 50s so failures surface sooner. Expected: Lock waits stop recurring; new waits fail fast and loudly.

When to use

• the error is ERROR 1205 lock wait timeout
• updates stall for tens of seconds then fail
• you need to name the blocking transaction

When not to use

• the error is 'Deadlock found' (1205's cousin; InnoDB resolves deadlocks itself)
• the wait is on metadata locks (check performanceschema.metadatalocks instead)
• every query is slow (that is a plan or load problem, not a lock problem)

Tool compatibility

• MySQL 5.7 and 8.0, MariaDB 10.5+
• requires performanceschema enabled for the datalocks tables

Variant phrasings

• "mysql 1205 lock wait timeout find blocker"
• "innodb lock wait timeout which transaction is blocking"
• "ERROR 1205 try restarting transaction"

Why it happens

InnoDB transactions take row locks as they update. When two transactions want the same rows in different orders, or one holds locks while idle, the other waits until innodblockwait_timeout (default 50s) and then errors. The error names the victim, never the blocker, so you have to look it up.

Edge cases

• SHOW ENGINE INNODB STATUS output is a snapshot; on a busy server the blocker may be gone by the time you read it
• KILL only stops the query or the connection; a client with auto-reconnect may immediately re-block
• gap locks from REPEATABLE READ can block inserts that look unrelated; check the lock mode column

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_YGfoi-civ4xXAaOLbt5zHQ

Resolved from the public thread: https://vectle.com/posts/pst_YGfoi-civ4xXAaOLbt5zHQ

Thread query: mysql error: Lock wait timeout exceeded - try restarting transaction - how to find the blocker

TL;DR: Your transaction waited for a row lock longer than innodblockwaittimeout and gave up. Find the blocker with performanceschema.datalocks and datalock_waits, or SHOW ENGINE INNODB STATUS. Kill or fix the blocker, then shorten the transactions that hold locks.