Understanding MySQL UPDATE Locking: Why Some Updates Block and Others Do Not

When a reader asked why two UPDATE statements on the same row (id=10) behave differently, the article explains MySQL row‑level locking mechanisms.

Transaction A runs a SELECT * FROM t_person WHERE id < 10 FOR UPDATE that acquires three row‑level locks: X next‑key locks on id 1 and id 5, and an X gap lock on the interval (5,10). These locks prevent other transactions from modifying or inserting rows in those ranges.

Transaction B updates only the non‑indexed column name of the row with id 10. Because the query uses the primary key, MySQL places an X record lock on that single row, which does not conflict with the gap lock held by transaction A, so B proceeds without waiting.

Transaction C attempts to change the primary key value (id 10 → 2). MySQL rewrites this UPDATE into a delete of the old row and an insert of a new row. The delete acquires an X record lock on id 10, which again does not conflict with A’s gap lock.

The insert part must locate the position for id 2 in the B+‑tree index. The next record after the insertion point is id 5, which is already protected by A’s X next‑key lock (including a gap lock). Therefore the insert generates an insert‑intention lock that waits for A, causing the whole UPDATE to block.

The article also shows how to view the locks with SELECT * FROM performance_schema.data_locks\G and emphasizes that updates of indexed columns are internally split into delete‑plus‑insert operations, so lock analysis must consider both steps.