确保 SQL Server 数据库中的唯一编号

Question

我有一个使用事件编号（以及其他类型的数字）的应用程序。这些数字存储在名为“Number_Setup”的表中，其中包含计数器的当前值。

当应用程序生成新事件时，它会在 number_setup 表中获取所需的数字计数器行（计数器可以每天、每周等重置，并存储为 int）。然后它递增计数器并用新值更新该行。

该应用程序是多用户的（每次大约有 100 个用户，以及运行并获取 100 个事件记录并请求每个事件编号的 SQL 作业）。事件表中有一些重复的事件编号，它们不应重复。

存储过程用于检索下一个计数器。

SELECT @Counter = counter, @ShareId=share_id, @Id=id
FROM Number_Setup
WHERE LinkTo_ID=@LinkToId
AND Counter_Type='I'

IF isnull(@ShareId,0) > 0
BEGIN 
    -- use parent counter
    SELECT @Counter = counter, @ID=id
    FROM Number_Setup
    WHERE Id=@ShareID
END

SELECT @NewCounter = @Counter + 1

UPDATE Number_Setup SET Counter = @NewCounter
WHERE id=@Id

我现在已经用事务包围了该块，但我不完全确定它会 100% 解决问题，因为我认为仍然存在共享锁，因此无论如何都可以读取计数器。

也许我可以检查计数器是否尚未更新，在更新声明中

UPDATE Number_Setup SET Counter = @NewCounter
WHERE Counter = @Counter
IF @@ERROR = 0 AND @@ROWCOUNT > 0 
    COMMIT TRANSACTION
ELSE
    ROLLBACK TRANSACTION

我确信这是金融应用程序等中发票号码的常见问题。
我也无法将逻辑放入代码中并在该级别使用锁定。 我也锁定了 HOLDLOCK，但我不确定它的应用。是否应该放在两个 SELECT 语句上？

如何确保不会创建重复项？

Answer 1

诀窍是在单个原子操作中进行计数器更新和读取：

UPDATE Number_Setup SET Counter = Counter+1
OUTPUT INSERTED.Counter 
WHERE id=@Id;

但这不会将新计数器分配给 @NewCounter，而是将其作为结果集返回给客户端。如果必须赋值，则使用中间表变量将新计数器输出 INTO：

declare @NewCounter int;
declare @tabCounter table (NewCounter int);
UPDATE Number_Setup SET Counter = Counter+1
OUTPUT INSERTED.Counter INTO @tabCounter (NewCounter)
WHERE id=@Id
SELECT @NewCounter = NewCounter FROM @tabCounter;

这解决了使 Counter 增量原子化的问题。您的过程中仍然存在其他竞争条件，因为 LinkTo_Id 和 share_id 仍然可以在第一次选择后更新，因此您可以增加错误链接到项目的计数器，但这不能仅通过此代码示例来解决，因为它还取决于在实际更新shared_id和/或LinkTo_Id的代码上。

顺便说一句，您应该养成使用一致大小写命名字段的习惯。如果它们的命名一致，那么您必须在 T-SQL 代码中使用完全匹配的大小写。您的脚本现在运行良好，只是因为您有一个不区分大小写的排序规则服务器，如果您部署在区分大小写的排序规则服务器上，并且您的脚本与字段/表名称的确切大小写不匹配，则会出现大量错误。

Answer 2

您是否尝试过使用 GUID 而不是自动增量作为唯一标识符？

Answer 3

如果您有能力修改获取多个记录的工作，我会改变想法，使您的计数器成为身份列。然后，当您获取下一条记录时，您可以执行插入并获取表的@@identity。这将确保您获得最大的数字。当您想要重置身份时，您还必须执行 dbccReseed 来重置计数器，而不仅仅是更新表。唯一的问题是，作为 sql 作业的一部分，您必须执行 100 次左右的插入才能获取一组身份。这可能开销太大，但使用标识列是获取唯一数字的有保证的方法。

Answer 4

我可能遗漏了一些东西，但看起来你正在尝试重新发明大多数数据库已经解决的技术。

为什么不为计数器使用自动增量主键，而不是从 Number_Setup 表中的“计数器”列读取和更新呢？主键永远不会有重复值。