InnoDB Gap Locks vs PostgreSQL SSI: Rethinking Isolation on Migration

Of everything that changes when you move from MySQL to PostgreSQL, concurrency behavior is the least visible in a schema diff and the most likely to produce a subtle production bug. Both databases are MVCC engines, both default to a read-committed-flavored happy path for most applications, and both can give you real serializability if you ask. But the machinery is genuinely different, and instincts trained on InnoDB will misfire on PostgreSQL in both directions.

I want to be fair to both systems here. InnoDB's locking model is coherent and battle-tested; PostgreSQL's Serializable Snapshot Isolation is one of the most elegant pieces of engineering in any open-source database. The migration problem is not that one is better. It is that your application's retry logic, timeout tuning, and phantom-prevention strategy were built for one and will be running on the other.

How InnoDB prevents phantoms: locking the space between rows

InnoDB's default isolation level is REPEATABLE READ. Plain SELECTs read from a consistent snapshot and block nobody. But locking reads (SELECT ... FOR UPDATE, SELECT ... FOR SHARE) and writes use next-key locks: a lock on the index record plus the gap before it. By locking gaps, InnoDB stops other transactions from inserting rows into a range you have read with a locking read, which is how it prevents phantom rows without full predicate locking.

This design works, and it is also the source of the locking behavior MySQL operators know best. Gap locks on a hot range serialize inserts into that range. A SELECT ... FOR UPDATE on a value that does not exist locks the gap where it would be, which surprises people the first time an "empty" read blocks a neighbor's insert. Insert intention locks, duplicate-key checks, and foreign-key checks add their own lock interactions. The classic result is the insert-heavy deadlock: two transactions each hold a gap lock and each wants an insert intention lock in the other's gap, InnoDB detects the cycle, and one transaction dies with error 1213.

-- InnoDB, REPEATABLE READ: this locks the gap even if no row matches.
START TRANSACTION;
SELECT * FROM bookings
WHERE room_id = 42
  AND starts_at >= '2026-07-01' AND starts_at < '2026-07-02'
FOR UPDATE;
-- Concurrent inserts into this range now wait or deadlock.
INSERT INTO bookings (room_id, starts_at, ends_at)
VALUES (42, '2026-07-01 10:00', '2026-07-01 11:00');
COMMIT;

Note that under READ COMMITTED, InnoDB mostly stops using gap locks, which is why many high-throughput MySQL shops run READ COMMITTED with row-based replication and accept that phantoms are the application's problem. That is a legitimate operating point, and knowing whether your MySQL fleet runs there matters for the migration, because it tells you which anomalies your code already tolerates.

How PostgreSQL handles the same problem: no gap locks at all

PostgreSQL has nothing like a gap lock in its regular executor path. Writers take row-level locks on the rows they touch; readers never block writers and writers never block readers. PostgreSQL's REPEATABLE READ is snapshot isolation: the transaction sees a frozen snapshot, so re-running a query cannot return new phantom rows within the transaction, without locking any ranges to do it.

The catch is that snapshot isolation permits write skew. Two transactions each read an overlapping predicate, each write disjoint rows based on what they read, and both commit; the combined result violates an invariant that each transaction individually preserved. The textbook example is the on-call rota: two doctors each check that someone else is on call, then both withdraw. InnoDB's REPEATABLE READ with locking reads happens to prevent some of these cases by blocking; PostgreSQL's REPEATABLE READ does not, because nothing blocks.

PostgreSQL's answer is the SERIALIZABLE level, implemented since 9.1 as Serializable Snapshot Isolation. SSI runs transactions optimistically on snapshots, tracks read and write dependencies with SIRead predicate locks, and aborts a transaction when it detects a dependency pattern that could produce a non-serializable outcome. The critical property: SSI does not block. Conflicts surface as a rollback with SQLSTATE 40001, serialization_failure, and the application retries.

-- PostgreSQL: the booking check, made safe by SSI instead of gap locks.
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM bookings
WHERE room_id = 42
  AND starts_at < '2026-07-01 11:00'
  AND ends_at > '2026-07-01 10:00';
-- Application checks the count, then:
INSERT INTO bookings (room_id, starts_at, ends_at)
VALUES (42, '2026-07-01 10:00', '2026-07-01 11:00');
COMMIT;  -- may fail with 40001 if a conflicting booking committed

Where MySQL made you pay for phantom safety with blocking and deadlocks, PostgreSQL makes you pay with retries. Same debt, different currency.

Retry logic: the code you must rewrite, not port

MySQL applications typically handle two error classes: 1213 (deadlock found, transaction rolled back) and 1205 (lock wait timeout exceeded, which by default rolls back only the statement, a detail that has caused more partial-transaction bugs than I can count). PostgreSQL applications at SERIALIZABLE or REPEATABLE READ must handle 40001 (serialization_failure) and 40P01 (deadlock_detected), and should treat both identically: roll back the whole transaction and retry the whole transaction from the top.

Three rules make PostgreSQL retry loops safe. First, the transaction must be repeatable: no side effects outside the database (emails, queue publishes) before commit, or make them idempotent. Second, retry with capped attempts and small jitter; serialization failures cluster under contention and naive tight loops amplify it. Third, log the retries. A rising 40001 rate is a workload signal, not noise, and it usually points at one hot predicate you can restructure.

Also port your timeout thinking. innodb_lock_wait_timeout has a PostgreSQL cousin in lock_timeout, but PostgreSQL defaults it to off; long lock waits sit there politely unless you bound them. Setting lock_timeout for interactive paths and statement_timeout globally is standard hygiene, and it matters double during migrations when DDL takes locks the application does not expect. Our ALTER TABLE locking guide covers that intersection.

Choosing an isolation strategy on PostgreSQL

• Most OLTP paths: READ COMMITTED plus explicit locking. The default level with SELECT ... FOR UPDATE on the specific rows you will modify handles the bulk of real-world correctness needs, exactly as in MySQL, minus the gap-lock side effects.
• Invariant-bearing transactions: SERIALIZABLE. Anything of the shape "read a predicate, decide, write" (bookings, balances, quotas, uniqueness across a computed condition) belongs at SERIALIZABLE with a retry loop. Do not try to hand-simulate gap locks.
• When you need a real materialized conflict: constraints or advisory locks. Exclusion constraints (for ranges), unique partial indexes, and pg_advisory_xact_lock give you explicit, targeted serialization far cheaper than locking whole tables.
• Reporting reads: REPEATABLE READ. A consistent snapshot for multi-statement reads, no retry loop needed for read-only work, and read-only serializable deferrable transactions if you need full serializability without abort risk.

One honest caveat in PostgreSQL's disfavor: SSI's predicate tracking has memory and CPU cost, and under coarse tracking (when the lock table fills, SIRead locks escalate to page or relation granularity) false-positive aborts increase. Long-running transactions also pin SSI state. Keep serializable transactions short and the retry budget honest. For how this fits into overall engine differences, the MySQL and PostgreSQL comparison pages take the wider view.

What to watch after cutover, and how MonPG helps

Concurrency regressions after a MySQL-to-PostgreSQL migration are behavioral, so they only show up under real traffic: a lock chain behind one idle-in-transaction session, a 40001 storm on a hot predicate after a deploy, or a lock_timeout misconfiguration turning waits into user-visible errors. Catching these requires database evidence with history, not a one-time load test.

MonPG is PostgreSQL-only monitoring, and this is the exact scenario it is built for: lock chains from pg_locks with the blocking session identified, wait-event history that distinguishes lock waits from I/O and CPU, and pg_stat_statements trends that show which query family changed when the serialization failures started. Pair that with slow query monitoring for the latency side, and the first post-migration concurrency incident becomes a fifteen-minute diagnosis instead of a war room. The PostgreSQL monitoring guide shows the baseline to have in place before you need it.