Get next invoice number and increment without concurrency problems
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
NextInvoicevalue 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.
Bogopo last edited by
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).