Why One MySQL UPDATE Blocks and Another Doesn’t: Row vs. Gap Locks Explained

Background

A reader asked why, in a MySQL test, transaction B’s UPDATE on id = 10 does not block, while transaction C’s UPDATE on the same row does block.

Transaction A’s Locks

Transaction A runs:

select * from t_person where id < 10 for update;

This statement acquires three row‑level locks:

On the primary‑key index entry id = 1, an X‑type next‑key lock covering the range (‑∞,1].

On id = 5, an X‑type next‑key lock covering (1,5].

On id = 10, an X‑type gap lock covering (5,10), preventing inserts of ids 6‑9.

1. Why Transaction B’s UPDATE Does Not Block

Transaction B executes:

update t_person set name = "小林" where id = 10;

Because the query uses the unique primary‑key index, InnoDB locates the existing row and the next‑key lock on that index degrades to a simple record lock. Transaction A holds a gap lock on the range (5,10), not a record lock on id = 10. Gap locks and record locks are not mutually exclusive, so B’s UPDATE acquires an X‑type record lock on the row and proceeds without waiting.

2. Why Transaction C’s UPDATE Blocks

Transaction C runs: update t_person set id = 2 where id = 10; This UPDATE changes the primary‑key value, which InnoDB implements as two operations:

Delete the row with id = 10.

Insert a new row with id = 2.

The delete part acquires an X‑type record lock on id = 10, which does not conflict with A’s gap lock, so it does not block. The insert part must locate the proper position in the B+‑tree. The next record after the insertion point is id = 5, which is already locked by Transaction A with an X‑type next‑key lock (including a gap lock). Therefore the insert generates an insert‑intention lock that waits for the gap lock to be released, causing the whole UPDATE to block.

3. Why Updating an Indexed Value Is Split into Delete + Insert

InnoDB stores secondary indexes in a B+‑tree that must remain ordered. Changing an indexed key value directly would break the tree’s order. The engine therefore deletes the old key node and inserts a new node with the updated value, preserving the B+‑tree’s sorted property.

Images illustrate the B+‑tree before and after the change, showing how a direct modification would violate ordering and why the delete‑plus‑insert approach is required.

4. Which Operation of Transaction C Actually Blocks?

By elimination, the delete operation does not block (gap lock vs. record lock). The blocking occurs during the insert operation (operation 2), which waits for the insert‑intention lock because of the gap lock held by Transaction A on the range before id = 5.

5. Why Does the Insert Operation Block?

When inserting a new primary‑key value, InnoDB first locates the insertion point in the B+‑tree. If the next index entry is protected by a gap lock, InnoDB creates an insert‑intention lock and puts it in a waiting state. In this case, the next entry is id = 5, already locked by Transaction A, so the insert (and thus the UPDATE) is blocked.

Conclusion

If an UPDATE modifies a non‑indexed column, InnoDB acquires an X‑type record lock on the affected row, and the statement proceeds unless another transaction holds a conflicting lock.

If an UPDATE modifies an indexed column (e.g., the primary key), InnoDB internally performs a delete followed by an insert. Analysis of locking must therefore consider both operations: the delete may acquire a record lock, while the insert may be blocked by existing gap locks, as demonstrated by the interaction of transactions A, B, and C.