MySQL gap locks and next-key locks: surprising deadlocks under REPEATABLE READ

MySQL deadlocks in production frequently involve transactions that modify apparently unrelated rows. Two concurrent UPDATEs with narrow WHERE clauses collide, or an UPDATE blocks every INSERT into the table. The cause is usually REPEATABLE READ combined with InnoDB next-key locking and a missing or non-unique index.

Under REPEATABLE READ, the default in MySQL, InnoDB does not lock only matching rows. To prevent phantom reads, it locks the gaps between index entries. When no suitable index exists, or when the optimizer scans a non-unique index, a targeted UPDATE escalates into a range lock covering far more of the table than the predicate suggests.

What it is and why it matters

A next-key lock is an index-record lock plus a gap lock on the gap immediately preceding that index record. Under REPEATABLE READ, InnoDB applies next-key locks by default during searches and index scans. The lock covers the interval from the previous index value up to and including the scanned record.

Gap locks are purely inhibitive: they block inserts into the locked gap, but do not conflict with each other. Multiple transactions can hold gap locks on the same gap simultaneously. Conflict occurs when one transaction holds a gap lock and another tries to insert into that gap, or when overlapping next-key ranges form a wait cycle.

Without gap locks, another session could insert into a range the first transaction scanned, violating REPEATABLE READ guarantees. The operational cost is that seemingly harmless queries acquire wide locks, turning a point update into range contention that blocks concurrent writes.

How it works

Lock scope is determined by the query plan, not the WHERE clause alone. When InnoDB executes a locking read, UPDATE, or DELETE, it follows the index.

  • If the query uses a unique index with an equality condition, InnoDB locks only the matching index record.
  • If the query uses a non-unique index or a range condition, InnoDB locks every index record it scans and the gaps between them.
  • If there is no usable index, InnoDB scans the entire table and locks every row it encounters.

An UPDATE t SET val = 1 WHERE status = 'pending' with no index on status scans the entire clustered index. InnoDB sets exclusive next-key locks on every row. The result is functionally equivalent to a table lock: no other session can insert until the transaction commits.

Even with an index, scans lock non-matching rows encountered along the way. Under REPEATABLE READ, InnoDB holds locks on all rows examined during the scan, including those that do not satisfy the WHERE clause.

If a transaction locks a gap where no row exists, such as SELECT * FROM t WHERE id = 999 FOR UPDATE when id 999 is missing, InnoDB still locks the gap where that row would be inserted. Any concurrent insert of id 999, or an adjacent value within the locked gap, will wait or deadlock.

InnoDB does not release locks early. Once acquired, next-key locks are held until the transaction commits or rolls back, even for rows that were examined but not modified. A transaction that scans one million rows to update ten holds one million next-key locks for the duration of the transaction. A long-running SELECT ... FOR UPDATE is as destructive as a long-running UPDATE.

When two transactions scan overlapping ranges, each holds locks the other needs. Transaction A locks rows 1 through 10. Transaction B locks rows 5 through 15. If both then try to update or insert within the overlap, InnoDB’s deadlock detector rolls back the cheaper transaction. The rows each transaction originally wanted to modify might be disjoint; it is the scanned range, not the application predicate, that creates the overlap.

flowchart TD
    Q[Locking query] --> P{Optimizer index path}
    P -->|No usable index| W[Full scan locks every row]
    P -->|Unique equality| R[Record lock only]
    P -->|Range or non-unique| N[Next-key locks on scanned range]
    N --> L[Locks rows and gaps encountered]
    W --> C[Blocks concurrent inserts]
    L --> C
    R --> S[Low contention]
    C --> D[Deadlocks and lock waits]

Where it shows up in production

The most common trigger is a high-concurrency OLTP workload on a table with missing secondary indexes. An application updates rows by an unindexed status column or timestamp range. Each UPDATE locks the entire table. Concurrent INSERTs queue. Threads pile up. InnoDB detects a deadlock and rolls back the cheaper transaction.

Queue-pattern tables are especially vulnerable. Multiple sessions poll for pending jobs with UPDATE ... WHERE status = 'pending' LIMIT 1. Without an index on status, every polling query locks the full table. Sessions that would have grabbed different rows instead contend for the same range locks.

Conditional locking also causes this. An application checks for a row before inserting it using SELECT ... FOR UPDATE. If the row does not exist, the session holds a gap lock on the missing value. A second session performing the same check locks the same gap. Neither can proceed to INSERT, and depending on timing they deadlock.

Schema migrations can accidentally introduce the pattern. Dropping an index that previously covered an UPDATE predicate, or changing a column type so that implicit conversion prevents index use, can turn a stable workload into a deadlock factory within minutes of deployment.

To diagnose, enable innodb_print_all_deadlocks so every cycle is written to the error log:

SET GLOBAL innodb_print_all_deadlocks = ON;

Warning: this increases error log volume on deadlock-heavy workloads. By default, SHOW ENGINE INNODB STATUS shows only the latest deadlock. With innodb_print_all_deadlocks, grep the error log for LATEST DETECTED DEADLOCK to find the dominant pattern instead of a single snapshot.

Check for long-running transactions that are holding locks:

SELECT trx_id, trx_mysql_thread_id, trx_state,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_seconds
FROM information_schema.innodb_trx
ORDER BY trx_started;

If a connection has been in RUNNING state for hundreds of seconds while holding locks, you can roll it back with KILL <trx_mysql_thread_id>. Warn the application owner first; rolling back a large write transaction can be expensive and may spike I/O.

Long-running transactions amplify the problem. Monitoring connections, ORM sessions, or backup tools that open a REPEATABLE READ transaction and then idle will hold next-key locks for extended periods. A single forgotten BEGIN in an interactive session can hold a wide read view and gap locks that block application writes.

Tradeoffs and when to use it

Switching to READ COMMITTED eliminates most gap locking. InnoDB locks only index records, not the gaps between them. This dramatically reduces deadlocks and allows higher concurrency for insert-heavy workloads.

READ COMMITTED changes MVCC semantics. A transaction can see rows committed by other transactions after its own start, and phantom rows can appear in repeated reads within the same transaction.

Be aware that locking reads and non-locking SELECTs inside the same REPEATABLE READ transaction see different data versions. The non-locking SELECT reads from the snapshot, while locking statements see the latest committed state. This inconsistency is legal but makes reasoning during an incident harder.

Under READ COMMITTED, if a row is already locked, InnoDB performs a semi-consistent read, returning the latest committed version so MySQL can evaluate the WHERE clause. An UPDATE may re-read and re-lock a row it previously skipped. Applications that rely on stable gap states, such as conditional inserts that assume no row can appear between two existing values, will see correctness changes.

Before changing the isolation level globally or per-session, audit whether application logic relies on gap stability. If code assumes that a gap between two values will remain empty for the duration of a transaction, READ COMMITTED is a breaking change. You are trading locking overhead for application-level consistency checks. If your code does not expect phantom rows, you may introduce subtle bugs that are harder to detect than deadlocks. Audit transaction boundaries before switching.

In many cases, the correct fix is to add a suitable index so the optimizer resolves the predicate with a small range scan, reducing lock scope from a table to a few rows.

If you switch to READ COMMITTED, row-based replication is required. Statement-based replication is unsafe at this isolation level, and the server may reject statements or switch logging format depending on your configuration.

Signals to watch in production

SignalWhy it mattersWarning sign
lock_deadlocks from INFORMATION_SCHEMA.INNODB_METRICSCumulative deadlocks detected by InnoDB. A sustained rate means lock scope is wider than the workload expects.Sustained rate above 1 per minute.
Innodb_row_lock_waitsCount of times transactions waited for a row lock. A rising rate indicates growing contention.Sustained increase over baseline.
Innodb_row_lock_time_avgAverage wait time for row locks. Long waits mean transactions hold locks for extended periods.Sustained average above 1000 ms.
Innodb_row_lock_current_waitsInstantaneous count of transactions waiting for row locks.Greater than 0 for more than 30 seconds.
Handler_read_rnd_nextIndicator of full table scans. High rate relative to point lookups suggests missing indexes that expand lock ranges.Step-change greater than 5x baseline correlated with slow queries.
Select_full_joinJoins executed without index. Can trigger scans that escalate locks across large tables.Nonzero sustained rate in OLTP.

How Netdata helps

  • Correlate lock_deadlocks rate with Threads_running and Questions rate to distinguish a lock storm from a traffic spike.
  • Watch Innodb_row_lock_waits and Innodb_row_lock_time_avg alongside query throughput to see contention building before the deadlock detector fires.
  • Track Handler_read_rnd_next and Select_full_join to flag queries that are likely expanding lock ranges through full scans.
  • Composite alert: rising deadlocks plus high Threads_running plus dropping Questions rate indicates a lock cascade in progress.
  • Per-second resolution on InnoDB lock metrics makes it easier to pinpoint which transaction pattern triggered a deadlock spike.