Skip to content

CONCEPT Cited by 2 sources

Row-level lock contention

Row-level lock contention is what happens when multiple concurrent transactions need exclusive access to the same row in a row-locking database engine. Canonical instance: InnoDB's lock_mode X locks rec but not gap on UPDATE counters SET count = count + 1 WHERE id = 1 when many clients increment the same counter simultaneously. Only one transaction holds the X lock at a time; all others queue.

Diagnostic signature

SHOW ENGINE INNODB STATUS\G surfaces the contention pattern directly:

---TRANSACTION 79853106, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 24, OS thread handle 6281670656, query id 107 localhost root updating
UPDATE slotted_counters SET count = count + 1 WHERE id = 1
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 184 index PRIMARY of table `github`.`downloads` trx id 79853106 lock_mode X locks rec but not gap waiting

Key fields:

  • LOCK WAIT — transaction is stalled on a lock it wants.
  • TRX HAS BEEN WAITING N SEC — wall-clock lock-wait duration.
  • lock_mode X locks rec but not gap waiting — exclusive record lock, not the broader gap/next-key lock, waiting (not granted).
  • trx id ... + space id ... page no ... — the specific on-disk row the lock is blocked on.

Consequences

  • Latency spikes. Writes that should be sub-millisecond block for lock-wait duration; under sustained load, tails grow and mean latency follows.
  • Concurrency collapses to 1. All writers effectively serialise on the contended row.
  • Deadlock risk. When transactions acquire multiple locks in different orders, deadlock becomes possible. InnoDB detects and rolls one back, but that failure path is a correctness-visible side-effect of contention.
  • Query timeouts. Default innodb_lock_wait_timeout (50 s) applies — under bad contention, writes fail outright with Lock wait timeout exceeded.
  • Hot row — the data-shape characterisation (one row receiving disproportionate writes). Row-level lock contention is the mechanical symptom of a hot row on a row-locking engine.
  • Deadlock vs lock contention — contention is serial waiting; deadlock is mutual waiting. Sustained contention makes deadlock more likely but is not itself deadlock.
  • Gap / next-key locks — InnoDB can lock the gap between rows (to prevent phantom reads under REPEATABLE READ); locks rec but not gap specifically means the record-only variant, narrower than a next-key lock.
  • Page-level locking — some engines lock whole data pages, not individual rows. Row-level is finer-grained but still serialises on any one row.

Mitigations

  • Slotted counter pattern — replace one row with N, pick a random slot per write, sum on read. Reduces collision probability to 1/N. (Source: sources/2025-02-04-planetscale-the-slotted-counter-pattern.)
  • Upsert via unique-key INSERT ... ON DUPLICATE KEY UPDATE — lets first write to a slot insert + subsequent increment without a separate prepare step.
  • Batched / coalesced writes — accumulate N increments in application memory, flush once. Trades durability window for lock-acquisition frequency.
  • Move counters out of the OLTP database entirely — Redis INCR, append-only event log + rollup, or a dedicated counter service like Netflix's Distributed Counter. Structural rather than tactical.
  • Reduce transaction scope — hold the lock for as little time as possible; avoid mixing the contended row with other work in the same transaction.
  • Reduce isolation level — READ COMMITTED drops next-key locks on SELECTs, narrowing contention, but changes semantics.

Seen in

  • sources/2025-02-04-planetscale-the-slotted-counter-pattern — canonical diagnosis + SHOW ENGINE INNODB STATUS\G snippet + counter workload framing; introduces the slotted-counter pattern as the MySQL-specific mitigation.

  • sources/2026-04-21-planetscale-achieving-data-consistency-with-the-consistent-lookup-vindex — canonical wiki instance of row-level lock contention as a deliberate correctness primitive, not a failure mode to mitigate. Vitess's Consistent Lookup Vindex issues SELECT ... FOR UPDATE on both the authoritative user-table row and the dependent lookup-table row across two different MySQL connections per DML, holding the X locks until the ordered commit completes. The article canonicalises a subtle deadlock case the mechanism would hit without a special-case: an update that sets a Vindex column to its current value would cause the Post-connection delete and Pre-connection insert to deadlock against each other on the same lookup-table row — fixed by turning identity Vindex updates into no-ops. Illustrates that row-level locking isn't only a contention problem; it's the building block sharding layers compose over, and compositions must be engineered around the locking semantics.

Last updated · 319 distilled / 1,201 read