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.
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.
| Choice | What it buys | What it costs |
|---|---|---|
| factor | performance_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;
Combine READ COMMITTED isolation, FOR.
Combine READ COMMITTED isolation, FOR UPDATE SKIP LOCKED, and PK-chunked deletes with Spring Retry.
| Choice | What it buys | What it costs |
|---|---|---|
| factor | READ_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);
}
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.
| Choice | What it buys | What it costs |
|---|---|---|
| factor | PK-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;
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.
| Choice | What it buys | What it costs |
|---|---|---|
| factor | Range 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;
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.
| Choice | What it buys | What it costs |
|---|---|---|
| factor | Per-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 UPDATEorDELETEon a non-unique index locks the gaps between records—including the open-ended supremum—blocking unrelatedINSERTs. - 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 = 3beforeDROP PARTITION.
Mysql Innodb Gap Lock Deadlocks — Normal → Load / Fault → Recovery
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.
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.
Putting Eliminating MySQL InnoDB Gap-Lock Deadlocks Together
- Set
@Transactional(isolation = Isolation.READ_COMMITTED). - Use
FOR UPDATE SKIP LOCKEDin the worker query. - Add
@RetryableforCannotAcquireLockException.
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.
Make the trade-off yourself.
Practice Check
Test your understanding
When is ACID Transaction Fundamentals the right default for a production team?
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.
