SQL Server - Implementing sequences

Question

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.

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

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'.

Answer 1

MS themselves offer a solution and even they say it locks/deadlocks. If you want to add some lock hints then you'd reduce concurrency for your high loads

Options:

• You could develop against the "Denali" CTP which is the next release
• Use IDENTITY and the OUTPUT clause like everyone else
• Adopt/modify the solutions above

On DBA.SE there is "Emulate a TSQL sequence via a stored procedure": see dportas' answer which I think extends the MS solution.