MySQL

MySQL "Lock Wait Timeout Exceeded" Try Restarting Transaction

"Lock wait timeout exceeded" is different from a deadlock — it means your transaction waited the full configured timeout for a lock held by another transaction, with no circular dependency for MySQL to detect and resolve automatically, and no automatic rollback happening on MySQL's own initiative.

The Problem

A query or transaction fails after waiting, rather than either succeeding or failing immediately:

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

Unlike a deadlock error (which appears quickly, since MySQL's deadlock detector actively identifies circular waits), this error only appears after the full configured wait time has elapsed — by default, 50 seconds.

Why It Happens

This error means some other transaction is holding a lock your query needs, and simply hasn't released it — often because that other transaction is genuinely still in progress, or, very commonly, because it was left open unintentionally. Common causes:

  • A long-running transaction (a large batch update, a slow report query wrapped in an explicit transaction) genuinely holds a lock for an extended period, and anything else needing a conflicting lock simply has to wait its full duration
  • An application connection began a transaction and never explicitly committed or rolled it back — sitting idle while still holding whatever locks it acquired, blocking everything else indefinitely until that connection is eventually closed or times out on its own
  • High concurrency on frequently-updated rows (a popular product's inventory count, a shared counter) creates natural lock contention even without any bug, simply from many transactions legitimately needing the same specific rows around the same time
  • A missing or inefficient index causing a query to lock far more rows than logically necessary for its actual intent, increasing the chance of colliding with other transactions' lock needs

The Fix

First, identify what's actually holding the blocking lock, rather than just retrying blindly:

SELECT * FROM information_schema.innodb_trx;

This shows currently active transactions, including how long each has been running — a transaction with an unusually long trx_started duration relative to what it should reasonably take is a strong candidate for being the blocker.

For more detail specifically on lock waits, check the lock wait information directly (syntax varies slightly by MySQL version):

SELECT * FROM performance_schema.data_lock_waits;

This surfaces exactly which transaction is waiting on which other transaction, similar to the deadlock diagnosis query but for the lock-wait-timeout scenario specifically.

If you find a transaction that's idle and clearly abandoned (an application connection that never committed), terminate it directly:

KILL <connection_id>;

To prevent connections being left open indefinitely in an idle transaction state in the first place, review your application's connection handling for missing commit/rollback calls, particularly on error paths that might skip normal cleanup logic — this is the same category of issue that causes connection pool leaks, and often the same code fix resolves both problems simultaneously.

If the timeout is genuinely too short for legitimate, longer-running operations in your specific workload, it can be adjusted, though this should be a deliberate decision rather than a blanket increase applied without understanding why waits are happening in the first place:

SET GLOBAL innodb_lock_wait_timeout = 100;

Raising this value doesn't reduce lock contention itself — it just gives legitimately slow operations more time before giving up, while also meaning any genuinely stuck situation takes longer to surface as an error, which is a real tradeoff worth considering rather than simply maximizing the value by default.

Still Not Working?

If lock waits are frequent and not clearly tied to one obviously identifiable long-running or abandoned transaction, review whether your application's transactions are scoped as tightly as possible — beginning a transaction, doing unrelated work (like an external API call) in the middle of it, and only then committing needlessly extends how long locks are held for no database-related reason. Restructuring so transactions open right before the database work begins and commit immediately after it finishes, rather than spanning unrelated application logic, is one of the most effective general strategies for reducing lock contention across the board, beyond fixing any single specific incident.

You might also like