Emulate a TSQL sequence via a stored procedure

Question

I have a requirement to create a stored procedure which emulates a TSQL sequence. That is it always gives an increasing distinct integer value on every call. In addition, if an integer is passed in it should return that value if there has never been a result greater or the next highest integer available. It goes without saying there can be multiple clients calling this SP at the same time.

Given a table MetaInfo with columns MetaKey varchar(max) and MetaValueLong bigInt. It is expected the row with the MetaKey of 'Internal-ID-Last' will contain the last highest value assigned. I created the following stored procedure:

CREATE PROCEDURE [dbo].[uspGetNextID]
(
  @inID bigInt 
)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION

    UPDATE MetaInfo WITH (ROWLOCK) 
      SET MetaValueLong = CASE 
                            WHEN ISNULL(MetaValueLong,0) > @inID THEN MetaValueLong+1 
                            ELSE @inID+1
                          END 
    WHERE MetaKey = 'Internal-ID-Last'

    SELECT MetaValueLong 
    FROM MetaInfo
    WHERE MetaKey = 'Internal-ID-Last'

    COMMIT TRANSACTION 

END

My question is simply, does this stored procedure work as expected (all callers will be assigned a unique result)?

Answer 1

I've had a look and Microsoft themselves offer a solution without locks:

SQL Server Sequence Number

This is a simple update with no lock hints, but they say it locks/deadlocks.

Nothing much on SO about this either.

I'd be inclined to add UPDLOCK to your ROWLOCK (as per "table as a queue" (SO) but without READPAST). This will increase isolation in case a second process starts reading.

However, the fact all your processes want to read/write the same row makes me second guess myself. READPAST allows safe concurrency but in this case it's useless.

Note: you can use the OUTPUT clause instead of a second select then you don't need the transaction.

Answer 2

Following thing are missing

1. SET XACT_ABORT
2. Exception Handling (Try Catch)

Yes, It should meet your condition. Once such situations comes in transactions, It creates its multiple instances and subsequently, all callers will be assigned a unique result

Answer 3

A more scalable solution that doesn't require serialization is this:

CREATE PROCEDURE [dbo].[uspGetNextID]
(
  @inID BIGINT OUT
)
AS
      SET NOCOUNT ON
      SET IDENTITY_INSERT SequenceTable ON;
      INSERT INTO SequenceTable (id) VALUES (@inID);
      SET IDENTITY_INSERT SequenceTable OFF;
      INSERT INTO SequenceTable DEFAULT VALUES;
      DELETE FROM SequenceTable WITH (READPAST);
      SET @inID = SCOPE_IDENTITY();
RETURN;