Introduction

Lock wait timeout exceeded usually means the waiting query is not the real problem. Another transaction already holds the row or index lock and keeps it open too long. Raising innodb_lock_wait_timeout can make the error appear less often, but it does not remove the blocking transaction, so the better approach is to find the blocker, understand why it stayed open, and shorten that critical section.

Symptoms

  • Writes fail intermittently while reads keep working
  • The same update succeeds immediately when retried later
  • Application threads pile up behind one busy table or one hot row range
  • Incidents become worse during batch jobs, retries, or slow external API calls

Common Causes

  • A transaction holds row locks while waiting on user input or another service
  • An update scans more rows than expected because of a missing index
  • Different code paths lock the same rows in inconsistent order
  • Background jobs and interactive traffic touch the same records at the same time

Step-by-Step Fix

  1. 1.Identify the waiting transaction and the blocker
  2. 2.Query the InnoDB lock views while the incident is active so you can see which session is holding the lock.
sql
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,
  TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS blocking_age_seconds,
  b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
  1. 1.Inspect the blocking session in full
  2. 2.Confirm whether the blocker is an application thread, a migration, or a forgotten batch worker before killing anything.
sql
SHOW FULL PROCESSLIST;
  1. 1.Terminate the blocker only when you know what it is doing
  2. 2.Killing the wrong thread can roll back valuable work, but a stuck transaction sometimes has to be cleared during an incident.
sql
KILL 18452;
  1. 1.Shorten the lock window in application code
  2. 2.Keep the transaction focused on the rows you need and do not hold locks while waiting on remote work.
sql
START TRANSACTION;
SELECT id FROM orders WHERE id = 42 FOR UPDATE;
UPDATE orders SET status = 'paid' WHERE id = 42;
COMMIT;

Prevention

  • Do not keep transactions open across network calls, retries, or user interaction
  • Add indexes that narrow update and delete scans to the intended rows
  • Acquire locks in a consistent order across code paths
  • Alert on long-running transactions before they become lock-wait incidents