DEV Community: Hossam Assadallah

MAX()+1: The Invoice Number That Showed Up Twice

Hossam Assadallah — Sat, 03 Oct 2026 12:48:22 +0000

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?

Oracle #PLSQL #Database #SQL

# The Suspiciously Round Number That Stopped Me Cold

Hossam Assadallah — Sun, 13 Sep 2026 23:25:05 +0000

I was pulling data from SAP through an OData API — every Internal Order from 2020 to today. The response came back with exactly 1000 rows. Not 987, not 1214. A clean, round 1000.

Was I happy? No. I got suspicious.

In data work, a number that "too perfect" is usually a lie. Real data comes back messy — fractions, odd totals, nothing that lines up so neatly. That 1000 wasn't telling me "here's your data." It was telling me "this is a ceiling."

I tried the standard fix: paginate using OData's own $skip and $top parameters. SAP's gateway ignored both completely and kept returning the exact same first 1000 rows, as if the parameters didn't exist. There was no __next link in the response either — nothing pointing to a next page.

So I tried a different check. I ran $count (a request that returns just the row count, no data) over the same date range — and got 1000 again. Even the count itself was capped by the same limit. I split the range in half and ran $count on each half separately: 938 on the first half, 651 on the second. Real total: 1589. Not 1000.

589 rows would have quietly disappeared, and nobody would have known — if I hadn't been suspicious of a number that looked too clean.

The lesson: any time an API can return a lot of data, assume it's silently capped until you prove otherwise. A round number isn't a sign of success — it's the first flag that something needs verification. And trusting the standard protocol alone isn't enough: here, SAP's own gateway simply didn't honor OData's standard pagination mechanism. The fix is building independent verification — like splitting time windows and cross-checking partial counts — before you trust any number an API hands you.

Have you run into this? An API silently truncating your data without telling you?

SAP #OData #DataEngineering #NodeJS #OracleAPEX #SoftwareEngineering #API

"Boredom Isn't the Enemy of Creativity — Knowing When to Stop Is the Skill

Hossam Assadallah — Sun, 13 Sep 2026 03:26:46 +0000

The second English piece from "Psychology of the Keyboard," a series I write about the mental side of being a programmer — the part nobody puts in the documentation.
I was standing at the sink, mid-wudu — not in front of a laptop, no editor open, not a single line of code in front of me — and the solution just jumped into my head. I'd been stuck on a bug for two days: an integration issue between FastAPI and Oracle. I'd tried everything — changed the query, dug through the documentation, asked everyone I could think of. Nothing. Then, while washing my hands, with zero warning, I suddenly understood: the problem wasn't in the code at all. The connection pool wasn't closing properly before it reopened. Something embarrassingly simple — but my mind couldn't see it while I was sitting there forcing focus.
That's not a coincidence. In cognitive psychology it's called the Default Mode Network — the part of your brain that activates when you're not demanding conscious focus from it: washing dishes, walking, showering. In those moments, your brain isn't off. It keeps working quietly in the background, connecting pieces of information that were scattered while you were focused, which is exactly what produces that "sudden" solution.
The problem is that we're trained, as programmers, to believe the answer comes from pressure — the harder you focus, the faster you'll find it. That's true, up to a point. Past that point, it works in reverse. When your brain sits on the same problem for two hours straight, it drops into a narrow tunnel of focus that shuts out every other possibility, and just keeps circling the same idea that already failed.
Boredom isn't the enemy of creativity. Boredom is the door that opens that space — when you let yourself get distracted for a bit, without a phone, without a screen, not to be lazy, but to give your brain the chance to work in a completely different mode than the one you're used to at your desk.
Take this as a rule, not a luxury: when you're stuck in front of a problem and can't move a single step forward, step away. Not just to rest your body — to give the other half of your mind room to work.
Has a coding solution ever come to you while you were nowhere near a computer? Tell me where, and when.

The 3 A.M. Moment Every Programmer Lies to Themselves About

Hossam Assadallah — Sat, 12 Sep 2026 20:27:28 +0000

The second English piece from "Psychology of the Keyboard," a series I write about the mental side of being a programmer — the part nobody puts in the documentation.

It was around three in the morning. I'd been in front of the screen since nine at night, staring at the same line of code for what felt like the hundredth time. The function looked perfect on paper — no visible issue — but the result kept coming out wrong. I tried everything I could think of: changed the condition, dug through Stack Overflow, asked an AI. Nothing changed.

I still remember exactly what I felt in that moment. It wasn't physical tiredness. It was something else entirely — a feeling that I wasn't good enough, that maybe the problem was in my own head, not in the code. That if my colleagues saw me stuck like this, they'd assume I was a beginner, not someone running a company that builds systems handling real data.

That's the thing almost nobody talks about in programming. We talk about algorithms, best practices, the newest framework — but nobody tells you this profession takes as much from your mental health as it does from your time.

In that moment of being stuck, something happens in your brain called tunnel vision. Your mind locks onto the same path you already tried and failed on, and keeps pulling you back to it — not because the answer is there, but because a tired brain finds it easier to repeat the same move than to open a new path. The dangerous part of that moment isn't failing. It's staying seated, convinced that continuing in the same direction is the answer — which is exactly what turns one exhausting hour into two or three wasted ones.

The fix isn't to be "strong" and push through. The fix is giving yourself real space away from the screen — even five minutes. Walk. Drink water. Look at something completely different. The mind isn't a machine; it needs to reorganize itself. And when you come back, you'll often find your eyes suddenly see something that had been sitting in front of you for two hours, unseen.

One more thing worth saying clearly: your value as a programmer isn't in solving everything fast. It's in continuing even when a lot of things don't get solved on the first try. Every professional programmer has been through the exact moment I just described. The difference is learning when to stop and when to keep going — not that they stopped getting lost altogether.

Have you had a moment like this? What actually helps you find your focus again?

What Training an AI Agent Taught Me About Letting Go of Control

Hossam Assadallah — Sat, 12 Sep 2026 18:25:21 +0000

his is the first English piece from "Psychology of the Keyboard," a series I write about the mental side of being a programmer — the part nobody puts in the documentation.

I was training the AI agent behind Amin, the product I've been building, and I had the exact reply mapped out in my head for every question it should get. I wrote the prompt, reviewed it, tuned it, ran the test. The reply came back — not wrong, exactly, just not phrased the way I'd already decided it should be. I went back, adjusted, tried again. A third reply came out, different from the first two. And I felt something strange. Not frustration at the model's performance — a feeling that I was losing control over something I had built from scratch.

I paused the training. Not because the model was wrong, but because I couldn't tolerate a system I'd built with my own hands producing responses I didn't control 100%. That made me think — this wasn't the first time I'd felt this. It shows up in everything I build, from the database layer to the UI: I want every detail under my control, completely.

And that instinct makes perfect sense in our profession. We're trained to believe that one point outside our control can break the whole system, so we've been conditioned to treat total control as the only real safety. The problem is that mindset doesn't stay inside the code. It follows you out. You catch yourself wanting to control an employee's reaction, a client's decision, even your own kid's mood mid-conversation — because your brain has learned that "no full control means danger."

But an agent, like any AI system, is built on probability, not fixed rules. It's not supposed to give you the exact same wording every time — that's not a flaw, that's its nature. The real lesson wasn't figuring out how to make the output 100% consistent. The lesson was learning to separate "controlling the general direction" from "controlling every word." I needed to tune the framework and the rules, not micromanage every small detail inside it.

Once I let the agent take up more space inside the framework I'd set, the responses got more natural and closer to each customer's actual tone — not because I'd gotten less precise, but because I'd gotten confident that the framework itself was enough.

Have you ever caught yourself trying to control something that was never meant to be fully controlled, at work or outside it? How did you know it was time to let go?