17

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

1

3 Answers 3

8

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.

0
0

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

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

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;
4
  • 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)
    – Hogan
    Jun 16, 2011 at 13:18
  • 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.
    – nvogel
    Jun 16, 2011 at 13:30
  • 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.
    – Hogan
    Jun 20, 2011 at 23:54
  • @Hogan, IDENTITY_INSERT has local scope so there is no race condition when that happens.
    – nvogel
    Jun 21, 2011 at 5:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.