Why Does One MySQL UPDATE Block While Another Doesn’t? A Deep Dive into Row‑Level Locks

A reader observed that, when running three concurrent transactions on a MySQL table, transaction B’s UPDATE on id = 10 does not block, whereas transaction C’s UPDATE on the same row does block, even though both target the same record.

Locks set by Transaction A

Transaction A executes a 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.

Why Transaction B’s UPDATE does not block

Transaction B runs UPDATE t_person SET name = "小林" WHERE id = 10. Because the WHERE clause uses the unique primary‑key index, InnoDB locates the exact row and the next‑key lock on that index entry degrades to a plain record lock. The lock held by Transaction A on id = 10 is a gap lock, which does not conflict with a record lock, so Transaction B proceeds without waiting.

Why Transaction C’s UPDATE blocks

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

Delete the row where id = 10.

Insert a new row with id = 2.

This rewrite is necessary because the B+‑tree index that stores primary‑key values must remain ordered. Changing a key from 25 to 3, for example, would break the sorted order, so the engine deletes the old key node and inserts a new one at the correct position.

Which operation causes the block?

The delete part (operation 1) acquires an X‑type record lock on id = 10. Since Transaction A only holds a gap lock on that key, the delete does not conflict and proceeds.

The insert part (operation 2) must place the new record ( id = 2) into the B+‑tree. The insertion point is just before the existing id = 5 entry, whose index range is already protected by the X‑type next‑key lock (including a gap lock) held by Transaction A. Consequently, the insert acquires an insert‑intention lock that waits for Transaction A to release its next‑key lock, causing the overall UPDATE to block.

Observing the lock conflict

Running SELECT * FROM performance_schema.data_locks\G shows that Transaction C is waiting for an insert‑intention lock, confirming that the block originates from the second operation.

Takeaways

• Updating a non‑indexed column (or a regular column) results in a simple X‑type record lock on the affected row.

• Updating a primary‑key or any indexed column causes MySQL to split the UPDATE into a delete followed by an insert; lock analysis must consider both steps.

• Gap locks prevent other transactions from inserting new rows into the locked range, which is why the insert part of Transaction C’s UPDATE is blocked.