Diagnosing and Resolving MySQL InnoDB Deadlocks and Lock‑Wait Timeouts in Data‑Warehouse Workloads

Recent online MySQL incidents in a data‑warehouse environment caused intermittent data anomalies that only appeared during early‑morning batch jobs; the article shows how to diagnose and resolve these issues using two real cases.

Case 1 – Partial data‑update failures: A report showed zero values for a few channels while others were correct. Examining SHOW ENGINE INNODB STATUS\G revealed a deadlock between two transactions, each holding row locks and waiting for the other. The deadlock log demonstrated that InnoDB rolls back the transaction with the fewest exclusive row locks.

The root cause was the table’s poor index design. InnoDB row locks are applied to index entries, so a non‑optimal index forces range scans and locks many rows, increasing deadlock risk. By creating a four‑column composite index that matches the UPDATE predicate, the UPDATE can use a precise index lookup, eliminating the deadlock.

Why only some statements rolled back? InnoDB’s default autocommit mode commits each statement immediately, so when a deadlock occurs only the offending statement is rolled back. Additionally, the innodb_rollback_on_timeout variable controls whether a timeout rolls back the entire transaction or just the last statement.

The article lists practical ways to reduce InnoDB deadlocks: use lower isolation levels (e.g., READ COMMITTED), design efficient indexes, keep transactions short, acquire appropriate lock levels in a single step, access tables in a consistent order, prefer equality conditions over range scans, avoid unnecessary explicit locks, and consider table locks for specific workloads.

Case 2 – Mysterious lock‑wait timeout: Repeated “Lock wait timeout exceeded” errors during LOAD DATA LOCAL INFILE were traced to contention on a task‑tracking table that received many concurrent INSERT/UPDATE statements at the top of the hour. No deadlock appeared in SHOW ENGINE INNODB STATUS\G , so the investigation turned to MySQL logs.

The article reviews MySQL log types (error, general, binary, slow, InnoDB redo/undo) and explains that the relevant information was likely in the general log, which was temporarily enabled around the failure windows to capture client activity.

To automate log collection, a Bash script ( mysql_perf.sh ) is provided. It runs via cron, captures InnoDB status, open tables, process list, transaction and lock information from information_schema , and rotates logs. The script includes optional commands to toggle general_log on/off at specific times, noting the performance impact of full‑query logging.

Overall, the article demonstrates a systematic approach to MySQL troubleshooting: identify symptoms, examine InnoDB status, verify index design, adjust transaction settings, and employ targeted logging to capture transient issues.