What this usually means
MySQL's InnoDB engine uses row-level locks to ensure transaction isolation. When transaction A holds a lock on a row (e.g., from an UPDATE or SELECT ... FOR UPDATE) and transaction B tries to modify or lock the same row, transaction B must wait. The wait is bounded by innodb_lock_wait_timeout (default 50 seconds). If transaction A doesn't release its lock within that time (by committing or rolling back), transaction B gets 'Lock wait timeout exceeded'. This usually points to long-running transactions that hold locks, often due to missing indexes causing table scans (which lock many rows), inefficient queries, or application logic that keeps transactions open while doing slow external calls.
The first ten minutes — establish facts before touching code.
- 1Run SHOW ENGINE INNODB STATUS\G and search for 'LATEST DETECTED DEADLOCK' or 'TRANSACTIONS' section to see current lock waits.
- 2Query sys.innodb_lock_waits to get a human-readable list of blocked and blocking transactions: SELECT * FROM sys.innodb_lock_waits;
- 3Check performance_schema.data_locks and performance_schema.data_lock_waits for detailed lock info: SELECT * FROM performance_schema.data_lock_waits\G
- 4Identify the blocking transaction's processlist ID and examine its current query and elapsed time: SHOW FULL PROCESSLIST; look for long-running queries.
- 5Measure the current innodb_lock_wait_timeout value: SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
The specific files, logs, configs, and dashboards that usually own this bug.
- search/var/log/mysql/error.log or MySQL error log for lock wait timeout warnings.
- searchINFORMATION_SCHEMA.INNODB_TRX for active transactions and their duration (trx_started, trx_mysql_thread_id).
- searchsys.innodb_lock_waits view (if sys schema is installed) for blocking relationships.
- searchperformance_schema.events_statements_current and events_statements_history for the SQL statements in the blocking transaction.
- searchApplication logs for the exact query that failed and the stack trace; check for long-running transactions in the app code.
- searchSlow query log (if enabled) to identify queries that run too long and hold locks.
Practical causes, not theory. These are the things you will actually find.
- warningLong-running transaction in application code that does external API calls or file I/O while holding database locks.
- warningMissing index causing a row lock to escalate to a gap lock or next-key lock, locking more rows than intended.
- warningImplicit lock on gap due to non-unique index in REPEATABLE READ isolation level, causing phantom row locking.
- warningTransaction not properly closed (commit/rollback) due to unhandled exceptions or missing finally block.
- warningHigh concurrency on a single hot row (e.g., counter, inventory) causing serialization of operations.
- warningBatch update or DELETE statements that touch many rows in a single transaction, holding locks for extended time.
Concrete fix directions. Pick the one that matches your root cause.
- buildReduce transaction scope: commit as early as possible, move slow operations outside the transaction.
- buildAdd appropriate indexes to narrow down the rows locked; use EXPLAIN to verify index usage.
- buildIf using REPEATABLE READ, consider switching to READ COMMITTED if gap locks are not needed (set transaction_isolation).
- buildImplement retry logic in the application for lock wait timeout errors (e.g., up to 3 retries with exponential backoff).
- buildUse SELECT ... FOR UPDATE NOWAIT or SKIP LOCKED for optimistic locking when appropriate (MySQL 8.0+).
- buildIncrease innodb_lock_wait_timeout temporarily as a band-aid, but only after addressing root causes.
A fix you cannot prove is a guess. Close the loop.
- verifiedAfter deploying the fix, simulate concurrent writes using a load test (e.g., with sysbench or custom script) targeting the same rows.
- verifiedMonitor sys.innodb_lock_waits to ensure no new lock waits appear during the load test.
- verifiedCheck application logs for zero occurrences of 'Lock wait timeout exceeded' after fix.
- verifiedVerify that the average innodb_row_lock_time decreases in performance_schema metrics.
- verifiedRun EXPLAIN on the previously problematic queries to confirm index usage and reduced rows examined.
Things that make this bug worse or harder to find.
- warningBlindly increasing innodb_lock_wait_timeout without understanding why locks are held — this just delays the problem.
- warningKilling the blocked transaction instead of the blocking transaction — the blocker is the one holding the lock.
- warningUsing autocommit=0 without explicit commit in application code, causing transactions to span multiple requests.
- warningAssuming row-level locking means only one row is locked — InnoDB can lock gaps and index ranges.
- warningIgnoring deadlock detection: if you see deadlocks, fix those first as they often accompany lock wait timeouts.
- warningSetting innodb_lock_wait_timeout too low (e.g., 1 second) without proper retry logic, causing spurious failures.
Payment service timeout causes cascading lock wait failures
Timeline
- 10:15PagerDuty alert: 30% of order placement requests failing with 'Lock wait timeout exceeded'.
- 10:18Checked application logs: all failures happen on UPDATE orders SET status='paid' WHERE id=?
- 10:21Ran SHOW PROCESSLIST: 12 connections in 'Waiting for row lock' on orders table, one connection running UPDATE with 45 seconds elapsed.
- 10:24Queried sys.innodb_lock_waits: blocking transaction (ID 12345) holding X lock on row id=9876, blocked transaction (ID 12346) waiting.
- 10:27Checked blocking transaction's query: SELECT * FROM orders WHERE id=9876 FOR UPDATE, then external HTTP call to payment gateway.
- 10:32Found that payment gateway was timing out (30s), keeping transaction open. Killed the blocking transaction (KILL 12345).
- 10:35Orders started processing again. Deployed hotfix to move payment gateway call outside the transaction.
I was on-call when our order placement endpoint started failing en masse. The error was clear: 'Lock wait timeout exceeded' on an UPDATE statement. I immediately checked the MySQL process list and saw a dozen queries all waiting for the same row lock. The blocking query was an UPDATE that had been running for 45 seconds — that's suspicious for a simple row update.
I dug into the blocking transaction's details using sys.innodb_lock_waits. It turned out the transaction was doing a SELECT ... FOR UPDATE on the order row, then making an HTTP call to the payment gateway. The gateway was slow, causing the transaction to stay open for 30+ seconds. During that time, any other request trying to update that order (e.g., for payment confirmation or inventory decrement) would wait and eventually timeout.
I killed the blocking transaction to restore service immediately. Then I worked with the team to refactor the code: the payment gateway call was moved outside the database transaction, so the lock is held only for the actual UPDATE (a few milliseconds). We also added a retry mechanism for lock wait timeouts as a safety net. After the fix, we load-tested with 100 concurrent orders and saw zero lock wait errors.
Root cause
An external HTTP call to a slow payment gateway was made inside a database transaction, holding a row-level X lock for 30+ seconds.
The fix
Moved the payment gateway call outside the transaction. Added retry logic on lock wait timeout (3 retries with 100ms backoff).
The lesson
Never perform external I/O (HTTP, file, etc.) inside a database transaction. Keep transactions as short as possible — only the minimal SQL operations should be within the transaction boundaries.
InnoDB uses row-level locks (X, S), gap locks, and next-key locks. A row lock can be shared (S) for reads, or exclusive (X) for writes. Gap locks prevent phantom rows in REPEATABLE READ isolation level. Next-key locks combine a row lock with a gap lock on the index gap before the row.
Lock compatibility: X locks are incompatible with any other lock (including S). S locks are compatible with other S locks but not X. Gap locks are compatible with each other but not with X locks that try to insert into the gap. When a transaction waits on a lock, it's because another transaction holds an incompatible lock on the same resource.
Performance Schema tables like data_locks and data_lock_waits provide detailed lock information. Query them instead of INFORMATION_SCHEMA for lower overhead. Example: SELECT * FROM performance_schema.data_lock_waits; This shows which transactions are waiting and which are blocking.
The sys.innodb_lock_waits view is a convenient wrapper. But for deep analysis, join data_locks with events_statements_current to get the exact SQL of the blocking session. This helps pinpoint the query causing the lock.
Pessimistic locking (SELECT ... FOR UPDATE) is the typical cause of lock wait timeouts. If your application can tolerate occasional retries, consider optimistic locking using a version column or timestamp. This avoids holding locks altogether.
If pessimistic locking is required, use NOWAIT or SKIP LOCKED (MySQL 8.0+). NOWAIT immediately fails if the row is locked, rather than waiting. SKIP LOCKED skips locked rows entirely — useful for queue-style workloads where you want to grab the next available row.
Percona Toolkit's pt-mysql-summary provides a quick overview of lock waits. Run it during an incident to see the top queries blocking others. Example: pt-mysql-summary --host=localhost --user=root --ask-pass
For continuous monitoring, set up a cron job to capture SHOW ENGINE INNODB STATUS every minute and alert on 'Lock wait' counts. Tools like Prometheus + mysqld_exporter can expose InnoDB metrics (innodb_row_lock_current_waits) and trigger alerts.
Frequently asked questions
How do I find which transaction is blocking my query?
Use SELECT * FROM sys.innodb_lock_waits; This shows the blocked and blocking transaction IDs. Then look up the blocking transaction's details in INFORMATION_SCHEMA.INNODB_TRX to get the MySQL thread ID and query. Alternatively, query performance_schema.data_lock_waits directly.
Should I kill the blocked or blocking transaction?
Always kill the blocking transaction (the one holding the lock). Use KILL <thread_id>; where thread_id is the MySQL connection ID from SHOW PROCESSLIST. Killing a blocked transaction only frees up that thread, but the lock remains held by the blocker, so the problem persists.
Does using autocommit=1 prevent lock wait timeouts?
No, autocommit=1 only ensures each statement is its own transaction. If a single statement (e.g., UPDATE) affects many rows and takes long, it can still hold locks for a long time. The timeout depends on how long the lock is held, not the number of statements.
Can missing indexes cause lock wait timeouts?
Yes. Without an index, UPDATE or DELETE may scan the entire table and lock many rows (or gaps) unintentionally. Adding a proper index narrows down the locked rows, reducing contention and lock duration.
What is the difference between deadlock and lock wait timeout?
A deadlock occurs when two transactions hold locks that the other needs, forming a cycle. InnoDB immediately detects this and rolls back one transaction (the 'deadlock victim'). A lock wait timeout is when a transaction waits longer than innodb_lock_wait_timeout for a lock held by another transaction — no cycle, just slow release.