DEV Community

Harry Phung
Harry Phung

Posted on Originally published at blog.quangpv.top on

Deep Dive: Why SKIP LOCKED Requires READ COMMITTED in MySQL and PostgreSQL

#chores #database

Common Concurrency Interview Question: Handling High-Traffic Purchases and Key Approaches

In technical and system design interviews, one of the most frequently asked questions is: “How do you handle thousands of users attempting to purchase the same product at the exact same second without overselling or crashing the database?” This article walks through the common approaches to this problem—including patterns

Deep Dive: Why SKIP LOCKED Requires READ COMMITTED in MySQL and PostgreSQL
](https://dev.to/quangpv/common-concurrency-interview-question-handling-high-traffic-purchases-and-key-approaches-1d7i-temp-slug-5727368)

Deep Dive: Why SKIP LOCKED Requires READ COMMITTED in MySQL and PostgreSQL

In our previous article, we discussed how the Reservation Pool pattern eliminates hot-row contention by allowing concurrent transactions to bypass locked records.

However, when engineers deploy SKIP LOCKED to production or run their first high-concurrency load test, they frequently encounter an unexpected surprise:

  • In MySQL: Transactions crash with Deadlock found when trying to get lock.
  • In PostgreSQL: Transactions abort with ERROR: could not serialize access due to concurrent update.

Wasn't SKIP LOCKED designed specifically to eliminate lock waiting? Why would it trigger deadlocks or serialization errors?

The issue is not SKIP LOCKED itself. The culprit is the database engine's Transaction Isolation Level —specifically, running under the default REPEATABLE READ. This deep dive examines the internal database engine mechanics behind both engines and explains why switching to READ COMMITTED is mandatory.

1. The Core Conflict: Repeatable Read vs. Non-Blocking Traversal

The isolation level REPEATABLE READ is designed to guarantee that any data read during a transaction remains consistent throughout its lifecycle, preventing phenomena like Non-repeatable Reads and Phantom Reads.

To provide these strong guarantees, database engines employ specialized locking and snapshot mechanics:

  • MySQL (InnoDB): Uses Next-Key Locks (Record Locks combined with Gap Locks) to physically block inserts into scanned index intervals.
  • PostgreSQL: Uses Snapshot Isolation , freezing a read snapshot at transaction start.

Under high-concurrency reservation workflows, these exact mechanisms directly counteract the non-blocking intent of SKIP LOCKED.

2. MySQL (InnoDB): Gap Locks and Deadlock Cycles

2.1. What is a Gap Lock?

In InnoDB, REPEATABLE READ prevents phantom reads by placing locks not only on physical records, but also on the empty intervals (gaps) between them.

$$\text{Next-Key Lock} = \text{Record Lock} + \text{Gap Lock}$$

For example, consider a table reservation_pool with IDs 1, 5, and 10:

[Record 1] ─── (Gap 1 to 5) ─── [Record 5] ─── (Gap 5 to 10) ─── [Record 10]
Enter fullscreen mode Exit fullscreen mode

When a query scans index record id = 1, InnoDB not only locks record 1, but also places a Gap Lock on the interval (1, 5) to prevent other transactions from inserting rows between 1 and 5.

2.2. The Conflict with SKIP LOCKED

The expected behavior of SKIP LOCKED is simple: if record 1 is locked, skip it immediately and lock record 5.

However, under REPEATABLE READ:

Deep Dive: Why SKIP LOCKED Requires READ COMMITTED in MySQL and PostgreSQL

  • Transaction B is prevented from advancing because interval (1, 5) is gap-locked by Transaction A.
  • When hundreds of transactions scan adjacent slots simultaneously, their Gap Locks overlap in a circular dependency, forcing InnoDB to abort transactions with: Deadlock found when trying to get lock.

2.3. The Resolution: Switching to READ COMMITTED

When you configure the transaction to run under READ COMMITTED :

  • InnoDB completely disables Gap Locking for index searches (retaining gap locks only for foreign key and duplicate-key checks).
  • Record locks remain active, but the gaps between records are free.
  • Transaction B detects that id = 1 holds a Record Lock, skips it, and locks id = 5 without blocking.
  • All concurrent transactions proceed in parallel without deadlocks.

3. PostgreSQL: Snapshot Isolation and Serialization Failures

PostgreSQL does not use gap locks, and its system default is already READ COMMITTED.

However, if an application or connection pool is explicitly configured to REPEATABLE READ , SKIP LOCKED fails under a different mechanism: Snapshot Isolation Conflict.

3.1. How Snapshot Isolation Works in PostgreSQL

Under REPEATABLE READ, PostgreSQL takes a snapshot of the database at the start of the transaction ($T_0$). The transaction operates in an immutable view of the world: any writes committed by other transactions after $T_0$ are completely invisible to it.

3.2. Why REPEATABLE READ Aborts Concurrent Queries

Consider two checkout transactions executing concurrently:

Deep Dive: Why SKIP LOCKED Requires READ COMMITTED in MySQL and PostgreSQL

The sequence of events:

  1. Transaction B's frozen snapshot at $T_0$ evaluates Slot 1 as available.
  2. Because Transaction A already committed at $T_1$, Slot 1 currently has no active lock. Therefore, SKIP LOCKED does not bypass Slot 1.
  3. When Transaction B attempts to lock Slot 1, PostgreSQL inspects the tuple on disk and discovers that it was updated and committed by Transaction A after Transaction B's snapshot point ($T_1 > T_0$).
  4. Under REPEATABLE READ, PostgreSQL cannot allow Transaction B to view the new version (violating repeatable read semantics), nor can it allow Transaction B to overwrite Transaction A's committed changes.
  5. PostgreSQL immediately aborts Transaction B with: 
ERROR: could not serialize access due to concurrent update
Enter fullscreen mode Exit fullscreen mode

3.3. The Resolution: READ COMMITTED and EvalPlanQual

Under READ COMMITTED :

  1. Each SQL statement takes a fresh snapshot at the moment it begins execution.
  2. PostgreSQL utilizes its internal EvalPlanQual mechanism:
    • When the engine encounters Slot 1 (modified by Transaction A), it fetches the latest committed version.
    • It re-evaluates the WHERE status = 'available' predicate against the latest version.
    • Because the latest version is now status = 'reserved', it no longer matches the predicate.
    • PostgreSQL cleanly advances to Slot 2 without raising an error.

4. Summary Matrix: MySQL vs. PostgreSQL

Technical Metric MySQL (InnoDB) PostgreSQL
Default Isolation Level REPEATABLE READ READ COMMITTED
Gap Locking Mechanism Active in REPEATABLE READ Not implemented
Behavior under REPEATABLE READ Blocked on index gaps; causes Deadlocks Aborts with serialization failure
Behavior under READ COMMITTED Disables gap locks; skips locked rows cleanly Uses EvalPlanQual; advances to next row cleanly
Recommended Configuration Explicitly set to READ COMMITTED Retain default READ COMMITTED

5. Production Configuration Rules

When implementing Reservation Pools or database-backed task queues:

  1. Always explicitly define the isolation level at the transaction level rather than relying on global server defaults:

  2. Index Alignment: Ensure the reservation_pool table has a composite index on (product_id, status) so the engine evaluates only matching records without broad table scans.

6. Conclusion

In high-concurrency architectures, choosing the right SQL syntax is only half the battle. Understanding how your database engine handles index locks and snapshot versions is what determines whether your system scales or deadlocks under pressure.

By pairing SELECT ... FOR UPDATE SKIP LOCKED with READ COMMITTED, you remove the underlying causes of both MySQL Gap Lock deadlocks and PostgreSQL snapshot serialization conflicts, achieving true non-blocking parallel execution.

Top comments (0)