Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Jun 14 '11 at 15:49

This question came from our site for professional and enthusiast programmers.

    
@all: FYI, spawned by this Q on SO: stackoverflow.com/q/6342732/27535 –  gbn Jun 14 '11 at 16:32

3 Answers 3

up vote 5 down vote accepted

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

http://blogs.msdn.com/b/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx

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 2nd 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 2nd select then you don't need the transaction.

HTH...

share|improve this answer
1  
You beat me to it. Note that SQL Server 2011 includes SEQUENCE functionality so the requirement to invent your own should go away soon (not before time). –  sqlvogel Jun 14 '11 at 16:36
    
@dportas: indeed. And runs better too: dba.stackexchange.com/q/1635/630 –  gbn Jun 14 '11 at 16:38
    
@dportas - can SEQUENCE allow for input requirement? In my quick read of the feature I did not see that functionality. –  Hogan Jun 14 '11 at 17:16
    
+1 for the MS links and OUTPUT point. (Sadly the MS links don't seem to apply 100%). –  Hogan Jun 14 '11 at 17:25

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

share|improve this answer
    
If I commit before the select isn't there a possibility I will select the result saved by a different call? –  Hogan Jun 14 '11 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 '11 at 15:45
    
+1 for SET XACT_ABORT –  Hogan Jun 14 '11 at 17:24

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;
share|improve this answer
    
OP has a requirement to allow users to pass in a value which complicates it... –  gbn Jun 14 '11 at 16:31
    
@dportas - Thanks. I was aware of this approach, but it does not work here because of the input parameter. –  Hogan Jun 14 '11 at 17:22
    
@Hogan, I've modified my suggestion to handle the input parameter. It's mostly untested though. –  sqlvogel Jun 14 '11 at 17:29
    
@dportas - A called with inID of 500, B called with inID of 50 -- action - A returns with 51, B with 52. Requirement fail. –  Hogan Jun 14 '11 at 17:34
    
Interesting. I get different results (on 2008r2). Can you post the full repro with DDL and state your build/version. –  sqlvogel Jun 14 '11 at 17:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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