How to Fix Lock Wait Timeout Exceeded Error in MySQL

The lock wait timeout exceeded error in MySQL happens when a transaction waits too long for a locked resource. To fix it, identify and optimize the blocking queries or increase the innodb_lock_wait_timeout setting to allow longer waits.

Why This Happens

This error occurs because one transaction is waiting for another to release a lock on a row or table, but the wait time exceeds the configured timeout. It usually happens when a transaction holds locks too long or when multiple transactions try to update the same data simultaneously.

sql

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Transaction is left open without commit or rollback
-- Another transaction tries to update the same row
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- This second update waits and eventually fails with lock wait timeout exceeded;

Output

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

The Fix

To fix this, make sure transactions are short and commit or rollback quickly. Also, avoid long-running transactions that hold locks. You can increase the innodb_lock_wait_timeout value if needed, but optimizing queries and transaction logic is better.

sql

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

Output

Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)

Prevention

To avoid this error in the future:

• Keep transactions short and commit or rollback as soon as possible.
• Use proper indexing to speed up queries and reduce lock time.
• Avoid user interactions inside transactions.
• Monitor and analyze slow or blocking queries regularly.
• Consider increasing innodb_lock_wait_timeout only if necessary.

Related Errors

Other errors related to locking include:

• Deadlock found when trying to get lock; this means two transactions wait on each other and MySQL rolls back one.
• Lock wait timeout exceeded; as explained, caused by long waits for locks.
• Lock wait timeout exceeded on metadata lock; happens when DDL statements wait for locks.

Fixes usually involve optimizing transactions and queries or handling deadlocks by retrying transactions.

Key Takeaways

Keep transactions short and commit or rollback quickly to avoid long locks.

Optimize queries and indexing to reduce lock contention.

Increase innodb_lock_wait_timeout only if necessary after optimization.

Monitor blocking queries to identify and fix locking issues early.

Handle deadlocks by retrying transactions or redesigning logic.