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



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2