3 Oct 2026 · @hossam Assadallah
Two invoices, one number
Friday, 2 PM, and the restaurant is packed. Two cashiers hit "Save" at almost the same moment. Both get invoice number 10452.
At month end, the accountant finds the duplicate. One of the two invoices drops out of the report; the other stays. The money gets counted once instead of twice.
The system had run for years without a problem. The bug wasn't new. It was one line written on day one.
The suspect
Most of us have written this, or inherited it in someone else's code:
SELECT NVL(MAX(invoice_no), 0) + 1
INTO v_new_no
FROM invoices;
INSERT INTO invoices (invoice_no, ...)
VALUES (v_new_no, ...);
It looks perfectly reasonable: take the highest number and add one. With one user on one machine, it will work forever. It only breaks when several people write at once, which is exactly when the system matters most.
What actually happened
Oracle gives every session a consistent view of the data (read consistency). A session can't see rows another session inserted but hasn't committed yet.
Time
Cashier 1
Cashier 2
14:00:00.100
MAX = 10451 → computes 10452
14:00:00.120
MAX = 10451 → computes 10452
14:00:00.150
INSERT 10452
14:00:00.170
INSERT 10452
14:00:00.200
COMMIT
COMMIT
Both sessions read the same truth at the same moment, and both were right from where they stood. The flaw is that "read, then write" is two separate steps, and anything can happen in the gap between them.
The more users you add, the more often that gap gets hit.
The usual patches
When the bug surfaces, the first reaction is usually one of these:
• A unique constraint on the column. It stops the duplicate, but the second cashier gets ORA-00001 in front of the customer. You turned a silent bug into a loud one; you didn't fix it.
• Retry on error. It can work, but under load you get sessions looping and colliding again.
• LOCK TABLE, or SELECT ... FOR UPDATE on a counter row. This does prevent duplicates, by making every session wait in line. The whole system now issues one invoice at a time, and the lock is held until the entire transaction finishes, not just until the number is computed.
On top of that, MAX on a table with millions of rows costs an index read on every invoice, and sometimes a full scan if you filter by branch or year.
Sequences: a counter that waits for no one
CREATE SEQUENCE invoice_seq
START WITH 10453
CACHE 50;
INSERT INTO invoices (invoice_no, ...)
VALUES (invoice_seq.NEXTVAL, ...);
The key difference: a sequence lives outside the transaction. NEXTVAL hands you a number and moves on immediately. It doesn't read the table and doesn't wait for anyone's commit.
• No duplicates: every session gets a number no one has taken, regardless of how many sessions there are.
• No queue: a thousand cashiers can pull numbers at the same instant.
• No table reads: with CACHE, numbers are reserved in memory, so most calls never touch disk.
One expression instead of a SELECT, a MAX and a lock, and it's faster and safer than both.
"But sequences leave gaps"
True. A ROLLBACK, or an instance restart that drops the cache, and you'll see missing numbers: 10452, then 10455.
That isn't a defect. It's the price of having no queue. A number once handed out never comes back, because taking it back would mean one session waiting on another to decide.
The real question is whether gaps actually matter to you:
• If the number is an internal key (an ID), nobody cares about gaps. Use a sequence and move on.
• If there's a legal or accounting requirement for gapless numbering, separate the two. Keep the ID from a sequence, and generate the customer-facing serial number only at final posting, from a counter table with FOR UPDATE. The queue now sits on one small step at the end, not on the whole sale.
From 12c on: let the table handle it
On Oracle 12c or later, you don't need to wire up a sequence and a trigger yourself:
CREATE TABLE invoices (
invoice_id NUMBER GENERATED ALWAYS AS IDENTITY,
...
);
Under the hood it's a regular sequence, but bound to the column, so no one can insert a manual value by mistake. If you need to supply values during a migration, use BY DEFAULT ON NULL instead of ALWAYS.
The takeaway
MAX()+1 assumes you're alone in the database. A system that makes money is never alone.
A sequence isn't "another way" to do the same thing. It's a tool built for exactly this problem: unique numbers, no queue, no table reads. The gaps it leaves cost far less than two invoices with the same number.
Have you found MAX()+1 in code you inherited? Did it bite you, or is it still waiting?
Top comments (0)