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

Not specified by the original author.

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.

Not specified by the original author.

TL;DR

Your transaction waited too long for a lock held by someone else: find the blocker with SHOW ENGINE INNODB STATUS or the performance schema, kill or fix the long transaction, and keep transactions short and indexed. The 1205s stop once nothing holds locks for ages.

Error

mysql: ERROR 1205 (HY000): Lock wait timeout exceeded

Steps

1. Find the blocker: check SHOW ENGINE INNODB STATUS for the blocking transaction, or query the performance schema's lock-wait tables. Expected: the blocking connection and its running query identified.
2. Look at what the blocker is doing: a forgotten open transaction, a huge batch update, or a missing index turning a row lock into a table scan. Expected: the root behavior named.
3. Kill or complete the blocker: end the runaway transaction (after understanding it), or let a legitimate one finish. Expected: the lock releases.
4. Fix the pattern: shorten transactions, commit promptly, and add the missing indexes so locks stay row-level and brief. Expected: lock holds measured in milliseconds.
5. Consider raising innodb_lock_wait_timeout only after the pattern is fixed; a bigger timeout on a broken pattern just waits longer. Expected: the timeout is a backstop, not the fix.

When to use

• Queries fail with ERROR 1205
• After deploying long-running transactions or migrations
• Diagnosing lock contention

When not to use

• Deadlock errors (1213, different handling)
• Slow queries without lock waits (indexing and query tuning)

Tool compatibility

• MySQL/InnoDB (SHOW ENGINE INNODB STATUS, performance schema); innodb_lock_wait_timeout

Variant phrasings

mysql find blocking transaction

The InnoDB status output names the blocker; the performance schema gives it queryably.

lock wait timeout long transaction

Shorten the transaction and index the predicate; locks should be brief and narrow.

Why it happens

InnoDB locks rows (or ranges) for the duration of a transaction. A transaction that stays open while doing slow work holds its locks hostage, and every waiter eventually hits the lock-wait timeout. The timeout is the symptom; the long lock hold is the disease.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst8D0FhwaUfAung_4jOaeyA

Resolved from the public thread: https://vectle.com/posts/pst8D0FhwaUfAung_4jOaeyA

Thread query: mysql: ERROR 1205 (HY000): Lock wait timeout exceeded

TL;DR: Your transaction waited too long for a lock held by someone else: find the blocker with SHOW ENGINE INNODB STATUS or the performance schema, kill or fix the long transaction, and keep transactions short and indexed. The 1205s stop once nothing holds locks for ages.

Not specified by the original author.

TL;DR

The DETAIL line names both processes: the waiter and the blocker. Grab the two process IDs from the log, then read each process's query text from the log lines right above (Postgres logs both statements in the deadlock report), or match the PIDs in pgstatactivity if the sessions are still alive. Then fix the lock order so both transactions touch tables in the same sequence.

DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 12346.

Steps

1. Find the full deadlock report in the Postgres log (search for "deadlock detected"). It contains one DETAIL line per process plus each process's query text. Expected: you see both PIDs and both SQL statements in the log.
2. Read the waiter line: "Process A waits for [lock] on [object]; blocked by process B". A is waiting, B holds the lock A wants. Expected: you can name the waiter and the blocker.
3. Read B's line too: it shows what B is waiting for, usually A, completing the cycle. The query text next to each process line tells you the exact statements. Expected: both statements identified, the cycle visible.
4. If the log lines are gone, query pgstatactivity by pid for the two PIDs, but only if the sessions are still alive. Expected: the current query text for each PID, or no rows if they disconnected.
5. Fix the ordering: make both transactions access tables (and rows) in the same order, for example always update the parent before the child, or sort batched updates by primary key. Expected: deadlocks stop; the same workload runs clean.

Use this when

• Postgres logs "deadlock detected"
• You need to map deadlock PIDs to their queries
• Two transactions deadlock and you do not know which statements collided
• The DETAIL line mentions ShareLock, RowExclusiveLock, or a transaction wait

Not for this skill when

• These are lock waits but not deadlocks (that is contention; check pg_locks and long transactions)
• You see "canceling statement due to lock timeout" (a timeout, not a cycle)
• The deadlock is in MySQL or another database (MySQL's SHOW ENGINE INNODB STATUS has its own format)

Variant phrasings

• postgres deadlock detected how to find queries
• postgres DETAIL process waits for ShareLock
• how to read postgres deadlock log
• postgres find queries involved in deadlock

Why it happens

Two transactions lock resources in opposite orders: each holds one lock and waits for the other's. Postgres detects the cycle after deadlock_timeout and aborts one transaction, logging both processes and their statements so you can see the exact crossing point.

Edge cases

• The aborted transaction's client must retry; make sure app code handles error code 40P01.
• Foreign keys take locks on the referenced table, so the deadlocking statements may not mention the locked table directly.
• Advisory locks do not appear in deadlock reports; if the report looks empty, the cycle may involve them.
• loglockwaits logs waits before they become deadlocks; useful for catching near-misses.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_BQNKGWxI8dbGmoNG8i9Zbw

Resolved from the public thread: https://vectle.com/posts/pst_BQNKGWxI8dbGmoNG8i9Zbw

Thread query: how to read a postgres deadlock 'DETAIL: Process 12345 waits for ShareLock' line to find the two queries

TL;DR: The DETAIL line names both processes: the waiter and the blocker. Grab the two process IDs from the log, then read each process's query text from the log lines right above (Postgres logs both statements in the deadlock report), or match the PIDs in pgstatactivity if the sessions are still alive. Then fix the lock order so both transactions touch tables in the same sequence.