skip to content

Your table's auto-generated primary keys have gaps — values 41, 42, then 57. Explain how a rollback, a crash, or ordinary concurrency can produce that, and whether it indicates a bug.

level: middleimportance: must knowfreq 62%

answer

  1. allocation is non-transactional
  2. rollback burns the value
  3. crash discards cached block
  4. allocation order ≠ commit order
  5. gap-free ⇒ transactional counter + lock

basics

~20 s

It is normal, not a bug. Sequence allocation is non-transactional: a value handed out is consumed even if the transaction rolls back, a crash discards cached values, and concurrent sessions interleave allocations. Sequences guarantee uniqueness, never contiguity.

solid answer

~50 s

Gaps are expected. Sequence allocation deliberately sits **outside transaction control**: when a transaction takes a value and then rolls back, the value is not returned — rolling it back would require serialising every allocator against every other, destroying concurrency. So any failed insert, constraint violation, deadlock victim or client disconnect burns a number. A crash creates larger gaps. Engines preallocate values in memory (the `CACHE` setting) and only persist a high-water mark, so an unclean shutdown discards everything cached but unissued — a jump of the cache size or more. Concurrency creates gaps too: sessions take values 41, 42, 43 in parallel and commit out of order, so if 42's transaction fails you see 41 and 43, and 43 may become visible before 41. The contract is uniqueness, not contiguity, and not commit ordering. If the business genuinely needs gap-free numbering — invoices in some jurisdictions — that requires a separate transactional counter with a real lock, and you must accept the serialisation cost.

code

sql · 9 lines
sql
BEGIN;
UPDATE invoice_counter
   SET last_number = last_number + 1
 WHERE series = '2026'
RETURNING last_number;   -- rollback here restores the number

INSERT INTO invoices (number, series, customer_id)
VALUES (:last_number, '2026', 42);
COMMIT;

go deeper

for a junior

State clearly that gaps are normal because a rolled-back transaction does not give its number back; sequences guarantee uniqueness only.

for a middle

Cover all three causes — rollback, crash with a discarded cache, concurrent interleaving — and name what the sequence does and does not guarantee.

for a senior

Add the operational traps: pollers keyed on id > watermark, sparse ranges in pagination, and the cost model of a gap-free transactional counter.

for a principal

Treat it as a contract question: decide where in the system contiguity is a real requirement, isolate that path, and keep the high-throughput path on non-transactional allocation.

## The design decision behind gaps A sequence exists to hand out unique numbers to many concurrent sessions cheaply. To make a number reusable after a rollback, the engine would have to remember who holds which value, keep the allocation in the transaction's undo scope, and prevent later allocators from passing a hole that might reopen. That is a serialisation point: allocation would effectively become one-at-a-time and transaction-ordered. So every mainstream engine makes the opposite choice: **allocation is non-transactional**. Taking a value has an immediate, permanent effect on the sequence's state, visible to other sessions at once, and unaffected by whether your transaction commits or rolls back. ## The three sources of gaps **1. Rollback and failed statements.** The `nextval` happens when the row is built. Anything after that — a unique-key violation on another column, a check constraint, a deadlock, an application exception, a client that vanishes — rolls the row back but not the number. A retry loop on a busy table can burn many values per stored row. **2. Crash and cache loss.** For throughput, engines preallocate a block of values (the `CACHE` setting, or an equivalent in-memory counter) and only durably record the top of the block. Values handed out are safe because the persisted high-water mark is already past them, but on an unclean shutdown every cached-but-unissued value is discarded. With `CACHE 50` you can lose up to 49 values in one restart. Some engines also fail to persist an in-memory counter at all and re-derive it at startup from the table's maximum, which produces the opposite anomaly — *reused* values after a crash if the highest rows were deleted. **3. Concurrency.** Two sessions take 41 and 42 simultaneously. Session A (41) is slow, Session B (42) commits first. A reader sees 42 exist while 41 does not yet; if A then rolls back, 41 never appears. Nothing is broken — you are just seeing that id order is allocation order, not commit order and not visibility order. ## Why this is not a bug — and what it breaks if you assume otherwise The guarantee a sequence offers is *uniqueness under concurrency*, plus monotonic issuance per generator. It offers no contiguity and no ordering guarantee for commits. Common incorrect assumptions built on gaps: - **"MAX(id) is the row count."** It is not, and never becomes one. - **"Pagination by id ranges scans a dense space."** Ranges are sparse; keyset pagination must page by the last id seen, not by arithmetic ranges. - **"Anything with an id greater than my watermark is newer."** False under concurrency: a transaction holding a lower id can commit after your read of the watermark, so a poller keyed on `id > last_seen` can miss rows permanently. Use a commit-visible marker, a change table, or a strictly increasing commit-ordered column. - **"Invoice numbers can be sequence values."** Where a regulator requires an unbroken series, gaps are a compliance defect, not a curiosity. ## What to do when you truly need gap-free numbers Accept serialisation and make the counter transactional: a single row holding the last used number, updated under a row lock inside the same transaction that consumes the number, so a rollback restores it. Throughput is then bounded by that lock's hold time — which is exactly the cost sequences were designed to avoid. Mitigations: apply gap-free numbering only at the moment it is legally required (issue the invoice number at finalisation, not at draft creation), keep the locking transaction extremely short, and partition the series (per year, per branch) so contention splits. ## How to answer Name the mechanism (non-transactional allocation), give all three causes, state the guarantee (uniqueness, not contiguity), and close with the gap-free alternative and its cost. Volunteering the "id > watermark misses rows" trap marks a candidate who has actually debugged this.

  • A worker polls for new rows with WHERE id > :last_seen_id. Why can it silently skip rows?
    Ids are allocated before commit, so a transaction holding id 100 can commit after one holding id 101. If the poller reads while 101 is visible and 100 is not, it advances its watermark to 101 and never sees 100. Poll on a commit-ordered signal instead — a change/outbox table consumed transactionally, or a marker written at commit time — rather than on the generated key.
  • How would you implement invoice numbers that a regulator requires to be gap-free?
    Use a transactional counter: a single row per series updated under a row lock in the same transaction that issues the invoice, so a rollback returns the number. Keep that transaction as short as possible, assign the number as late as possible (at finalisation, not draft creation), and partition the series by year or branch to split contention. Accept that throughput on that path is serialised by design.

A deli ticket machine. Take a ticket and leave without being served, and that number is simply never called. The machine guarantees no two customers hold the same ticket, not that every ticket gets used.

saying these in an interview costs you the question

  • Calling gaps a bug or a sign of data loss
  • Believing a rollback returns the sequence value
  • Assuming id order equals commit order or visibility order
  • Using MAX(id) as a row count
  • Proposing sequences for legally gap-free document numbering

context

resolving links…