How to Fix MySQL Lock Wait Timeout Exceeded Error

In this tutorial, you'll learn about How to Fix MySQL Lock Wait Timeout Exceeded Error. We cover key concepts, practical examples, and best practices.

The Problem

Your query times out with:

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

Another transaction holds a row-level lock that your query needs. MySQL waits for innodb_lock_wait_timeout seconds (default 50) then gives up.

Quick Fix

1. Find the blocking transaction

Run this query to find active transactions and their locks:

SELECT
  r.trx_id AS waiting_trx_id,
  r.trx_mysql_thread_id AS waiting_thread,
  b.trx_id AS blocking_trx_id,
  b.trx_mysql_thread_id AS blocking_thread
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r
  ON w.REQUESTING_ENGINE_TRANSACTION_ID = r.trx_id
JOIN information_schema.innodb_trx b
  ON w.BLOCKING_ENGINE_TRANSACTION_ID = b.trx_id;

2. Kill the blocking transaction

Once you identify the blocking thread, kill it:

-- Kill the blocking thread (replace 123 with actual thread id)
KILL 123;

Find all running processes:

SHOW FULL PROCESSLIST;

Look for queries with a long Time value or Waiting for table level lock state.

3. Commit or rollback transactions promptly

Long-running transactions cause contention. Ensure your application commits quickly:

# Wrong — transaction left open
cursor.execute("START TRANSACTION")
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
# ... long processing ...
cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
# forgot to commit!

# Right — commit immediately
cursor.execute("START TRANSACTION")
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
connection.commit()

4. Reduce transaction scope

Update only the rows you need, and keep transactions short:

-- Wrong — locks many rows unnecessarily
START TRANSACTION;
UPDATE orders SET status = 'processed' WHERE created_at < '2024-01-01';
-- ... 10 seconds of processing ...
COMMIT;

-- Right — process in batches
START TRANSACTION;
UPDATE orders SET status = 'processed' WHERE id IN (SELECT id FROM orders WHERE status = 'pending' LIMIT 100);
COMMIT;

5. Increase the timeout (temporary fix)

Increase the lock wait timeout for the session or globally:

-- For current session
SET innodb_lock_wait_timeout = 100;

-- Globally (persists until restart)
SET GLOBAL innodb_lock_wait_timeout = 100;

Update my.cnf to make it permanent:

[mysqld]
innodb_lock_wait_timeout = 100

Prevention

• Keep transactions short — commit as soon as the work is done.
• Always update rows in the same order across transactions to avoid deadlocks.
• Index columns used in WHERE clauses to reduce row locking.
• Monitor SHOW ENGINE INNODB STATUS for lock contention regularly.

Common Mistakes with lock wait

1. Placing the wildcard pattern first in case expressions, making all subsequent patterns unreachable
2. Using head and tail instead of pattern matching, causing runtime errors on empty lists
3. Forgetting that lazy evaluation defers computation until the value is forced, causing space leaks with unevaluated thunks

These mistakes appear frequently in real-world MYSQL code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ