Reducing Gap-Lock Pain in MySQL REPEATABLE READ

The strangest lock waits in MySQL are the ones on rows that do not exist. Two transactions insert different order numbers and deadlock anyway. A DELETE on a range nobody else touches blocks an INSERT far away from it. A batch job holds up the write path over key values it never read. All of these are gap locks doing exactly what they were designed to do under REPEATABLE READ, InnoDB's default isolation level, and they are the source of a large share of "MySQL is randomly locking" complaints I get asked about.

Gap locks exist for a good reason: they prevent phantom rows from appearing inside a range a transaction has already scanned, which keeps locking reads and replication consistent. But the price is that locking statements lock ranges of the index, not just rows. These are my working patterns for shrinking that footprint on MySQL 8.0 and 8.4 without breaking correctness.

Know which statements take gap locks

Plain SELECT statements under REPEATABLE READ use consistent nonlocking reads from a snapshot and take no row locks at all. The gap-lock story starts with locking statements: UPDATE, DELETE, SELECT ... FOR UPDATE, SELECT ... FOR SHARE, and the checks behind INSERT. For those, the rule of thumb is that InnoDB locks what it scans. An equality lookup on a unique index that finds its row takes a record lock on just that row, with no gap. Anything else, a range predicate, a scan on a non-unique secondary index, an equality lookup that finds nothing, escalates to next-key locks, which cover a record plus the gap before it, or to pure gap locks on empty space.

That rule contains almost every reduction pattern. The differences from PostgreSQL here are big enough that I wrote them up separately in MySQL gap locks vs PostgreSQL SSI; PostgreSQL never gap-locks, it detects conflicts instead, so intuitions do not transfer between the engines.

Verify with data_locks instead of guessing

Before changing schema or isolation levels, look at what your actual statements lock. Open a transaction, run the statement, and inspect it from another session:

SELECT index_name,
       lock_type,
       lock_mode,
       lock_status,
       lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'app'
  AND object_name = 'orders'
ORDER BY index_name, lock_data;

The lock_mode column tells you which regime you are in. X,REC_NOT_GAP means a clean single-record lock, the best case. X alone is a next-key lock. X,GAP is a gap-only lock, and X,INSERT_INTENTION shows an insert waiting to enter a locked gap. If lock_data shows "supremum pseudo-record", your statement locked everything from the last index entry to infinity, which is a common surprise with open-ended range predicates. Ten minutes of this exercise on your real schema is worth more than any amount of documentation reading.

Pattern 1: unique keys plus equality predicates

The single most effective change is making hot write paths address rows through equality on a unique index, usually the primary key. UPDATE ... WHERE id = 42 on an existing row takes one record lock. The same logical change expressed as UPDATE ... WHERE external_ref = 'A-42' on a non-unique index takes next-key locks on the matching entries plus the gap beyond them, blocking inserts of neighboring values.

Two corollaries follow. First, add unique constraints where the data model already guarantees uniqueness; a column that is unique in practice but not declared unique gets the pessimistic range-locking treatment anyway. Second, split read-then-write flows so the write lands on the primary key: select the ids you need first with a nonlocking read, then update by id. The lock footprint drops from a range to a handful of records.

Also watch the miss case. SELECT ... FOR UPDATE on a unique key that finds no row leaves a gap lock where the row would be. Two sessions can both hold that same gap lock, because gap locks do not conflict with each other, then both try to INSERT into the gap and deadlock, because inserts do conflict with gap locks. If your code does "lock, check absent, insert", prefer a plain INSERT with a unique key and handle the duplicate-key error, or use INSERT ... ON DUPLICATE KEY UPDATE.

Pattern 2: keep locking scans off unindexed columns

If an UPDATE or DELETE has no usable index for its predicate, InnoDB scans the table and locks every row it examines, effectively next-key locking the whole table under REPEATABLE READ. This is the pathological case behind "one DELETE froze everything". The fix is unglamorous: index the write predicates, and batch large deletes into small primary-key ranges so each transaction holds a narrow window of locks. Deadlock reports full of gap and insert intention locks usually point back to exactly this, and the report-parsing method in deadlock diagnosis in MySQL vs PostgreSQL will name the offending index for you.

For queue-shaped workloads, where workers grab pending rows, skip the fight entirely:

START TRANSACTION;
SELECT id
FROM jobs
WHERE status = 'ready'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- process the claimed rows, mark them done, then COMMIT

SKIP LOCKED lets each worker pass over rows another worker has locked instead of queueing on them, which removes both the waits and most queue-related deadlocks. It is available in MySQL 8.0 and later, and NOWAIT is the fail-fast sibling when skipping rows is not acceptable.

Pattern 3: READ COMMITTED, with its real tradeoffs

Dropping to READ COMMITTED disables gap locking for ordinary statements; only foreign-key checks and duplicate-key checks still use it. Locking statements lock just the rows they match, and locks on non-matching scanned rows are released after the WHERE clause is evaluated instead of being held until commit. For write-heavy, insert-heavy workloads this removes entire classes of contention, and it is a legitimate, widely used production choice:

SET SESSION transaction_isolation = 'READ-COMMITTED';
-- or per transaction, before it starts:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

The tradeoffs are real, so name them honestly. Each statement in a transaction reads its own fresh snapshot, so two reads in one transaction can see different data, and phantoms are possible. Any logic that assumed a stable repeatable snapshot must be rechecked. Binary logging must be row-based, which is the 8.0 default, but verify binlog_format on an instance with history. And UPDATE statements use semi-consistent reads, which reduces waiting but means a statement can skip rows based on values that changed while it ran. My rule: prefer READ COMMITTED per session for the specific hot write paths that suffer, rather than flipping the global default on a codebase written under REPEATABLE READ assumptions.

Pattern 4: remember foreign keys lock the parent

Foreign keys create locks that appear in no query text. Inserting a child row takes a shared record lock on the referenced parent row to guarantee it cannot vanish before commit. Updating or deleting a parent checks and locks rows in the child table, and gap locking for these constraint checks happens even under READ COMMITTED. The classic symptom is a hot parent row, one merchant, one tenant, one account, whose children are inserted by every request: any transaction that updates that parent row now conflicts with every child insert. Mitigations are structural. Keep parent-row updates out of hot paths, move frequently updated counters off the parent table into their own rows, and keep transactions that touch parent rows short. Some teams eventually drop the constraint and enforce integrity in the application; that is a real tradeoff with real risks, not a free win, but it is worth naming as the endpoint of this path.

Monitoring gap-lock pressure, and where MonPG stands

Gap-lock pain shows up in metrics before it shows up in tickets: rising lock-wait counts in innodb_metrics, recurring GAP and INSERT_INTENTION modes in sampled data_locks output, deadlock reports concentrated on one secondary index, and lock waits attributed to indexes rather than primary keys. Sampling those signals over time is what turns "MySQL randomly locks" into "this index, this statement, this fix", and it belongs in a standing MySQL monitoring baseline rather than in incident-day heroics.

Full disclosure on tooling: MonPG is a PostgreSQL monitoring platform today, and it does not monitor MySQL yet. MySQL support is under active development, with lock-mode-aware wait tracking part of the plan, and the MySQL monitoring (coming soon) page is the place to watch for it. If PostgreSQL is also in your stack, MonPG already provides the equivalent lock and transaction evidence there, and you can start there now.