Skip to main content
← PROBLEMS / ARCHITECTURE
An end-to-end domain learning journey

Eliminating MySQL InnoDB
Gap-Lock Deadlocks.

Your payment service runs on MySQL 8.0 (REPEATABLE READ isolation). A background worker polls SELECT id FROM ledger_entries WHERE status = 'PENDING' AND account_id = 42 FOR UPDATE, and a cleanup job runs DELETE FROM ledger_entries WHERE created_at < NOW() - INTERVAL 90 DAY using a non-unique idx_created_at index. Concurrent INSERT INTO ledger_entries transactions for completely different accounts block for 50s (Lock wait timeout exceeded) and fail with ER_LOCK_DEADLOCK (1213). Fix the locking behavior without losing data integrity.
Start the journey5 stages
Loading diagram…
Eliminating MySQL InnoDB Gap-Lock Deadlocks architecture sketch diagram
FIRST PRINCIPLES. PRODUCTION SCALE.
The idea to keep with you

Your payment service runs on MySQL 8.0 (`REPEATABLE READ` isolation). A background worker polls `SELECT id FROM ledger_entries WHERE status = 'PENDING' AND account_id = 42 FOR UPDATE`, and a cleanup job runs `DELETE FROM ledger_entries WHERE created_at < NOW() - INTERVAL 90 DAY` using a non-unique `idx_created_at` index. Concurrent `INSERT INTO ledger_entries` transactions for completely different accounts block for 50s (`Lock wait timeout exceeded`) and fail with `ER_LOCK_DEADLOCK` (1213). Fix the locking behavior without losing data integrity.

From patterns to decisions.

Your payment service runs on MySQL 8.0 (`REPEATABLE READ` isolation). A background worker polls `SELECT id FROM ledger_entries WHERE status = 'PENDING' AND account_id = 42 FOR UPDATE`, and a cleanup job runs `DELETE FROM ledger_entries WHERE created_at < NOW() - INTERVAL 90 DAY` using a non-unique `idx_created_at` index. Concurrent `INSERT INTO ledger_entries` transactions for completely different accounts block for 50s (`Lock wait timeout exceeded`) and fail with `ER_LOCK_DEADLOCK` (1213). Fix the locking behavior without losing data integrity.

01
ARCHITECTURE · STAGE 01L4 · FUNDAMENTALS

Under REPEATABLE READ, InnoDB locks.

Under REPEATABLE READ, InnoDB locks the gaps around scanned index entries (and the supremum gap if no rows match) to prevent phantom reads.

ChoiceWhat it buysWhat it costs
factorperformance_schema.data_locks; pro: Pinpoints exact gap vs record lock conflicts in real time.; con: Only shows currently blocked waits, not historical deadlocks.

Step 1

Inspect SHOW ENGINE INNODB STATUS -> LATEST DETECTED DEADLOCK for lock_mode X locks gap before rec insert intention.

Step 2

Confirm transaction_isolation = REPEATABLE-READ.

Alternative 1

Enable innodb_print_all_deadlocks = ON to log all deadlocks to the MySQL error log.

name

Supremum Lock Contention; description: Empty range scans lock the tail of the index.; mitigation: Switch to READ COMMITTED.

-- Inspect active InnoDB lock waits
SELECT r.trx_id waiting_trx, r.trx_mysql_thread_id waiting_thread,
       b.trx_id blocking_trx, l.lock_mode, l.lock_type, l.lock_data
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.innodb_trx b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
JOIN performance_schema.data_locks l ON l.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID;
02
ARCHITECTURE · STAGE 02L5 · APPLIED DESIGN

Combine READ COMMITTED isolation, FOR.

Combine READ COMMITTED isolation, FOR UPDATE SKIP LOCKED, and PK-chunked deletes with Spring Retry.

ChoiceWhat it buysWhat it costs
factorREAD_COMMITTED per transaction vs global; pro: Minimizes blast radius for legacy code that might rely on repeatable snapshots.; con: Must be applied consistently on all writing/locking services.

Step 1

Set @Transactional(isolation = Isolation.READ_COMMITTED).

Step 2

Use FOR UPDATE SKIP LOCKED in the worker query.

Step 3

Add @Retryable for CannotAcquireLockException.

Alternative 1

Move queue polling out of MySQL into Kafka or Redis Streams.

name

Missing Index on (status, account_id, id); description: Without a composite index, MySQL still scans many rows and acquires unnecessary record locks.; mitigation: Create INDEX idx_status_acct (status, account_id, id).

@Retryable(retryFor = CannotAcquireLockException.class, maxAttempts = 4,
           backoff = @Backoff(delay = 25, maxDelay = 200, multiplier = 2, random = true))
@Transactional(isolation = Isolation.READ_COMMITTED)
public List<Long> claimPendingBatch(long accountId, int limit) {
  return jdbc.queryForList(
    "SELECT id FROM ledger_entries WHERE status = 'PENDING' AND account_id = ? ORDER BY id LIMIT ? FOR UPDATE SKIP LOCKED",
    Long.class, accountId, limit);
}
03
ARCHITECTURE · STAGE 03L6 · SCALE & TRADE-OFFS

Bounding by primary key (id.

Bounding by primary key (id BETWEEN low AND high) restricts locks strictly to old historical IDs far away from the auto-increment insert tail.

ChoiceWhat it buysWhat it costs
factorPK-Chunked Delete; pro: Zero overlap with live `INSERT`s at the table tail.; con: Requires a small driver script or stored procedure.

Step 1

Query SELECT MIN(id), MAX(id) for rows older than 90 days.

Step 2

Loop in 1,000-ID increments.

Alternative 1

Use MySQL PARTITION BY RANGE (by month) and DROP PARTITION in <10ms.

name

Replication Lag from Huge Deletes; description: Deleting 1M rows in one transaction stalls replicas.; mitigation: Sleep 50ms between 1,000-row chunks.

-- Safe PK-chunked purge (run in loop incrementing @cur_id by 1000)
DELETE FROM ledger_entries
WHERE id BETWEEN @cur_id AND @cur_id + 999
  AND created_at < NOW() - INTERVAL 90 DAY;
04
ARCHITECTURE · STAGE 04L6 · SCALE & TRADE-OFFS

Switch to time-based range partitioning.

Switch to time-based range partitioning (PARTITION BY RANGE (TO_DAYS(created_at))) so purging 90-day data becomes an instant ALTER TABLE ... DROP PARTITION metadata operation.

ChoiceWhat it buysWhat it costs
factorRange Partitioning vs Chunked Delete; pro: Zero undo log bloat, zero B-tree fragmentation, instant space reclamation.; con: Every unique key must include the partitioning column `created_at`.

Step 1

Include created_at in the primary key (id, created_at) as required by MySQL partitioning.

Step 2

Automate monthly/weekly partition creation and DROP PARTITION.

Alternative 1

Archive old rows to S3/Parquet and truncate.

name

DROP PARTITION Still Needs MDL; description: Dropping a partition acquires an exclusive Metadata Lock.; mitigation: Set lock_wait_timeout = 3 before DROP PARTITION.

-- Instant metadata-only purge via partitioning
ALTER TABLE ledger_entries DROP PARTITION p_2026_07;
05
ARCHITECTURE · STAGE 05L7 · STAFF / PRINCIPAL

Audit transactions that issue multiple.

Audit transactions that issue multiple SELECTs expecting a frozen snapshot; those specific read-only reports can explicitly run START TRANSACTION WITH CONSISTENT SNAPSHOT or use REPEATABLE READ on a replica.

ChoiceWhat it buysWhat it costs
factorPer-Session Isolation; pro: OLTP gets lock-free READ COMMITTED while batch exports keep snapshot consistency.; con: Requires explicit session config on batch jobs.

Step 1

Identify multi-statement financial reconciliation jobs.

Step 2

Keep reporting/export sessions on REPEATABLE READ.

Alternative 1

Run consistent-snapshot exports on a dedicated read replica.

name

Long Snapshot Bloating Undo Logs; description: A 2-hour REPEATABLE READ export prevents InnoDB purge of undo history.; mitigation: Run long exports on an isolated replica.

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;

Theory & Tips and Tricks

Practical heuristics & interview traps

  • 01Interview anchor: Under MySQL's default REPEATABLE READ, SELECT ... FOR UPDATE or DELETE on a non-unique index locks the gaps between records—including the open-ended supremum—blocking unrelated INSERTs.
  • 02Trade-off tip: factor: PK-Chunked Delete; pro: Zero overlap with live INSERTs at the table tail.; con: Requires a small driver script or stored procedure.
  • 03Watch out for: name: DROP PARTITION Still Needs MDL; description: Dropping a partition acquires an exclusive Metadata Lock.; mitigation: Set lock_wait_timeout = 3 before DROP PARTITION.
Behavioral Progression · Mysql Innodb Gap Lock Deadlocks

Mysql Innodb Gap Lock Deadlocks — Normal → Load / Fault → Recovery

Send Request
1. Normal Request Ingress
Traffic: 1x baseline · Latency (p99): 4 ms
Process State
2. Core Execution & State Lookup
Traffic: 1x baseline · Latency (p99): 9 ms
Inject 10x Spike
3. Scale Spike / Failure Injection
Traffic: 10x burst · Latency (p99): 185 ms

Under MySQL's default REPEATABLE READ, SELECT ... FOR UPDATE or DELETE on a non-unique index locks the gaps between records—including the open-ended supremum—blocking unrelated INSERTs.

Synthesis / One connected design

Putting Eliminating MySQL InnoDB Gap-Lock Deadlocks Together

Under MySQL's default `REPEATABLE READ`, `SELECT ... FOR UPDATE` or `DELETE` on a non-unique index locks the gaps between records—including the open-ended supremum—blocking unrelated `INSERT`s.

A candidate architecture, not a universal answer

Putting Eliminating MySQL InnoDB Gap-Lock Deadlocks Together

Step 1
Step 2
Step 3
  1. Set @Transactional(isolation = Isolation.READ_COMMITTED).
  2. Use FOR UPDATE SKIP LOCKED in the worker query.
  3. Add @Retryable for CannotAcquireLockException.
Switch to time-based range partitioning (`PARTITION BY RANGE (TO_DAYS(created_at))`) so purging 90-day data becomes an instant `ALTER TABLE ... DROP PARTITION` metadata operation.
Interview Readiness Check

Switch to time-based range partitioning (PARTITION BY RANGE (TO_DAYS(created_at))) so purging 90-day data becomes an instant ALTER TABLE ... DROP PARTITION metadata operation.

Walk through bottlenecks, back-of-the-envelope math, and failure recovery.

Practice / Explain the consequences

Make the trade-off yourself.

Practice Check

Test your understanding

Question 1 of 3

When is ACID Transaction Fundamentals the right default for a production team?

Key takeaway

Eliminating MySQL InnoDB Gap-Lock Deadlocks

Under MySQL's default REPEATABLE READ, SELECT ... FOR UPDATE or DELETE on a non-unique index locks the gaps between records—including the open-ended supremum—blocking unrelated INSERTs.