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)?

• gbn: @all: FYI, spawned by this Q on SO: stackoverflow.com/q/6342732/27535

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

• Hogan: If I commit before the select isn't there a possibility I will select the result saved by a different call?
• SQL: Not sure about it. But seems like you are right. I need to check. Thanks. BTW +1 for, Good Question...

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;

• Hogan: Let me try and be clearer -- my question is this; If this is run in a multi-client environment and the SP is called at the same time with two clients A w/ param 50 and B w/ param 500, B runs the first 4 lines first (setting ID to 500), then A runs the first 4 lines (setting ID to 50), then B finishes the last 3 lines (getting a result of 51) and then A finishes (getting a result of 52). This does not meet the requirements -- B returned a result less than 500. Correct results would be (A=51, B=501) or (A=502, B=501)
• nvogel: In the circumstances you describe B would get 501, A would get 502, which you say is correct. The bit you got wrong is where you say "A runs the first 4 lines (setting ID to 50)" That could not happen because the IDENTITY property doesn't allow the current IDENTITY value to be rolled back, even using IDENTITY_INSERT. The highest number of the identity sequence is always preserved. It's easy enough to try it and confirm that yourself.
• Hogan: Ah very good, I didn't know Identity insert did that. So consider this: A runs the first two lines, B runs 4 lines; now identity insert is off. A runs and inserts 50. A and B run to completion with values 50 and 51.
• nvogel: @Hogan, IDENTITY_INSERT has local scope so there is no race condition when that happens.