SQL Server - 实现序列

Question

我有一个系统，需要在数据进入数据库之前为其分配ID。我曾经使用GUID，但发现它们过于庞大，无法证明这种方便性。

现在我正在尝试实现一个序列生成器，它基本上为给定上下文保留了一系列唯一的ID值。代码如下：

ALTER PROCEDURE [dbo].[Sequence.ReserveSequence]
@Name varchar(100),
@Count int,
@FirstValue bigint OUTPUT
AS
BEGIN
SET NOCOUNT ON;

-- Ensure the parameters are valid
IF (@Name IS NULL OR @Count IS NULL OR @Count < 0)
    RETURN -1;

-- Reserve the sequence
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION

    -- Get the sequence ID, and the last reserved value of the sequence
    DECLARE @SequenceID int;
    DECLARE @LastValue bigint;

    SELECT TOP 1 @SequenceID = [ID], @LastValue = [LastValue]
    FROM [dbo].[Sequences]
    WHERE [Name] = @Name;

    -- Ensure the sequence exists
    IF (@SequenceID IS NULL)
    BEGIN
        -- Create the new sequence
        INSERT INTO [dbo].[Sequences] ([Name], [LastValue])
        VALUES (@Name, @Count);

        -- The first reserved value of a sequence is 1
        SET @FirstValue = 1;
    END
    ELSE
    BEGIN
        -- Update the sequence
        UPDATE [dbo].[Sequences]
        SET [LastValue] = @LastValue + @Count
        WHERE [ID] = @SequenceID;

        -- The sequence start value will be the last previously reserved value + 1
        SET @FirstValue = @LastValue + 1;
    END

COMMIT TRANSACTION

END

“Sequences”表只包含ID、名称（唯一）和序列的最后一个分配值。使用此过程，我可以请求命名序列中的N个值，并将这些值用作标识符。

到目前为止，这个方法非常好用——它非常快速，因为我不必不断地请求单个值，而是可以使用一系列值，然后再请求更多。

问题在于，在极高的频率下并发调用该过程有时会导致死锁。我发现只有在压力测试时才会出现这种情况，但我担心它会在生产中出现。这个过程中是否存在任何明显的缺陷？有没有人能推荐任何改进方法？例如，不需要事务就可以完成，但我确实需要这个过程是“线程安全”的。

- Barguast

---

一般来说，这是一个不好的想法。请使用内置的“Identity”数据类型，否则您将需要手动处理锁定问题。 - JNK

为什么在数据进入数据库之前需要ID呢？您提出的解决方案只是预分配ID - 那么为什么不使用标识字段，创建空记录以创建ID，然后进行更新而不是插入呢？任何未使用的记录都可以被删除。 - Martin

@Martin - 简短的回答是这是关系型数据，我使用BULKCOPY将其插入到数据库中。我无法进行往返以获取ID，因此我需要事先知道它们是什么。 - Barguast

等待SQL Server 2011“Denali” - 它将支持SQL Server中的本地SEQUENCES。 - marc_s

如果您无法从批次 ID 和简单序列构建一个可以由您的批量插入管理的 ID，那就算了吧。如果您的批次 ID 无法在不调用数据库的情况下变得唯一，那么至少它只需要在处理开始时获取一次。 - Martin

Answer 1

微软公司自己提供了解决方案，甚至他们也说会出现锁定/死锁的情况。 如果您想添加一些锁提示，那么您将减少高负载下的并发性。

选项：

• 您可以使用“Denali” CTP进行开发，这是下一个版本
• 像其他人一样使用IDENTITY和OUTPUT子句
• 采用/修改上述解决方案

在DBA.SE上有一个“通过存储过程模拟TSQL序列”：请参见dportas的答案，我认为它扩展了微软的解决方案。

- gbn

Answer 2

我想分享我的解决方案。它不会死锁，也不会产生重复的值。与我的原始过程之间的一个重要区别是，如果队列不存在，它不会创建队列。

ALTER PROCEDURE [dbo].[ReserveSequence]
(
    @Name nvarchar(100),
    @Count int,
    @FirstValue bigint OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;

    IF (@Count <= 0)
    BEGIN
        SET @FirstValue = NULL;
        RETURN -1;
    END

    DECLARE @Result TABLE ([LastValue] bigint)

    -- Update the sequence last value, and get the previous one 
    UPDATE [Sequences]
    SET [LastValue] = [LastValue] + @Count
    OUTPUT INSERTED.LastValue INTO @Result
    WHERE [Name] = @Name;

    -- Select the first value
    SELECT TOP 1 @FirstValue = [LastValue] + 1 FROM @Result;
END

- Barguast

Answer 3

如果你说这主要是为了准备批量插入数据，我建议你坚持使用GUID（它比下面我提供的方法更简单）。

作为替代方案，你能否限制数量？比如每次只处理100个ID值？在这种情况下，你可以创建一个带有IDENTITY列的表，将数据插入该表中，返回生成的ID（例如39），然后你的代码可以分配所有值在3900到3999之间的值（例如乘以你假定的粒度），而无需再次查询数据库服务器。

当然，这也可以扩展到一次调用中分配多个ID，前提是你可以接受一些ID可能不被使用。例如，你需要638个ID，因此你请求数据库分配7个新的ID值（这意味着你已经分配了700个值），使用你需要的638个值，剩余的62个值则不会被分配。

- Damien_The_Unbeliever

Answer 4

你能获取某种死锁跟踪吗？例如，在此处启用跟踪标志1222。复制死锁。然后在SQL Server日志中查找死锁跟踪。

此外，您可以通过在COMMIT TRANSACTION之前立即插入对exec sp_lock或select * from sys.dm_tran_locks的调用来检查代码中使用的锁。

- Paul Williams

Answer 5

很可能您正在观察一个转换死锁。为了避免它们，您需要确保您的表已经聚集并且有一个主键，但是这个建议只适用于2005和2008 R2版本，而且它们的实现可能会改变，使得这个建议变得无用。请搜索“一些堆表比具有聚集索引的相同表更容易发生死锁”。

无论如何，如果您在压力测试期间观察到错误，很可能迟早也会在生产中发生。

您可能想使用sp_getapplock来序列化您的请求。请搜索“SQL Server 2005中的应用程序锁（或互斥锁）”。此外，我在这里描述了一些有用的想法：“开发能够在并发环境下生存的修改”。

- A-K