Troubleshooting InnoDB Lock Wait Timeout Exceeded
The "ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction" message means an InnoDB transaction waited longer than innodb_lock_wait_timeout seconds (default 50) to acquire a row lock held by another transaction. Unlike a deadlock, InnoDB does not automatically detect which transaction is the blocker — it simply cancels the waiting statement after the timeout expires. Correctly diagnosing a lock wait timeout requires inspecting the TRANSACTIONS section of SHOW ENGINE INNODB STATUS to identify which active transaction holds the conflicting lock, then deciding whether to kill the blocker, increase the timeout, or redesign the locking strategy. This guide covers every diagnostic step and tuning option available in MySQL 5.7, 8.0, 8.4, and MariaDB 10.x.
Identifying the Blocking Transaction
Using information_schema.INNODB_TRX to Find Long-Running Queries
In the TRANSACTIONS section, each active transaction shows "trx started" with a timestamp and "trx_started" age in seconds. A transaction that has been active for many minutes but shows no query is almost certainly holding row locks while the application waits for user input or a network round-trip — the classic "idle in transaction" anti-pattern. Use SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT' to find waiting transactions, and JOIN to information_schema.INNODB_LOCKS to identify the exact lock object. In MySQL 8.0+, the performance_schema.data_locks and data_lock_waits tables provide the same information with better indexing and lower overhead than the older INFORMATION_SCHEMA views.
Correlating Lock Waits with performance_schema Data
The performance_schema.data_lock_waits table in MySQL 8.0 and later directly maps each blocked thread (REQUESTING_ENGINE_TRANSACTION_ID) to its blocker (BLOCKING_ENGINE_TRANSACTION_ID), eliminating the need to manually correlate rows from multiple INFORMATION_SCHEMA tables. Query SELECT r.trx_id waiting_id, r.trx_mysql_thread_id waiting_thread, b.trx_id blocking_id, b.trx_mysql_thread_id blocking_thread FROM performance_schema.data_lock_waits dlw JOIN information_schema.innodb_trx r ON r.trx_id=dlw.requesting_engine_transaction_id JOIN information_schema.innodb_trx b ON b.trx_id=dlw.blocking_engine_transaction_id to produce a ready-to-action blocker report. Once the blocking thread ID is known, KILL <thread_id> immediately releases its locks and unblocks all waiting transactions.
Connection Pool Timeout vs InnoDB Timeout Mismatch
A critical trap occurs when the application connection pool timeout (e.g., HikariCP connectionTimeout, PHP-FPM request_terminate_timeout) is shorter than innodb_lock_wait_timeout. The application gives up and closes its connection, but the underlying MySQL transaction remains open and continues holding row locks until InnoDB times it out. This mismatch is a leading cause of cascading lock pile-ups on busy servers. Always set innodb_lock_wait_timeout strictly lower than your pool and application-layer timeouts so that MySQL controls lock release, not the pool.
Configuring innodb_lock_wait_timeout
Default Value, Scope, and Dynamic Change Procedure
The global default of innodb_lock_wait_timeout=50 seconds is suitable for OLTP workloads but far too long for interactive web applications where a 50-second page load is effectively a timeout at the HTTP layer anyway. Reduce the session-level timeout to 5–10 seconds for user-facing requests with SET SESSION innodb_lock_wait_timeout=5, while keeping a higher global value for batch jobs and ETL processes that legitimately hold locks for longer periods. Setting innodb_lock_wait_timeout=1 in staging environments forces developers to surface lock contention bugs early.
innodb_rollback_on_timeout: What It Does and When to Enable It
With the default innodb_rollback_on_timeout=OFF, a lock wait timeout rolls back only the single timed-out statement, leaving the transaction open with its previously acquired locks still held. The next statement in the same transaction can still execute, potentially leaving data in an inconsistent state. With innodb_rollback_on_timeout=ON, the entire transaction is rolled back and all its locks are released immediately, providing clean failure semantics identical to a deadlock rollback.
Enabling innodb_rollback_on_timeout changes the failure mode from "one statement fails" to "the entire transaction is silently rolled back". On legacy applications that do not properly check for and handle transaction rollback errors (ER_LOCK_WAIT_TIMEOUT = 1205), this can silently abort multi-step business flows without any visible error to the user. Before enabling this setting in production, audit every code path that handles lock wait timeout errors to confirm it retries the full transaction, not just the failed statement.
Long-Term Solutions for Lock Contention
Refactoring Transactions for Shorter Hold Times
Long-running transactions are the root cause of virtually every lock wait timeout in production MySQL. A transaction that stays open for seconds or minutes accumulates row locks continuously, and any other session trying to modify those rows must wait. The most common offenders are: (1) transactions left open while application code performs external API calls or user-interaction steps, (2) ORM frameworks that wrap entire HTTP request lifecycles in a single transaction, and (3) batch jobs that process millions of rows in one giant transaction instead of committing every 1,000–10,000 rows. Monitor trx_time in information_schema.INNODB_TRX and alert when any transaction exceeds 30 seconds; anything beyond 60 seconds in an OLTP system almost certainly indicates a bug.
Optimistic Locking Patterns and SELECT FOR UPDATE Alternatives
Optimistic locking avoids acquiring row locks at read time by deferring conflict detection to the update step. Instead of SELECT … FOR UPDATE, read the row with a regular SELECT and record a version column or checksum, then at write time issue UPDATE … WHERE id=? AND version=? to detect concurrent modifications. If the UPDATE affects 0 rows, another session changed the data and the application retries from scratch. This pattern eliminates the lock hold time between read and write, reducing lock wait timeouts to near zero for read-heavy workloads with rare write conflicts. Pair optimistic locking with a monotonically incrementing INTEGER version column indexed alongside the primary key for sub-millisecond conflict detection at any scale.