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.