Analysis of Lock Wait Timeout and Gap Locks in InnoDB

Lock Wait Timeout Analysis

Online error logs showed a lock wait timeout with the message Lock wait timeout exceeded; . The issue was traced to InnoDB gap locks caused by concurrent operations from different business transactions.

The database environment is MySQL with InnoDB engine and repeatable‑read isolation. The table t has columns id , biz_id , and field , with a primary key on id and an index on biz_id .

Scenario reconstruction:

Transaction 1

Transaction 2

begin;

delete from t where biz_id=100;

insert into t (biz_id, field) values (101, 'field');

BLOCKED

Transaction 2’s insert is blocked because InnoDB’s gap lock prevents other sessions from inserting into the locked range, leading to a phantom‑read situation.

Phantom Reads

A phantom read occurs when a transaction re‑executes a query and sees rows inserted by another concurrent transaction, which gap locks aim to prevent under repeatable‑read isolation.

Gap Locks

Gap locks are placed on the gaps between index records (or before the first and after the last record) to block inserts into those ranges. They are part of the next‑key lock mechanism and do not block other transactions from acquiring the same gap lock.

Gap locks are not used for unique‑index point queries, but they apply when the query range includes non‑unique values.

Disabling Gap Locks

Setting the isolation level to READ COMMITTED or enabling innodb_locks_unsafe_for_binlog disables gap locks for regular queries, leaving them active only for foreign‑key and uniqueness checks.

Mitigation Measures

Beyond shortening long transactions or lowering the isolation level, the recommended fix is to avoid gap locks by deleting rows via primary key after a prior SELECT, e.g.:

select id from t where biz_id = #{bizId};
delete from t where id = #{id};

This converts the operation to a row‑level lock instead of a gap lock.

Recruitment

The Zero technology team in Hangzhou is hiring developers with experience in cloud‑native, blockchain, AI, big data, and other areas. Interested candidates can contact [email protected] .