Get next invoice number and increment without concurrency problems

Question

I have a table of locations that looks something like this:

CREATE TABLE Locations (
    Id INT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(255) NOT NULL,
    NextInvoice INT NOT NULL,
    PRIMARY KEY (Id)
);

Now I want to create an invoice for a location and I need an invoice number.

The invoice number will be equal to the NextInvoice value for that location. And then I must increment this value for the next invoice.

Very simple logic but is complicated by concurrency and race conditions.

How could I perform that task of retrieving the next available invoice number for a location and then incrementing that value, and not duplicating or skipping any numbers when that code is called more than once at the same time?

I cannot use an auto-increment number of unique constraint because numbers can be duplicates across locations.

Note: My core skills include C# and not necessarily SQL-Server. In fact, I'm using Entity Framework for this. But I'm not above writing a stored procedure if needed.

Answer 1

If the transaction that allocates invoice numbers is always short-running, and your throughput needs are modest (a few 100s of invoices/sec), you can use a sequence table. It shouldn't be the main "Location" table, as rows are going to be frequently (but briefly) exclusively locked. So

CREATE TABLE LocationInvoiceNumbers 
(
    Id INT NOT NULL references Locations,
    NextInvoice INT NOT NULL,
    PRIMARY KEY (Id)
);

Then to generate a new invoice number

update LocationInvoiceNumbers 
set NextInvoice = NextInvoice + 1
output inserted.NextInvoice
where Id = @Id

If your Invoice-creation process requires longer-running transactions, you can pre-generate as many InvoiceNumbers as you need and commit them.

And if you need to generate a lot of new InvoiceNumbers for a location you can add more than 1 to NextInvoice, eg set NextInvoice = NextInvoice + @numInvoices.

If you really need high throughput, then use a single global SEQUENCE object or IDENTITY column across Locations, and abandon the idea that each Location will have its own invoice numbers (even if the data model allows it).