MySQL InnoDB Locking: Deadlocks, Gap Locks, and Next-Key Locks Explained

It was 11:47 PM on Black Friday when the on-call engineer's phone lit up. Orders were failing across the checkout service — not with a database connection error, not a timeout, but a stream of ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction. Two background workers were both processing order fulfillment queues, each locking the orders table in a slightly different row order. When traffic spiked, they collided thousands of times per minute, InnoDB's deadlock detector killed one transaction per collision, and the application's retry logic made it worse by re-entering the same pattern. The fix took twenty minutes once the cause was understood; the diagnosis took four hours because nobody on the team had a clear mental model of how InnoDB locking actually works.

InnoDB's locking system is one of the most powerful — and most misunderstood — parts of MySQL. The engine provides full ACID transactions with row-level locking, but the details of which locks are acquired, when they are acquired, and how long they are held depend on isolation level, index usage, and the exact form of the query. A SELECT ... FOR UPDATE on a range column in REPEATABLE READ isolation can silently block every INSERT in that range for the lifetime of the transaction.

This guide explains InnoDB's locking model from the ground up: what each lock type does, how gap locks and next-key locks interact with isolation levels, how to diagnose deadlocks from SHOW ENGINE INNODB STATUS and performance_schema, and the concrete patterns that prevent most locking problems before they reach production.

Background

MySQL's InnoDB storage engine implements multiversion concurrency control (MVCC) to allow readers and writers to coexist without blocking each other in most cases. Reads see a consistent snapshot of the data as it existed at the start of the transaction. Writes acquire locks to serialize concurrent modifications. The combination of MVCC and row-level locking is what allows InnoDB to support high-concurrency OLTP workloads that would cripple table-locking engines like MyISAM.

But MVCC does not eliminate all blocking. Writes still lock, and under the default REPEATABLE READ isolation level, InnoDB goes further than simple row locking: it protects the gaps between indexed values to prevent phantom reads. Understanding exactly when and why those locks are acquired is the foundation for avoiding the deadlocks and lock wait timeouts that plague many production MySQL deployments.

InnoDB always works with indexes when acquiring row-level locks. If a query does not use an index, InnoDB must scan every row in the table and locks all of them — including rows that do not match the query predicate. This is one of the most common sources of unexpected lock contention, and it is why EXPLAIN output belongs in every lock contention investigation.

InnoDB Lock Types

InnoDB implements several distinct lock types. Understanding each one is necessary for reading deadlock diagnostics and predicting which queries will block which other queries.

Record Locks

A record lock locks a single index record. When you execute SELECT id, amount FROM orders WHERE id = 1001 FOR UPDATE, InnoDB acquires an exclusive record lock on the index entry for id = 1001. No other transaction can modify that row until the lock is released.

-- Transaction A acquires an exclusive record lock on id = 1001
START TRANSACTION;
SELECT id, status, amount FROM orders WHERE id = 1001 FOR UPDATE;

-- Transaction B will BLOCK here until Transaction A commits or rolls back
-- ERROR 1205 (HY000): Lock wait timeout exceeded after innodb_lock_wait_timeout seconds
UPDATE orders SET status = 'shipped' WHERE id = 1001;

Record locks always lock an index record, never a physical row directly. If a table has no primary key and no unique index, InnoDB creates a hidden clustered index using an internal DB_ROW_ID column, and record locks are placed on that internal index.

Gap Locks

A gap lock locks the gap between two index values — the space where new rows could be inserted. Gap locks do not lock any existing row; they only prevent other transactions from inserting a row into the protected gap.

-- orders table has rows with id: 10, 20, 30, 50, 100
-- This query in REPEATABLE READ acquires gap locks on the range (20, 50)
-- to prevent phantoms -- no existing rows in that range are locked,
-- but no INSERT with id between 20 and 50 can succeed until this transaction ends.
START TRANSACTION;
SELECT * FROM orders WHERE id BETWEEN 25 AND 45 FOR UPDATE;
-- InnoDB acquires: gap lock on (20, 50), and record lock on any row in range

Next-Key Locks

A next-key lock is the combination of a record lock on an index record and a gap lock on the gap immediately before that record. InnoDB uses next-key locks as the default locking strategy under REPEATABLE READ when scanning index ranges.

For a table with index values (10, 20, 30, 50, 100), the next-key lock ranges are: (-inf, 10], (10, 20], (20, 30], (30, 50], (50, 100], (100, +inf). The square bracket indicates the record itself is locked; the parenthesis indicates the gap is locked but not including that boundary value.

-- With REPEATABLE READ (default), a range scan acquires next-key locks
-- on every index entry scanned, including the gap after the last scanned record.

START TRANSACTION;
-- Suppose orders has rows with order_date values: 2024-01-01, 2024-01-05, 2024-01-10
-- This query scans the range and acquires next-key locks on:
--   (2024-01-01, 2024-01-05] and (2024-01-05, 2024-01-10]
-- AND a gap lock on (2024-01-10, +inf) to prevent phantoms at the scan boundary
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-10'
FOR UPDATE;

Intention Locks

Intention locks are table-level locks that InnoDB uses to indicate that a transaction intends to acquire row-level locks within the table. There are two types: Intention Shared (IS) and Intention Exclusive (IX). Before acquiring a row-level shared lock, InnoDB acquires an IS lock on the table. Before acquiring a row-level exclusive lock, it acquires an IX lock.

Intention locks matter when operations compete for table-level locks. LOCK TABLES ... WRITE requires an exclusive table lock, which is blocked by any outstanding IS or IX intention lock. This is why a long-running row-level transaction can block ALTER TABLE, OPTIMIZE TABLE, and other DDL operations that need a table-exclusive lock.

Gap Locks and Next-Key Locks

REPEATABLE READ: Gap Locking is On

Under REPEATABLE READ (MySQL's default isolation level), InnoDB uses next-key locks for index range scans to prevent phantom reads. A phantom read occurs when a transaction re-reads a range and sees rows that were inserted by another committed transaction between the two reads. InnoDB prevents this by locking the gaps.

-- Verify current isolation level
SELECT @@transaction_isolation;
-- REPEATABLE-READ (default)

-- This UPDATE on a range will acquire next-key locks on the scanned range
-- in REPEATABLE READ, blocking INSERTs with status='pending' from other sessions
-- for the duration of the transaction.
START TRANSACTION;
UPDATE orders SET priority = 'high' WHERE status = 'pending' AND amount > 500;
-- InnoDB scans the (status, amount) index range and acquires next-key locks
-- on every entry in the scan. INSERTs of new pending orders with amount > 500
-- will BLOCK until this transaction commits.
COMMIT;

READ COMMITTED: Gap Locking is Off

Under READ COMMITTED, InnoDB releases record locks on rows that did not match the WHERE clause after the scan completes, and — critically — it does not acquire gap locks at all. This eliminates phantom-read protection but dramatically reduces the range of rows and gaps that any given query holds locks on.

-- Set isolation level for the current session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Now the same UPDATE acquires record locks ONLY on the rows it modifies.
-- No gap locks. INSERTs of new pending orders proceed without blocking.
-- Trade-off: a concurrent transaction can INSERT a matching row between
-- two reads within this transaction (phantom read risk).
START TRANSACTION;
UPDATE orders SET priority = 'high' WHERE status = 'pending' AND amount > 500;
COMMIT;

Unique Index Lookups: No Gap Lock

When a query uses a unique index with an equality predicate that matches exactly one row, InnoDB acquires only a record lock — no gap lock. This is an important optimization: primary key and unique index point lookups are the most concurrency-friendly write patterns.

-- Primary key lookup: record lock only, no gap lock
-- This is the most concurrency-friendly locking pattern
SELECT * FROM orders WHERE id = 1001 FOR UPDATE;

-- Non-unique index range: next-key locks on every scanned entry + gap
-- Much broader lock scope, even if only one row is returned
SELECT * FROM orders WHERE customer_id = 42 FOR UPDATE;

Detecting Deadlocks

A deadlock occurs when two or more transactions are each waiting for a lock held by the other, creating a cycle that cannot be resolved without aborting one of them. InnoDB detects these cycles automatically and rolls back the transaction with the smallest number of modified rows (the cheapest victim). The application receives ERROR 1213 (40001) and must retry the transaction.

SHOW ENGINE INNODB STATUS

The most detailed deadlock information is in the LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS. This section shows the last deadlock InnoDB detected, including which transactions were involved, which locks they held, and which lock each was waiting for.

SHOW ENGINE INNODB STATUS\G

Look for the LATEST DETECTED DEADLOCK section. A typical deadlock output looks like this:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-11-29 23:47:15 140234567890
*** (1) TRANSACTION:
TRANSACTION 421938, ACTIVE 0 sec starting index read
MySQL thread id 88, OS thread handle 140234567, query id 12041 localhost app
UPDATE orders SET status = 'processing' WHERE id = 1001

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 38 page no 4 n bits 72 index PRIMARY of table `shop`.`orders`
trx id 421938 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: id=1001

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 4 n bits 72 index PRIMARY of table `shop`.`order_items`
trx id 421938 lock_mode X locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: order_id=1001, item_id=55

*** (2) TRANSACTION:
TRANSACTION 421939, ACTIVE 0 sec starting index read
UPDATE order_items SET quantity = 2 WHERE order_id = 1001 AND item_id = 55

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 38 page no 4 n bits 72 index PRIMARY of table `shop`.`order_items`
trx id 421939 lock_mode X locks rec but not gap
Record lock, heap no 9 PHYSICAL RECORD: order_id=1001, item_id=55

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 4 n bits 72 index PRIMARY of table `shop`.`orders`
trx id 421939 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: id=1001

*** WE ROLL BACK TRANSACTION (2)

Read this output from the bottom up: Transaction 2 holds a lock on order_items and is waiting for a lock on orders. Transaction 1 holds the lock on orders that Transaction 2 needs, and is waiting for the lock on order_items that Transaction 2 holds. Classic cycle. InnoDB rolled back Transaction 2 (fewer rows modified).

performance_schema: data_locks and data_lock_waits (MySQL 8.0)

MySQL 8.0 replaces the deprecated INFORMATION_SCHEMA.INNODB_LOCKS and INNODB_LOCK_WAITS tables with performance_schema.data_locks and performance_schema.data_lock_waits. These tables show live lock state — not just the last deadlock, but every lock currently held or waited on across all active transactions.

-- Show all currently held and pending locks (MySQL 8.0+)
SELECT
    dl.ENGINE_TRANSACTION_ID   AS trx_id,
    dl.THREAD_ID,
    dl.OBJECT_SCHEMA,
    dl.OBJECT_NAME             AS table_name,
    dl.INDEX_NAME,
    dl.LOCK_TYPE,              -- TABLE or RECORD
    dl.LOCK_MODE,              -- X, S, IX, IS, X,GAP, X,REC_NOT_GAP, etc.
    dl.LOCK_STATUS,            -- GRANTED or WAITING
    dl.LOCK_DATA               -- locked record value or gap boundary
FROM performance_schema.data_locks dl
ORDER BY dl.ENGINE_TRANSACTION_ID, dl.LOCK_STATUS DESC;

-- Show which transaction is blocking which (the lock wait graph)
SELECT
    r.REQUESTING_ENGINE_TRANSACTION_ID  AS waiting_trx,
    r.REQUESTING_THREAD_ID              AS waiting_thread,
    b.BLOCKING_ENGINE_TRANSACTION_ID    AS blocking_trx,
    b.BLOCKING_THREAD_ID                AS blocking_thread,
    dl.OBJECT_NAME                      AS table_name,
    dl.INDEX_NAME,
    dl.LOCK_MODE,
    dl.LOCK_DATA
FROM performance_schema.data_lock_waits r
JOIN performance_schema.data_lock_waits b
    ON r.REQUESTING_ENGINE_TRANSACTION_ID = b.REQUESTING_ENGINE_TRANSACTION_ID
JOIN performance_schema.data_locks dl
    ON dl.ENGINE_TRANSACTION_ID = b.BLOCKING_ENGINE_TRANSACTION_ID
   AND dl.LOCK_STATUS = 'GRANTED';

innodb_deadlock_detect and innodb_lock_wait_timeout

-- Show current deadlock detection and timeout settings
SELECT @@innodb_deadlock_detect, @@innodb_lock_wait_timeout;
-- 1 (enabled), 50 (seconds)

-- innodb_deadlock_detect: ON by default.
-- Disable only on very high-concurrency systems where the detection overhead
-- itself becomes a bottleneck. When disabled, deadlocks are resolved only
-- by innodb_lock_wait_timeout expiry.
SET GLOBAL innodb_deadlock_detect = ON;

-- innodb_lock_wait_timeout: seconds a transaction waits for a lock before
-- rolling back the waiting statement (not the whole transaction).
-- Default is 50. Lower values (5-15) fail faster and reduce queue buildup
-- under lock contention. Set in my.cnf for persistence.
SET GLOBAL innodb_lock_wait_timeout = 10;

Preventing Deadlocks

Deadlocks cannot be fully eliminated, but they can be made rare. The following patterns address the root causes of most deadlocks encountered in production MySQL systems.

Consistent Lock Acquisition Order

The most common deadlock pattern is two transactions acquiring the same set of locks in opposite orders. Transaction A locks Table X then Table Y; Transaction B locks Table Y then Table X. The fix is to enforce a consistent ordering across all code paths.

-- WRONG: These two transactions will deadlock under concurrent load.
-- Transaction A:
START TRANSACTION;
UPDATE orders SET status = 'processing' WHERE id = 1001;      -- locks orders row 1001
UPDATE order_items SET reserved = 1 WHERE order_id = 1001;   -- then locks order_items
COMMIT;

-- Transaction B (concurrent):
START TRANSACTION;
UPDATE order_items SET reserved = 0 WHERE order_id = 1001;   -- locks order_items first
UPDATE orders SET status = 'cancelled' WHERE id = 1001;      -- then waits for orders
COMMIT;

-- CORRECT: Both transactions lock orders first, then order_items.
-- Transaction A:
START TRANSACTION;
SELECT id FROM orders WHERE id = 1001 FOR UPDATE;            -- lock orders first
UPDATE order_items SET reserved = 1 WHERE order_id = 1001;
UPDATE orders SET status = 'processing' WHERE id = 1001;
COMMIT;

-- Transaction B:
START TRANSACTION;
SELECT id FROM orders WHERE id = 1001 FOR UPDATE;            -- lock orders first
UPDATE order_items SET reserved = 0 WHERE order_id = 1001;
UPDATE orders SET status = 'cancelled' WHERE id = 1001;
COMMIT;

Keep Transactions Short

Every lock acquired within a transaction is held until the transaction commits or rolls back. A transaction that holds a lock for 30 seconds while waiting for an external API call is 30 seconds of blocked concurrent writes. Move all non-database work outside transaction boundaries.

-- WRONG: Lock held while application code runs (payment API call, etc.)
START TRANSACTION;
SELECT * FROM orders WHERE id = 1001 FOR UPDATE;
-- Application code: call payment API, validate response, send email...
-- Lock on orders row 1001 held for entire duration (seconds to minutes)
UPDATE orders SET status = 'paid' WHERE id = 1001;
COMMIT;

-- CORRECT: Do application work first, then open the transaction for the
-- minimum time needed to read-verify-write.
-- 1. Fetch order data (no lock needed for read)
SELECT * FROM orders WHERE id = 1001;
-- 2. Call payment API, validate response...
-- 3. Short transaction: re-verify state and write
START TRANSACTION;
SELECT id, status FROM orders WHERE id = 1001 FOR UPDATE;
-- Verify status is still what we expect, then update
UPDATE orders SET status = 'paid' WHERE id = 1001;
COMMIT;

SELECT ... FOR UPDATE vs SELECT ... FOR SHARE

SELECT ... FOR UPDATE acquires an exclusive lock (X lock) that blocks both readers using FOR SHARE and writers. SELECT ... FOR SHARE acquires a shared lock (S lock) that blocks writers but allows other FOR SHARE readers. Use the weakest lock that satisfies the requirement.

-- FOR SHARE: allows concurrent reads with FOR SHARE, blocks writes
-- Use when: you need to read a row and ensure it is not modified,
-- but you will NOT modify it yourself in this transaction.
START TRANSACTION;
SELECT balance FROM accounts WHERE id = $account_id FOR SHARE;
-- Validate balance is sufficient, then proceed...
-- If you will UPDATE in the same transaction, use FOR UPDATE instead,
-- or you risk a deadlock when two FOR SHARE holders both try to upgrade.
COMMIT;

-- FOR UPDATE: exclusive lock, blocks all concurrent access to the locked rows
-- Use when: you will write to the row in this transaction.
START TRANSACTION;
SELECT balance FROM accounts WHERE id = $account_id FOR UPDATE;
UPDATE accounts SET balance = balance - $amount WHERE id = $account_id;
COMMIT;

Minimize Rows Locked by Range Queries

-- Ensure range queries use the most selective index available.
-- This query without an index on (status, created_at) will full-scan and lock
-- every row in the table.
EXPLAIN SELECT * FROM orders
WHERE status = 'pending' AND created_at > NOW() - INTERVAL 1 HOUR
FOR UPDATE;

-- Add a composite index to minimize the locked range
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- Under READ COMMITTED, range locks do not include gap locks.
-- Under REPEATABLE READ, consider rewriting range queries as IN() with
-- known IDs to acquire only record locks, not gap locks.
-- First fetch IDs:
SELECT id FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL 1 HOUR;
-- Then lock only the specific rows:
SELECT * FROM orders WHERE id IN (1001, 1002, 1007, 1042) FOR UPDATE;

Key Takeaways

Working with JusDB on MySQL

InnoDB locking problems — deadlocks, lock wait timeouts, and the silent insert blocking caused by gap locks — are among the most operationally damaging issues in production MySQL systems, and also among the hardest to diagnose without deep engine knowledge. The root cause is almost always visible in SHOW ENGINE INNODB STATUS or performance_schema, but knowing what to look for and how to restructure the offending transactions requires experience across many different production workloads.

JusDB's MySQL consultants have resolved deadlock clusters in high-traffic checkout systems, eliminated lock contention in multi-tenant SaaS platforms, and redesigned transaction boundaries that were holding locks across external API calls. We work from diagnosis through implementation: identifying the exact lock pattern from engine diagnostics, recommending the correct isolation level for each workload, restructuring transactions for minimum lock scope, and validating the fix under load before it reaches production.

MySQL Consulting Services Talk to a MySQL Expert

We also offer a MySQL locking and transaction health review specifically for teams experiencing recurring deadlocks or lock wait timeouts — reviewing isolation level configuration, transaction structure, index coverage, and monitoring setup before the next incident, not after.

Related Reading

• MySQL EXPLAIN and Query Optimization — understand index usage and scan type before analyzing lock scope
• Understanding InnoDB Architecture: Performance, Reliability, and Internals — buffer pool, redo log, and the storage foundations that underpin locking behavior
• MySQL Architecture Deep Dive: From Query Execution to Physical Storage — how the query execution pipeline interacts with the InnoDB lock manager