17 lock-wait timeout errors on production in under two hours, and user-facing operations are failing. The root cause turned out to be a single SELECT statement that was doing exactly what it was supposed to.

This post is about how InnoDB's locking works beneath the surface, and why a perfectly correct query can silently block unrelated writes through a mechanism that never shows up in logs, EXPLAIN output, or error messages.

Context: The Outbox Pattern

We use a transactional outbox for publishing domain events. When a business operation completes, we don't send a message to the queue (SQS in our case) inline , we write a row to an outbox_messages table within the same database transaction as the operation itself. A background worker picks up those rows every minute and publishes them to the queue.

Two processes, one table:

  • The application: INSERTs rows when something interesting happens (e.g. a state transition)
  • The worker: SELECTs unpublished rows, publishes them to the queue, and marks them as published

This is a well-established pattern. The key property is that the INSERT and the business operation share a transaction, so you can never lose an event.

The Worker Query

The worker runs this:

BEGIN;

SELECT * FROM outbox_messages
WHERE published_at IS NULL
ORDER BY created_at
LIMIT 100
FOR UPDATE SKIP LOCKED;

-- ... publish each message to the queue, mark as published ...

COMMIT;

FOR UPDATE locks the rows so nobody else modifies them mid-batch. SKIP LOCKED means that if another worker instance has already claimed some rows, skip them rather than waiting. The transaction keeps the locks held until the whole batch is committed.

This query is correct. It does exactly what it should. The problem is what it does in addition to what it should.

The Invisible Lock

InnoDB has two default behaviours that combined to cause this:

  1. The default isolation level is REPEATABLE READ. This guarantees that if you run the same query twice in one transaction, you get the same results , no "phantom rows" appearing between reads.
  2. To enforce that guarantee, FOR UPDATE acquires gap locks. A gap lock doesn't lock a row. It locks the empty space between rows in an index, preventing new rows from being inserted there.

Our table has an index on (published_at, created_at). When the worker runs WHERE published_at IS NULL ... FOR UPDATE, InnoDB locks:

  • Each matched row (row locks , expected and needed)
  • The gap after the last NULL entry in the index (gap lock , invisible and not needed)

That gap is precisely where every new outbox row would be inserted, because every new row has published_at = NULL.

So while the worker is working through its batch , publishing 100 messages to the queue, one by one, over the course of potentially a minute , every INSERT into the outbox table is blocked. And if the worker takes longer than 50 seconds (MySQL's default innodb_lock_wait_timeout), the INSERT fails with a lock-wait timeout.

That's what happened. The worker was processing a large batch, the queue was slightly slow, and user-facing operations started failing because they couldn't insert their outbox rows.

Why This Was Hard to Spot

The gap lock doesn't appear anywhere obvious:

  • EXPLAIN shows the query plan, not the locking strategy
  • The slow query log shows the INSERT that timed out, not the SELECT that caused it
  • The worker logs show nothing , it completed successfully
  • SHOW ENGINE INNODB STATUS shows the lock wait, but you have to be looking at the right moment

The error message on the INSERT is Lock wait timeout exceeded, which points you at the INSERT, not at the SELECT holding the lock. You'd naturally assume something is wrong with the write path.

The Fix

The worker doesn't need gap lock protection. It fetches rows once, processes them, and commits. It never re-queries the index, so "phantom rows" appearing mid-transaction are irrelevant.

MySQL lets you set the isolation level per-transaction:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- ... same worker logic ...
COMMIT;

Under READ COMMITTED, FOR UPDATE still acquires row locks (so SKIP LOCKED still works between concurrent workers), but gap locks are eliminated. New INSERTs go through immediately.

We also reduced the default batch size from 100 to 20 as defence-in-depth, shorter transactions mean shorter lock windows.

What We Learned

Correct queries can have invisible side effects on other queries. The worker's query was well-designed. It used FOR UPDATE SKIP LOCKED, which is the textbook approach for concurrent polling consumers. But the gap lock, a side effect of the default isolation level, turned a read path into a bottleneck for the write path. Nothing in the query itself hints at this.

REPEATABLE READ + FOR UPDATE = gap locks. This is InnoDB's default, and it's well-documented, but it's the kind of thing you only internalise after it bites you. If you're writing a polling consumer, a queue processor, or anything that uses FOR UPDATE on a range scan, think about whether the gap lock surface overlaps with your write path.

The outbox pattern has an inherent tension. The worker must hold a transaction (locks need to persist for SKIP LOCKED to work). The write path must insert freely (events are generated in-line with business operations). These two needs compete on the same index range. If you're building an outbox or a similar pattern, this tension is worth designing for upfront.

When an INSERT times out, look at what's holding locks, not just what's waiting. SHOW ENGINE INNODB STATUS under the TRANSACTIONS section will show you both the waiting and the blocking transactions. The error message on the INSERT is misleading; it points you at the victim, not the cause.

Would this happen on PostgreSQL?

No. And the reason is worth understanding, because it highlights a fundamental design difference between the two databases.

PostgreSQL's default isolation level is READ COMMITTED, not REPEATABLE READ. That alone means the gap lock problem never arises in a default PostgreSQL setup, as there are no gap locks to begin with.

But even if you explicitly set PostgreSQL to REPEATABLE READ, it still wouldn't block. PostgreSQL doesn't use lock-based concurrency control the way InnoDB does. Instead, it uses MVCC snapshots: each transaction sees a frozen view of the data as it existed when the transaction started. There are no gap locks in PostgreSQL's implementation at any isolation level.

The practical difference:

  • InnoDB (REPEATABLE READ): prevents phantom rows by blocking writes. The gap lock physically stops new rows from being inserted into the locked range. This is pessimistic: it assumes conflict and prevents it upfront.
  • PostgreSQL (REPEATABLE READ): prevents phantom rows by ignoring them. The snapshot means the transaction simply cannot see rows inserted after it started. No blocking needed. If a true conflict exists (e.g., two transactions updating the same row), PostgreSQL throws a serialisation error and lets the application retry.

This means the exact same outbox worker, same SQL, same FOR UPDATE SKIP LOCKED, same batch transaction, works without modification on PostgreSQL and would never block the write path.

The takeaway isn't that PostgreSQL is better. It's that "standard SQL" doesn't specify locking behaviour: two databases can both claim REPEATABLE READ support and implement it with completely different mechanisms and side effects. If you're switching between MySQL and PostgreSQL (or consulting documentation from one while working in the other), don't assume locking semantics transfer. They rarely do.