MySQL Gap Locks and Insert Deadlocks

Scenario

Example app: a loyalty service on MySQL 8 (Repeatable Read). On each purchase it gets or creates a points row for the customer and campaign, then adds points. Campaign launches bring bursts of first purchases.

What you see in production

During launches, ERROR 1213 (40001): Deadlock found when trying to get lock appears on the very first purchase for many customers — never on later ones. The code locks the row "to be safe" before inserting.

The naive code

BEGIN;
SELECT * FROM points WHERE customer_id = 42 AND campaign_id = 7 FOR UPDATE;   -- no row yet
-- application: not found → insert
INSERT INTO points (customer_id, campaign_id, balance) VALUES (42, 7, 10);
COMMIT;

DB::transaction(function () use ($customerId, $campaignId, $earned) {
    $row = Points::where(compact('customerId', 'campaignId'))->lockForUpdate()->first();
    if (! $row) {
        Points::create(['customer_id' => $customerId, 'campaign_id' => $campaignId, 'balance' => $earned]);
    } else {
        $row->increment('balance', $earned);
    }
});

@Transactional
public void earn(long customerId, long campaignId, int points) {
    Points p = repo.findForUpdate(customerId, campaignId).orElse(null);   // FOR UPDATE on a missing row
    if (p == null) repo.save(new Points(customerId, campaignId, points)); else p.add(points);
}

Reproduce it

Two first purchases for nearby keys run concurrently. Gap locks are compatible with each other, but an insert into a gap needs an insert intention lock that conflicts with any gap lock held by another transaction:

gap_locks = {}          # gap -> set of transactions holding a gap lock on it
waiting = {}

def select_for_update_missing(tx, gap):
    gap_locks.setdefault(gap, set()).add(tx)          # gap locks don't conflict with each other
    print(f"{tx}: SELECT … FOR UPDATE found nothing -> gap lock on {gap}")

def insert(tx, gap):
    others = gap_locks.get(gap, set()) - {tx}
    if others:
        blocker = next(iter(others))
        waiting[tx] = blocker
        print(f"{tx}: INSERT needs insert-intention lock on {gap}, blocked by {blocker}'s gap lock")
        if waiting.get(blocker) == tx:
            print(f"deadlock: {tx} <-> {blocker}; InnoDB rolls back {tx} (ERROR 1213)")
    else:
        print(f"{tx}: inserted")

gap = "(customer 40 .. customer 50) on idx(customer_id, campaign_id)"
select_for_update_missing("T1", gap)
select_for_update_missing("T2", gap)
insert("T1", gap)
insert("T2", gap)

T1: SELECT … FOR UPDATE found nothing -> gap lock on (customer 40 .. customer 50) on idx(customer_id, campaign_id)
T2: SELECT … FOR UPDATE found nothing -> gap lock on (customer 40 .. customer 50) on idx(customer_id, campaign_id)
T1: INSERT needs insert-intention lock on (customer 40 .. customer 50) on idx(customer_id, campaign_id), blocked by T2's gap lock
T2: INSERT needs insert-intention lock on (customer 40 .. customer 50) on idx(customer_id, campaign_id), blocked by T1's gap lock
deadlock: T2 <-> T1; InnoDB rolls back T2 (ERROR 1213)

Root cause

Under Repeatable Read, InnoDB's locking reads take next-key / gap locks on the index range they search, so that no one can insert a phantom into it. A FOR UPDATE that finds nothing still locks the gap where the row would go. Two transactions can both hold that gap lock; each then tries to insert, needs an insert-intention lock that conflicts with the other's gap lock, and they deadlock.

The fix

Make the first write a single atomic statement — no lock-then-insert:

INSERT INTO points (customer_id, campaign_id, balance) VALUES (42, 7, 10) AS new
ON DUPLICATE KEY UPDATE balance = points.balance + new.balance;    -- needs UNIQUE(customer_id, campaign_id)

DB::statement(
    'INSERT INTO points (customer_id, campaign_id, balance) VALUES (?, ?, ?) AS new
     ON DUPLICATE KEY UPDATE balance = points.balance + new.balance',
    [$customerId, $campaignId, $earned]
);

@Modifying
@Query(value = "INSERT INTO points (customer_id, campaign_id, balance) VALUES (:c, :k, :p) AS new "
    + "ON DUPLICATE KEY UPDATE balance = points.balance + new.balance", nativeQuery = true)
void earn(@Param("c") long customerId, @Param("k") long campaignId, @Param("p") int points);

from sqlalchemy.dialects.mysql import insert
stmt = insert(Points).values(customer_id=cid, campaign_id=kid, balance=earned)
session.execute(stmt.on_duplicate_key_update(balance=Points.balance + stmt.inserted.balance))

Other options: create the row ahead of time (when the customer joins the campaign) so later code only updates; or use Read Committed for this workload, where InnoDB disables most gap locking for searches (it still uses them for foreign-key and duplicate-key checks).

Trade-offs

• Upserts on InnoDB still take locks on the unique index and can deadlock under extreme concurrency on the same key; a retry handles the remainder.
• Switching to Read Committed reduces gap locks but changes read behaviour for the whole session — evaluate per workload, not globally on a hunch.
• Ensure the lookup uses the unique index: an exact match on a unique key locks only that record, while a non-unique or missing index widens the locked range.

Detect it in production

• SHOW ENGINE INNODB STATUS deadlocks mentioning locks gap before rec and insert intention waiting are the signature of this pattern.
• performance_schema.data_locks shows LOCK_MODE values such as X,GAP and X,INSERT_INTENTION while it happens.
• Deadlocks concentrated on "first time" events (first purchase, first login of the day) point here.

Key points

• In InnoDB Repeatable Read, a locking read that finds nothing locks the gap.
• Two gap locks are compatible, but each blocks the other's insert → deadlock.
• Use a single upsert with a unique key, pre-create rows, or use Read Committed for that workload.