MySQL InnoDB REPEATABLE READ: Next-Key Locking Architecture and Operational Boundaries

Requirements Context

Application developers choose REPEATABLE READ because it prevents phantom reads—rows appearing or disappearing between reads in the same transaction—without the throughput cost of SERIALIZABLE. InnoDB implements this guarantee through next-key locking, a mechanism that combines record locks on index entries with gap locks on the intervals between them. Understanding where this mechanism applies, where it degrades, and how to verify its behavior is essential for correct concurrent write paths.

Smallest Suitable Design: How Next-Key Locking Works

When a transaction runs a range scan under REPEATABLE READ (e.g., SELECT * FROM orders WHERE customer_id = 42 FOR UPDATE), InnoDB places next-key locks on every index record it encounters and on the gap before the first record and after the last record in the scan range. A next-key lock is effectively a record lock plus a gap lock on the preceding interval. This prevents other transactions from inserting new rows that would satisfy the same predicate.

The lock scope depends entirely on the index structure:

• Primary key present: Next-key locking degenerates to locking only the primary key values returned by the scan. Gap locks are not needed because the primary key uniquely identifies each row; phantoms cannot appear between distinct primary key values.
• No primary key, secondary unique index used: Next-key locks apply to the unique index records and their gaps. Insertions that would create duplicate unique keys are blocked immediately by the unique constraint check, not by gap locks.
• No primary key, no unique index: InnoDB creates a hidden 6-byte row ID and clusters on it. Range scans on non-unique columns can escalate to table-level gap locks, severely limiting concurrency.

Trust and Data Boundaries

REPEATABLE READ guarantees that a transaction sees a consistent snapshot for read operations. It does not serialize write-write conflicts. Two concurrent transactions can both read the same snapshot, then attempt conflicting updates. The second to commit will fail with a lock wait timeout or deadlock, not a serialization error.

This boundary matters in sharded or replicated setups. If application logic assumes REPEATABLE READ provides serializable semantics (e.g., "check-then-insert" patterns without unique constraints), write skew anomalies can occur. The isolation level prevents phantoms for the querying transaction only; it does not coordinate concurrent writers.

Operational Checks

Confirm Isolation Level

SELECT @@transaction_isolation;

Run in the application's connection pool initialization or a test harness. Expected result: REPEATABLE-READ. If the session variable differs, check @@global.transaction_isolation and the --transaction-isolation server startup option.

Observe Lock Types Under Load

SHOW ENGINE INNODB STATUS\G

Execute while a concurrent workload runs. In the TRANSACTIONS section, look for lock lines such as:

RECORD LOCKS space id 42 page no 3 n bits 72 index PRIMARY of table `test`.`orders`
LOCK MODE X locks rec but not gap

locks rec but not gap indicates primary key value locking (degenerate next-key). locks gap before rec or locks gap before rec insert intention indicates true gap locking. Compare tables with and without primary keys to see the difference.

Duplicate-Key Insert Test

Create two sessions. In session A:

BEGIN;
INSERT INTO users (email) VALUES ('[contact removed]');
-- do not commit

In session B, attempt the same insert:

INSERT INTO users (email) VALUES ('[contact removed]');

Session B blocks on a record lock (not a gap lock) because the unique index on email triggers an immediate duplicate-key check. Session B will eventually timeout or deadlock. This behavior is identical with or without a primary key, provided a unique index exists on the inserted column.

Failure Modes

Missing Primary Key + High-Concurrency Inserts

A table without a primary key and without a unique index on the insertion column forces InnoDB to use the hidden row ID. Concurrent INSERT statements that do not specify the row ID contend on the same gap at the end of the clustered index, serializing throughput. Symptoms: high Innodb_row_lock_waits, increasing Innodb_row_lock_time_avg, and transaction timeouts under moderate load.

sql_require_primary_key Enabled on Legacy Schemas

Enabling sql_require_primary_key=ON (default in MySQL 8.0.13+) prevents creation of tables without explicit primary keys. Existing tables without primary keys continue to operate but may exhibit the locking behavior above. Applications that relied on implicit row ID generation for partitioning or sharding logic may break if they assume stable row IDs across restarts (the hidden row ID is not persistent).

Misinterpreting REPEATABLE READ as Serializable

Consider a "reserve inventory" pattern:

BEGIN;
SELECT quantity FROM inventory WHERE sku = 'ABC' FOR UPDATE;
-- application logic checks quantity > 0
UPDATE inventory SET quantity = quantity - 1 WHERE sku = 'ABC';
COMMIT;

Under REPEATABLE READ, two transactions can both read quantity = 1, both pass the check, and both decrement to 0 and -1 respectively. The second commit succeeds because the FOR UPDATE lock is on the primary key record, not a predicate lock. A unique constraint on quantity >= 0 cannot be expressed in MySQL. The fix is application-level retry logic or moving the check into a stored procedure that runs under a single statement.

Conditions That Would Change the Design

• Write-heavy workloads with frequent range scans: Consider READ COMMITTED with binlog_format=ROW. Gap locks are disabled, reducing lock contention. Phantom reads become possible but may be acceptable if the application re-validates before commit.
• Sharded writes with cross-shard transactions: REPEATABLE READ does not coordinate across shards. Use a saga pattern or two-phase commit with an external coordinator.
• Migration to MySQL 8.0+ with sql_require_primary_key: Audit all tables for missing primary keys before enabling. Add explicit primary keys (even auto-increment surrogate keys) to restore degenerate next-key locking behavior.
• High contention on unique secondary indexes: If duplicate-key errors are frequent, consider application-level idempotency keys or INSERT ... ON DUPLICATE KEY UPDATE to convert lock waits into immediate resolution.

Practical Verification Checklist

1. Run SELECT @@transaction_isolation; on a pooled connection; verify REPEATABLE-READ.
2. For each hot table, confirm a primary key exists: SHOW CREATE TABLE table_name; and look for PRIMARY KEY.
3. Under representative load, capture SHOW ENGINE INNODB STATUS and verify lock lines show locks rec but not gap on primary key indexes.
4. Monitor Innodb_row_lock_waits and Innodb_row_lock_time_avg via SHOW GLOBAL STATUS; sustained increases indicate gap-lock contention.
5. Test duplicate-key insert behavior in staging with concurrent sessions; confirm errors are immediate unique constraint violations, not lock timeouts.

Limitations

This analysis covers InnoDB's default behavior on MySQL 5.7 and 8.0. Percona Server, MariaDB, and MySQL HeatWave may implement different lock optimizations (e.g., MariaDB's innodb_lock_schedule_algorithm). External lock managers (e.g., Vitess, ProxySQL) can override or bypass InnoDB locking entirely. Always verify against the specific deployment topology.