Introduction to InnoDB Row Locks, Splitting, Inheritance, and Migration

InnoDB row locks consist of a lock mode (shared LOCK_S or exclusive LOCK_X ) combined with a GAP type that determines whether the lock applies to the record, the gap before it, or both.

GAP types include LOCK_GAP (gap only), LOCK_REC_NO_GAP (record only), LOCK_ORDINARY (record and preceding gap), and LOCK_INSERT_INTENTION (used during INSERT to detect conflicts).

A complete lock is expressed as a combination, e.g., LOCK_X|LOCK_ORDINARY locks the record and its preceding gap, while LOCK_S|LOCK_GAP locks only the gap before a record. GAP locks are compatible with most other locks, which is why they are primarily used to prevent phantom inserts.

Lock Splitting

When an INSERT creates a new record inside a gap that already holds a GAP lock, the original GAP lock is split into two separate GAP locks to protect both sides of the new record.

Lock Inheritance

If a record is deleted and a GAP lock exists before it, that GAP lock is inherited by the next record, ensuring the protection range does not shrink.

Lock Migration

During B‑tree structural changes (node split, merge, or delete), lock information is migrated so that the locked range remains unchanged. The migration follows specific rules such as moving GAP locks from the original node to the appropriate child nodes.

Code Example – Lock Inheritance (gap lock)

for (lock = lock_rec_get_first(block, heap_no);
lock != NULL;
lock = lock_rec_get_next(heap_no, lock)) {
if (!lock_rec_get_insert_intention(lock) &&
(heap_no == PAGE_HEAP_NO_SUPREMUM || !lock_rec_get_rec_not_gap(lock))) {
lock_rec_add_to_queue(
LOCK_REC | LOCK_GAP | lock_get_mode(lock),
block, heir_heap_no, lock->index,
lock->trx, FALSE);
}
}

Code Example – General Lock Inheritance

for (lock = lock_rec_get_first(block, heap_no);
lock != NULL;
lock = lock_rec_get_next(heap_no, lock)) {
if (!lock_rec_get_insert_intention(lock) &&
!((srv_locks_unsafe_for_binlog || lock->trx->isolation_level <= TRX_ISO_READ_COMMITTED) &&
lock_get_mode(lock) == (lock->trx->duplicates ? LOCK_S : LOCK_X))) {
lock_rec_add_to_queue(
LOCK_REC | LOCK_GAP | lock_get_mode(lock),
heir_block, heir_heap_no, lock->index,
lock->trx, FALSE);
}
}

SQL Demonstration – Lock Splitting

set global tx_isolation='repeatable-read';
create table t1(c1 int primary key, c2 int unique) engine=innodb;
insert into t1 values(1,1);
begin;
-- The supremum record acquires LOCK_X|LOCK_GAP (locks (1~))
select * from t1 where c2=2 for update;
-- Inserting (3,3) finds a GAP lock and acquires LOCK_X|LOCK_GAP as well, still locking (1~)
insert into t1 values(3,3);

If the INSERT of (3,3) did not acquire the GAP lock, another session could successfully insert (2,2), demonstrating the protective role of the GAP lock.

SQL Demonstration – Lock Inheritance

Running two sessions under repeatable-read isolation shows that a DELETE leaves a GAP lock that is inherited by the next record, causing INSERTs to wait when they encounter the inherited lock.

Similar experiments under read‑committed and serializable isolation levels illustrate how lock inheritance interacts with different isolation guarantees.

B‑Tree Structure Changes and Lock Migration

When a B‑tree node splits, merges, or is deleted, the lock ranges are transferred according to rules such as lock_update_split_right , lock_update_merge_left , and lock_update_discard . After migration, the effective locked interval (e.g., (1~)) remains unchanged.

Related Bugs

Bug #73170 and Bug #76927 involve secondary unique index failures caused by incorrect lock inheritance when a deleted record has not yet been purged. Detailed discussions are available in the referenced MySQL monthly reports.