0

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.

1
  • The only task needed is to read a value and then increment it. Any other operations, such as storing the actual invoices, would not be part of this block. Seems like that should be very fast and that a lock during that operation would have minimal impact. Jan 31, 2022 at 18:44

1 Answer 1

3

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).

5
  • Thanks. A SEQUENCE seemed more oriented for when the number of sequences if fixed. It didn't seem ideal when I need a sequence for each row in a table. That's an interesting point about using a separate table so I don't lock the Locations table. The only downside is that increases complexity to handle cases where a matching row does not exist. Does your code sufficient prevent concurrency issues? Do you think that is still too much to do it on the main table? Jan 31, 2022 at 23:29
  • YMMV on all perf issues. The code prevents multiple sessions from generating the same invoice number, but doesn't prevent sessions generate sequences for the same location from blocking each other. Jan 31, 2022 at 23:54
  • Sorry to pester you, but just to be clear this query would block even read access to the location row I'm working on, right? It seems like it would be very quick, so I don't think it's an issue, but I probably need to think that through. Feb 2, 2022 at 18:37
  • There would be no locking on the Location row if you are only updating the LocationInvoiceNumber row. In READ COMMITTEDN SNAPSHOT or SNAPSHOT isolation reads on even the LocationInvoiceNumber would not be blocked, but the readers would see the pre-update version of the row. Feb 2, 2022 at 20:29
  • Yeah, I'm still trying to decide if it's okay if I don't create the separate table and just put the NextInvoice column directly on the Locations table. Seems like it would be very quick. But it could block. Thanks for all your help! Feb 2, 2022 at 21:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.