Resolving MySQL InnoDB Lock Wait Timeout for Batch Inserts

**Problem description**

The development team reported recurring lock wait timeout errors during a business process, shown by the message:

Lock wait timeout exceeded; try restarting transaction

Further investigation revealed that a single INSERT statement was timing out, which is unusual for isolated inserts.

**Analysis and diagnosis**

Database version: MySQL 5.6. The InnoDB lock wait timeout variable is set to 30 seconds:

show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 30    |
+--------------------------+-------+

Slow‑query logs and SHOW ENGINE INNODB STATUS\G indicated batch insert operations causing auto‑increment lock contention. The innodb_autoinc_lock_mode was 1, meaning the auto‑increment lock is held until the statement finishes:

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+

Changing this mode to 2 would release the lock earlier, but it is a static variable requiring a server restart. Without a restart, the only option is to optimize the slow query, which took more than 100 seconds and examined 862,584 rows while returning no rows:

Query_time: 108.527499  Lock_time: 0.000342  Rows_sent: 0  Rows_examined: 862584

The original query used derived tables and a block nested‑loop join, leading to inefficient execution:

SELECT *
FROM (SELECT * FROM aa WHERE add_time >= '2022-10-01') a
LEFT JOIN (SELECT * FROM bb WHERE add_time >= '2022-10-01') b
ON a.account = b.account AND a.end_time = b.end_time AND a.app_id = b.app_id
WHERE b.account IS NULL;

Because of the derived tables, MySQL created temporary tables derived2 and derived3 , then performed a block nested‑loop join, which could not use the composite index on account, app_id, end_time . In MySQL 5.7 the optimizer could merge derived tables when derived_merge=on and the query does not contain aggregates, DISTINCT, GROUP BY, HAVING, LIMIT, UNION, subqueries in the SELECT list, user‑variable assignments, or literal‑only references.

**Solution**

After identifying the bottleneck, the SQL was rewritten to eliminate derived tables and allow the optimizer to use indexes directly:

SELECT * FROM (
  SELECT * FROM aa WHERE add_time >= '2022-10-01'
) a
LEFT JOIN bb b
ON (b.add_time >= '2022-10-01' AND a.account = b.account AND a.end_time = b.end_time AND a.app_id = b.app_id)
WHERE b.account IS NULL;

The new execution plan shows index usage and reduces the rows examined dramatically, bringing the execution time down to less than one second:

root@xsj_chat_filter> SELECT count(*) FROM (
  -> SELECT * FROM aa WHERE add_time >= '2022-10-01') a
  -> LEFT JOIN bb b
  -> ON (b.add_time >= '2022-10-01' AND a.account = b.account AND a.end_time = b.end_time AND a.app_id = b.app_id)
  -> WHERE b.account IS NULL;
+----------+
| count(*) |
+----------+
| 23       |
+----------+
1 row in set (0.65 sec)

With the query optimized, the lock wait timeout no longer occurs.