sql - 在 SQL Server 中创建连续的发票编号而无需竞争条件

Question

我有一个法律要求，在我们的应用程序(使用 SQL Server)发票集合中，我们的发票编号不能有间隙。因此，如果这些是发票号码，则不允许使用:[1, 2, 3, 4, 8, 10]，因为它不是连续的。为此，我们的 Invoices 表中有一个 InvoiceNumber 列。除此之外，我们还有一个 InvoiceNumbers 表，其中保存每个组织的当前发票编号(因为每个组织都需要有自己的序列)。然后，存储过程负责自动填写 Invoices 上的 InvoiceNumber；它要么将 InvoiceNumbers 表中的当前计数器加 1，并将新值填充到 Invoices 表中，要么在发生错误时回滚事务。这效果很好。

现在添加了一项新要求:某些订单必须共享相同的发票，因此具有相同的发票编号，而以前每个订单都是单独开具发票的。为此，我们在一天开始时创建一张发票，并将其与当前的FinancialPeriod(本质上是工作日)关联起来，这将是每个订单使用的发票。但是，组织可能不会创建任何需要共享发票类型的订单，因此在一天内没有任何发票可开具，这会“浪费”最初创建的发票(因为第二天会创建新的发票)并创建一个新的发票。差距。

现在，对我来说最简单的解决方案是在当天开始时创建的共享发票上懒惰地填写 InvoiceNumber 。如果当天创建订单且 InvoiceNumber 仍为 NULL，则创建该编号。这将确保 InvoiceNumber 永远不会被使用(Invoice 记录未被使用并不重要，它没有任何实际意义)。

为此，我创建了以下存储过程，对于现有的 Invoice，它会填充 InvoiceNumber，但前提是它仍然是 NULL。我只是不确定 SQL Server 如何锁定，以及是否存在潜在的竞争条件，即两个数据库事务决定 InvoiceNumber 仍为 NULL 并且都会增加计数器并浪费一个数字，造成差距。

本质上，这个冗长的问题可以归结为:两个同时发生的数据库事务是否可以决定进入同一个 @invoiceID 的 if(@currentNumber is null) block 在这里？

您看到的锁定部分是我从这里获得的，但我不确定它是否适用于我的情况:

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

编辑

正如我在评论中提到的，这些和其他写入操作是从 C# 应用程序逻辑发起的数据库事务的一部分。只是带有默认选项的 SqlConnection 上的常规 BeginTransaction，当然会在出现任何异常时回滚。

Answer 1

确保数据库隔离级别已设置为READ COMMITTED。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

这是默认的隔离级别。它确保所有事务必须在读取行之前提交，因此不会发生脏读。

还有一个重要的注意事项，在更新 InvoiceNumbers 表时，请确保它位于事务中，您希望在此处应用 ACID 原则，并且所有内容都是原子的(作为整个单元或事务提交)回滚)。