Allocating sequential invoice numbers safely under concurrent Lambda invocations

Invoice numbers have to be sequential, per series, with no gaps that anyone has to explain and no duplicates at all. It sounds like a MAX() + 1 problem, and on a single server under light load it behaves like one for a long time.

Then the same code runs on Lambda, where concurrency is not a thread pool but separate execution environments that know nothing about each other, and the bug appears.

The bug, stated exactly#

// Wrong.
$last = Invoice::where('driver_id', $driverId)->max('number');
$invoice = Invoice::create(['driver_id' => $driverId, 'number' => $last + 1]);

The gap between those two statements is where two invocations interleave. Both read 41. Both write 42. Two invoices now carry the same number, and nothing in the system objects.

The window is small, which is the problem. It stays closed through development, through testing, and through the first quiet months in production, then opens the first time a user taps twice or two devices sync at once.

Retrying does not help, because neither call failed. Making the request idempotent does not help either: idempotency stops the same operation being applied twice, and this is two different operations getting the same number.

Lock a counter row, not the invoice table#

The fix is to make the second caller wait for the first, which means holding a lock across the read and the write. Lock a dedicated counter row rather than the invoice rows themselves. The counter is one row per series, so the lock is narrow and short, and it exists whether or not any invoice has been written yet.

return DB::transaction(function () use ($driverId, $attributes) {
    $counter = InvoiceCounter::where('driver_id', $driverId)
        ->lockForUpdate()
        ->firstOrFail();

    $counter->increment('next_number');

    return Invoice::create($attributes + [
        'driver_id' => $driverId,
        'number' => $counter->next_number,
    ]);
});

lockForUpdate() is SELECT ... FOR UPDATE. The second transaction blocks on that select until the first commits, then reads the value the first one wrote. The lock is per series, so allocations for different drivers never wait on each other.

Two details that matter more than they look:

Create the counter row when the series is created, not lazily on first use. A lazy firstOrCreate inside the transaction moves the race to the counter row, which is exactly where you just removed it.

Keep the transaction short. Everything inside it holds the lock. Generating a PDF, sending an email or calling a payment provider inside that block turns a lock measured in milliseconds into one measured in seconds, and on Lambda that becomes a queue of invocations all waiting to pay for the same wait.

Add the unique index anyway#

$table->unique(['driver_id', 'number']);

The lock is the mechanism. The index is the guarantee. It costs nothing on a table that is mostly appended to, and it means a future refactor that moves the allocation out of the transaction fails loudly instead of quietly issuing duplicates.

It also changes what a bug looks like. Without the index, the failure is two invoices with the same number discovered at audit time, with no way to tell which came first. With it, the failure is an exception at the moment of writing, with a stack trace.

Handle the violation by retrying the whole transaction once, not by incrementing and trying again in a loop. A loop around a broken allocation is how gaps and long lock waits get introduced together.

Why gaps are fine and duplicates are not#

Someone will eventually ask for no gaps either. It is worth being clear that this is a different requirement with a much higher price.

A gap happens when a number is allocated and the invoice is not created, because the request failed after the counter incremented. Under this design that is possible, and in most jurisdictions a sequence with an explained gap is acceptable while a duplicate number is not.

Guaranteeing no gaps means holding the lock until the invoice is definitely final, which means holding it across whatever else can fail. That is a real trade: more contention, longer transactions, and a much worse failure mode when something downstream is slow. Ask whether the requirement is actually no gaps, or no unexplained gaps, before paying for it.

Why a database sequence is usually not the answer#

Postgres sequences are the obvious tool and they solve a different problem. A sequence is global rather than per series, it is explicitly not gapless, and it does not roll back, so a failed transaction consumes a number anyway. Creating one sequence per driver means DDL at runtime, which is worse than a counter table in every way that matters.

Redis INCR is fast and genuinely atomic, and it is still the wrong home for this. The counter would live in a cache that can be evicted or reset, separate from the transaction that writes the invoice, so a failure between the increment and the insert leaves the two disagreeing with nothing to reconcile them. Keep the number in the same database as the row it numbers, inside the same transaction.

Test it with real concurrency#

A test that allocates a hundred numbers in a loop passes against the broken version, because a loop is sequential. The test has to overlap.

Run parallel processes against a real Postgres, have each allocate a number for the same series, and assert that the set of numbers has no duplicates and the count matches. This is also a case where the sqlite test database lies: its locking behaviour is not Postgres's, so the test has to run on the engine production uses.

Invoicing in YathraBook works this way, on Postgres, under Lambda concurrency. The offline queue that feeds it is described in idempotent offline sync with client-generated operation UUIDs, and the constraints of the runtime itself are in running Laravel on AWS Lambda with Bref, Neon and Upstash.