Diagnosing and Resolving MySQL InnoDB Row Lock Wait Timeout Errors

Background

The log snippet shows a typical MySQL error: Lock wait timeout exceeded; try restarting transaction , which is commonly referred to as a lock wait timeout. The article focuses on row‑level lock wait timeouts, which occur when multiple transactions contend for the same row.

Types of lock wait

Row lock wait timeout – occurs in high‑concurrency DML scenarios.

Metadata lock wait timeout – occurs during DDL operations (not covered in detail).

Why row lock waits happen

When several transactions try to modify the same row, InnoDB acquires a row lock to preserve ACID properties. If a transaction holds the lock longer than innodb_lock_wait_timeout (default 50 s, often set to 5 s), other transactions receive the timeout error.

Common root causes

Non‑database operations inside a transaction (e.g., API calls, file I/O) causing the transaction to pause.

Slow queries within a transaction that keep the lock held.

Large numbers of statements in a single transaction (e.g., loops inserting many rows).

Cascade updates that lock multiple tables.

Disk problems that stall transaction commit.

Difficulty of locating the problem

MySQL does not record lock‑wait details, and the same lock can be caused by many different scenarios, making post‑mortem analysis hard. Developers often need the full transaction SQL, which may require DBA assistance and the use of general_log or Performance Schema.

Common troubleshooting methods

1. Manual reproduction

Create a test table and run two transactions that deliberately cause a lock wait:

CREATE TABLE `emp` ( `id` int(11) NOT NULL, KEY `idx_id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Transaction 1

START TRANSACTION;

DELETE FROM emp WHERE id = 1;

SELECT * FROM emp WHERE id IN (SELECT id FROM emp);  -- simulate slow query

COMMIT;

-- Transaction 2

START TRANSACTION;

DELETE FROM emp WHERE id < 10;  -- waits for row lock

ROLLBACK;

2. Monitoring script for lock waits

#!/bin/bash
user="root"
password="Gepoint"
logfile="/root/innodb_lock_monitor.log"
while true; do
  num=$(mysql -u${user} -p${password} -e "SELECT COUNT(*) FROM information_schema.innodb_lock_waits" | grep -v count)
  if [[ $num -gt 0 ]]; then
    date >> ${logfile}
    mysql -u${user} -p${password} -e "SELECT r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, CONCAT(timestampdiff(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP()),'s') AS duration, b.trx_mysql_thread_id blocking_thread, t.processlist_command state, b.trx_query blocking_query, e.sql_text 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 JOIN performance_schema.threads t ON t.processlist_id = b.trx_mysql_thread_id JOIN performance_schema.events_statements_current e USING(thread_id) \G" >> ${logfile}
  fi
  sleep 5
done

Running this script (e.g., nohup sh innodb_lock_monitor.sh & ) logs waiting and blocking threads, allowing quick identification of the offending transaction.

3. Using general_log

Enable the log temporarily ( SET GLOBAL general_log = 1; ), reproduce the issue, then search the log for the waiting thread ID to retrieve the full sequence of SQL statements.

4. Performance Schema method

Enable transaction and statement instruments:

UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES' WHERE name='transaction';
UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE name LIKE '%events_transactions%';
UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE name LIKE '%events_statements%';

Query rolled‑back transactions and long‑running statements to pinpoint lock‑waits:

SELECT a.THREAD_ID, b.EVENT_ID, a.EVENT_NAME,
       CONCAT(b.TIMER_WAIT/1000000000000,'s') AS trx_duration,
       CONCAT(a.TIMER_WAIT/1000000000000,'s') AS sql_duration,
       a.SQL_TEXT, b.STATE, a.MESSAGE_TEXT
FROM performance_schema.events_statements_history_long a
JOIN performance_schema.events_transactions_history_long b
  ON a.THREAD_ID = b.THREAD_ID
WHERE b.autocommit='NO' AND a.SQL_TEXT IS NOT NULL
  AND b.STATE='ROLLED BACK'
  AND b.TIMER_WAIT/1000000000000 > 5;

This approach avoids the heavy overhead of general_log and provides per‑statement timing, but it has drawbacks: limited retention of history_long tables, need to restart MySQL to enlarge them, and lack of explicit lock‑wait records.

Summary

The article demonstrates that diagnosing InnoDB row lock wait timeouts requires a combination of reproducing the issue, monitoring lock‑wait tables, analyzing logs, and leveraging Performance Schema. Each method has trade‑offs in accuracy, performance impact, and ease of use.