SQL Server - 实现序列 [英] SQL Server - Implementing sequences

问题描述

我有一个系统，它要求我的数据在进入数据库之前具有 ID.我正在使用 GUID，但发现它们太大而无法证明其便利性.

I have a system which requires I have IDs on my data before it goes to the database. I was using GUIDs, but found them to be too big to justify the convenience.

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

I'm now experimenting with implementing a sequence generator which basically reserves a range of unique ID values for a given context. The code is as follows;

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

序列"表只是序列的 ID、名称(唯一)和最后分配的值.使用此过程，我可以请求命名序列中的 N 个值，并将它们用作我的标识符.

The 'Sequences' table is just an ID, Name (unique), and the last allocated value of the sequence. Using this procedure I can request N values in a named sequence and use these as my identifiers.

到目前为止效果很好 - 它非常快，因为我不必不断地要求单个值，我可以用完一系列值然后再请求更多.

This works great so far - it's extremely quick since I don't have to constantly ask for individual values, I can just use up a range of values and then request more.

问题是在极高的频率下，并发调用过程有时会导致死锁.我只在压力测试时发现这种情况发生，但我担心它会在生产中突然出现.这个程序有什么明显的缺陷，任何人都可以推荐任何改进它的方法吗?例如，在没有事务的情况下会很好，但我确实需要它是线程安全的".

The problem is that at extremely high frequency, calling the procedure concurrently can sometimes result in a deadlock. I have only found this to occur when stress testing, but I'm worried it'll crop up in production. Are there any notable flaws in this procedure, and can anyone recommend any way to improve on it? It would be nice to do with without transactions for example, but I do need this to be 'thread safe'.

推荐答案

我想我会分享我的解决方案.我不会死锁，也不会产生重复值.这和我原来的程序之间的一个重要区别是，如果队列不存在，它不会创建队列；

I thought I'd share my solution. I doesn't deadlock, nor does it produce duplicate values. An important difference between this and my original procedure is that it doesn't create the queue if it doesn't already exist;

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

这篇关于SQL Server - 实现序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！