intermediateAdvanced SQL — Stored Procedures, Functions, Triggers & Sequences
Why do sequences guarantee uniqueness but not gaplessness?
nextval() uses a lightweight increment operation that's never rolled back, even if the surrounding transaction is — a transaction that calls nextval() and then rolls back loses that value forever, since guaranteeing no gaps would require blocking every other concurrent nextval() call until that transaction resolves. Uniqueness under concurrency and gaplessness are fundamentally in tension.
Ready to master this question?
Generate a complete walkthrough — background, the full answer in plain language, a working code example explained line by line, a real-world scenario, common mistakes, and how this same question gets asked in different ways.
Sign in to generate a response