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.
This is a Pro chapter
Sign in, then upgrade to Pro or Power to unlock this and the full Databases Mastery library.
Why do sequences guarantee uniqueness but not gaplessness?