Why Does One MySQL UPDATE Block While Another Doesn’t? Deep Dive into InnoDB Locks

A reader asked why, when updating the same row (id = 10) in a MySQL table, one transaction’s UPDATE blocks while another’s does not. The answer lies in the different row‑level locks that InnoDB acquires depending on the statement and the index values involved.

Lock analysis of Transaction A

Transaction A runs a SELECT * FROM t_person WHERE id < 10 FOR UPDATE. This statement acquires three row‑level locks:

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

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

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

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 exact row and converts the next‑key lock on that index entry into a plain record lock that only protects the single row. Since gap locks and record locks are independent, the gap lock held by Transaction A does not conflict, so Transaction B proceeds without waiting.

Why Transaction C’s UPDATE blocks

Transaction C runs UPDATE t_person SET id = 2 WHERE id = 10. Updating a primary‑key value is special: InnoDB internally rewrites the statement as two operations:

Delete the row with id = 10.

Insert a new row with id = 2.

This transformation is necessary to preserve the B+‑tree order of the primary‑key index.

B+‑tree ordering and the need for delete‑plus‑insert

InnoDB stores primary‑key indexes as a B+‑tree, which must remain sorted. Changing a key from 25 to 3 would break the order if the value were simply overwritten. Therefore the engine deletes the old key node and inserts a new node at the correct position.

Lock conflict that causes Transaction C to wait

During the insert part of Transaction C, InnoDB must acquire an insert‑intention lock on the gap where the new key (2) will be placed. The next‑key lock held by Transaction A on id = 5 includes a gap lock covering the interval (1,5], which overlaps the insertion point. Because gap locks block insert‑intention locks, the insert operation—and thus the whole UPDATE—waits.

Running SELECT * FROM performance_schema.data_locks\G reveals that Transaction C is blocked while trying to acquire the insert‑intention lock.

Key takeaways

Updates that modify only non‑indexed columns acquire a simple X‑type record lock on the affected row.

Updates that change indexed values are internally split into a delete and an insert; lock analysis must consider both operations.

Gap locks protect ranges between index entries and can block insert‑intention locks, preventing phantom reads under REPEATABLE READ isolation.

Understanding these lock mechanics helps diagnose unexpected blocking in MySQL transactions.