Creating sequential invoice numbers in SQL Server without a race condition

Question

I have a legal requirement that in our application's (using SQL Server) collection of invoices there cannot be a gap in our numbering of them. So, if these were invoice numbers, this would not be allowed: [1, 2, 3, 4, 8, 10] because it's not sequential. To that end, we have an InvoiceNumber column on our Invoices table. In addition to that, we have an InvoiceNumbers table which holds the current invoice number per organization (because every organization needs to have its own sequence). A stored procedure is then responsible for filling in the InvoiceNumber on Invoices atomically; it either increments the current counter by 1 in the InvoiceNumbers table and fills in that new value into the Invoices table, or it rolls back the transaction in case of an error. This works well.

Now a new requirement was added: certain orders must share the same invoice and thus the same invoice number, whereas previously every order was invoiced separately. To this end, we create an invoice at the start of the day and associate it with the current FinancialPeriod (the workday, essentially) which will be the invoice used for every order. However, it's possible that an organization does not create any orders of the type that require shared invoicing and so has nothing to invoice during a day which 'wastes' the initially created invoice (because the next day a new one gets created) and creates a gap.

Now, the easiest solution to me was to lazily fill in the InvoiceNumber on the shared invoice that gets created at the start of the day. If an order gets created that day and the InvoiceNumber is still NULL, then create the number. This would ensure an InvoiceNumber never goes unused (it doesn't matter that an Invoice record goes unused, it has no real meaning).

To that end, I've created the below stored procedure, which for an existing Invoice, fills in the InvoiceNumber but only if it's still NULL. I'm just unsure about how SQL Server locks and if there's a potential for a race condition where two database transactions decide that InvoiceNumber is still NULL and will both increment the counter and waste one number, creating a gap.

Essentially, this long-winded question boils down to: can two simultaneous database transactions decide to enter the if(@currentNumber is null) block for the same @invoiceID here?

The locking part you see I've gotten from here, but I'm not sure it applies to my case:

Pessimistic lock in T-SQL

CREATE PROCEDURE [dbo].[CreateInvoiceNumber]
    @invoiceID int,
    @appID int
AS
BEGIN

  SET NOCOUNT ON;

    if not exists (select 1 from InvoiceNumbers where ApplicationID = @appID) insert into InvoiceNumbers values (@appID, 1)

    declare @currentNumber int = null;

    select @currentNumber = convert(int, i.InvoiceNumber)
    from   Invoices i
    with   (HOLDLOCK, ROWLOCK)
    where i.ID = @invoiceID

    if(@currentNumber is null) 
    begin
        update InvoiceNumbers set @currentNumber = Value = Value + 1 
            where ApplicationID = @appID

        update Invoices set InvoiceNumber = @currentNumber where ID = @invoiceID        
    end

    select convert(nvarchar, @currentNumber)
END

EDIT

As mentioned in my comment, these and other write operations are part of a database transaction initiated from the C# application logic. Just a regular BeginTransaction on a SqlConnection with default options, which is of course rolled back in case of any exceptions.

Answer 1

Ensure that the database isolation level has been set to READ COMMITTED.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

This is the default isolation level. It ensures that all transactions must be committed before the row is read, therefore no dirty reads occur.

Also an important note, when updating the InvoiceNumbers table, ensure that it is in a transaction, you want ACID principles to apply here and everything to be atomic (committing as a whole unit or the transaction rolls back).