Table Lock Timeout in MySQL — Fixing the Wait Limit

You get this error when a transaction holds a table or row lock for longer than the timeout allows, and another transaction tries to acquire the same lock. The exact message is Lock wait timeout exceeded; try restarting transaction. This happens most often during batch updates that run for minutes over thousands of rows, or in applications using long-running transactions that forget to commit.

Root Cause

MySQL's InnoDB engine uses row-level locking by default. When a transaction modifies a row, it places an exclusive lock on that row. Other transactions trying to modify the same row wait for the lock to release. The wait time is controlled by the system variable innodb_lock_wait_timeout. Its default is 50 seconds. If a lock isn't released within that window, MySQL kills the waiting transaction and throws error 1205.

What's actually happening here is one of three things:

• A transaction holds a lock too long due to slow queries or large row scans.
• A transaction locks rows but never commits or rolls back (left open in code).
• Multiple transactions create a lock chain where each waits for the other.

Fix Steps

1. Increase the timeout temporarily — but don't treat this as a permanent fix. It's diagnostic.
   Run SET GLOBAL innodb_lock_wait_timeout = 100; to raise it to 100 seconds. Check the current value with SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';.
   If the error goes away after raising it, you have a lock contention problem, not a timeout problem.
2. Identify the blocking transaction — the real fix is finding which transaction holds the lock.
   Query information_schema.innodb_trx to see active transactions. Look for trx_state = 'RUNNING' and trx_started older than a few seconds. Then join with innodb_locks and innodb_lock_waits to see who blocks whom.
   If you find an old transaction, let the app commit or kill it with KILL <connection_id>;.
3. Fix the query or transaction logic — this is where most people skip and just raise the timeout. Don't.
   Look for queries that scan too many rows unnecessarily. Use EXPLAIN on the problematic query. Missing indexes cause full table scans that hold row locks far longer than needed. Add an index on the columns used in WHERE and JOIN clauses for the offending table.
   Also check application code: are there transactions that do a SELECT ... FOR UPDATE and then sleep or call external APIs? Move those external calls outside the transaction. The rule is: keep transactions short, commit quickly.

If It Still Fails

Raise the timeout to 300 seconds and enable slow query logging (SET GLOBAL slow_query_log = ON;). Then reproduce the error. Check the slow query log for any query taking more than a few seconds. That's a candidate for optimization.

Also check for deadlocks: run SHOW ENGINE INNODB STATUS and look for "LATEST DETECTED DEADLOCK" section. Deadlocks are different — MySQL kills one transaction automatically. The log shows which queries deadlocked.

Finally, if you're using connection pooling, make sure your pool resets transaction state after each connection borrow. A stale transaction from a previous request can hold locks indefinitely.