The document number was six digits of a clock, and the retry loop was the giveaway

The sale-creation handler generated its document number from the current millisecond clock, took the last six digits, prefixed the calendar year, and inserted. Above it sat a comment saying the timestamp approach avoided race conditions. Below it sat a loop that caught the duplicate-key error, generated a different number, slept fifty milliseconds and tried again, up to ten times.

Both of those cannot be true. If the scheme avoided collisions there would be nothing to catch. The loop is the author’s own evidence that the comment is wrong, and it had been sitting there, unremarked, for as long as the module had existed.

The arithmetic

Six digits of a millisecond epoch is one million distinct values, and a millisecond epoch advances one value per millisecond. So the sequence repeats every one million milliseconds, which is sixteen minutes and forty seconds. The year prefix does not help, because the whole year shares it.

That means the number issued at any moment will be issued again a quarter of an hour later, and again a quarter of an hour after that, all year. The only thing standing between the system and a constant stream of duplicates is that two tills rarely bill in the same millisecond — and “rarely” is doing all the work in that sentence, across every till in every tenant for twelve months.

The recovery path is worse than the primary path. On a collision the code rebuilds the number from the last three digits of the clock plus three random digits. Three digits of clock is a one-second cycle. The retry moves from a scheme that repeats every sixteen minutes to one that repeats every second, and makes up the difference with randomness — so the fallback is a thousand-value random draw against whatever is already in the table.

The part that matters more: the uniqueness was not tenant-scoped

The unique constraint sat on the number column alone. Not on the pair of organisation and number — on the number.

This is a multi-tenant product. Every other table in the schema carries an organisation column and every query filters on it. The document number, the one field a customer reads out over the phone and an accountant types into a return, was the single value shared across the whole estate.

The consequence is not a data leak. It is worse in an ordinary way: one tenant’s activity causes another tenant’s sale to fail. A busy customer generates more numbers, which raises the collision rate for everybody, and the tenant who experiences the retries and the eventual failure is whichever one happened to be second. Support gets a ticket saying the till would not bill, from a business that had done nothing unusual.

The same mistake was in the purchase table, on the purchase number, for the same reason: the constraint was written when the first module was built and nobody revisited it when tenancy arrived.

A unique constraint is a statement about the scope in which a value must be unique. If your product is multi-tenant, a constraint that omits the tenant column is asserting something you do not mean. Every uniqueness rule in a tenanted schema should be read out loud with the tenant column missing to see whether the sentence is still what you want. “No two organisations may issue the same invoice number” is not a rule anybody asked for.

Sleeping inside an open transaction

The retry slept fifty milliseconds between attempts. That sleep happens inside the database transaction the handler opened at the top, which by then holds row locks on the inventory rows the sale is about to decrement.

Fifty milliseconds is nothing to a person and a long time to a lock. Ten attempts is half a second of a transaction held open doing nothing, while every other till trying to sell the same product waits behind it. A retry storm under load is exactly the moment you least want to add delay to the critical section, and this delay was reached only under load.

Never sleep inside a transaction. If a retry needs a backoff, the transaction has to end first and the whole unit of work has to be repeated. A retry that keeps the transaction open is not a backoff, it is a hold.

What the number should have been

The scheme was chosen to avoid a counter, because a counter needs coordination and coordination looked slow. That is a reasonable instinct and it is the wrong trade here, because a document number is not a high-throughput identifier. It is issued once per sale, by a human, at a till.

A counter row per tenant per series per year, incremented and returned in a single upsert statement, is atomic, is transactional — if the sale rolls back the number comes back — and contends only with other sales in the same tenant and series. It gives you numbers a person can read and a sequence that means something. The contention it introduces is the contention the business rule already implies.

If a readable sequence genuinely is not required, then the honest alternative is the opposite extreme: a random identifier wide enough that collisions are not a design consideration at all. What does not work is the middle — a short value derived from a clock, patched with a retry, and treated as if the retry were an optimisation rather than an admission.

The habit worth taking

Read every retry loop as a specification. A loop that catches a constraint violation is telling you the author knew the generator could produce a duplicate. If the surrounding comment says otherwise, the comment is the thing that is out of date, and the retry count is a rough estimate of how much the author expected it to happen.

The same reading applies to a try around a unique insert, a fallback that regenerates an identifier, and any code that catches an error class it also raises. None of those are defensive programming. They are the design telling you what it actually does.

The limit I will admit: replacing the generator is easy and migrating the numbers already issued is not. Those numbers are printed on invoices customers hold. The change made was to fix the constraint scope and the generator for new documents, leave the history untouched, and accept that the series has a discontinuity at the date of the change — which is visible, explicable, and much better than a series that quietly repeats itself every sixteen minutes.