Best code for generating sequence numbers

  • jaffar.yelavalli

    Old Hand

    Points: 343

    Hi All,

    We have a requirement where we need to generate IDs from a table.

    For each kind of ID we have a row defined with a name.

    ex Table:

    IDName next_value

    ShipID 1

    PackingID 1

    What would be best code sequence where we would end up with no deadlocks, blockings or duplicates IDs.

    Tweaking of code I am looking for is:

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    BEGIN TRAN

    select @nextvalue = next_value

    from table_num_scheme where name = 'ShipID'

    update table_num_scheme set next_value = (next_value + 1) where name = 'ShipID'

    COMMIT TRAN GRAB_SHP_NBR

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    Any other suggestions to generate the sequence numbers from the same row.

  • Lynn Pettis

    SSC Guru

    Points: 442118

    How about something like this?

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    BEGIN TRAN

    update table_num_schema set

    @nextvalue = next_value = next_value + 1;

    where

    name = 'ShipID'

    COMMIT TRAN GRAB_SHP_NBR

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    set @nextvalue = @nextvalue - 1;

  • jaffar.yelavalli

    Old Hand

    Points: 343

    That seems great.

    I tried the same but with default read committed isolation and ended up with duplicates.

    I am quite unable to understand why I am running into duplicates even though I am doing everything in an atomic transaction:

    Code I already tried is:

    Begin Tran

    UPDATE table_num_scheme SET @nextvalue = next_value = (next_value + 1) WHERE name = 'Ship ID'

    Commit tran

    Begin Tran

    UPDATE table_num_scheme with(rowlock, updlock) SET @nextvalue = next_value = (next_value + 1) WHERE name = 'Ship ID'

    Commit tran

    I haven't tried with serialization though.

  • Tergum Rufus

    SSChasing Mays

    Points: 651

    5th Feb 2010

    Lynn,

    We have a mission critical app which has this problem in the very guts of the business logic and is causing deadlocks & major disruption on a 50 user installation. I am not a DBA but it appears that your tuning suggestion removes a read and thus halves the disk activity. I would also like to understand better the implications and effect of the two statements regarding transaction isolation levels and how the changes proposed below might affect our application.

    I believe we could make an immediate improvement by just modifying our stored procedure as indicated by the lines below marked **:

    ALTER PROCEDURE [dbo].[sp_nxnumber]

    @Result Int output,

    @NumberType Varchar(20)

    AS

    **SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    BEGIN TRANSACTION

    UPDATE nxno WITH (tablockx) SET NxNumber = NxNumber + 1 WHERE numberType = @NumberType

    IF @@error <> 0

    BEGIN

    ROLLBACK TRANSACTION

    RAISERROR ('NxNumber Update Error', 16, 2) WITH NOWAIT, SETERROR

    RETURN 0

    END

    ** REMOVE NEXT LINE

    -- SET @Result = (Select NxNumber FROM NxNo WITH (tablockx) WHERE NumberType = @NumberType)

    COMMIT TRANSACTION

    **SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    **SET @Result = NxNumber

    ANY OTHER COMMENTS WELCOME

    Peter HORSLEY

    Melbourne, Australia

  • Lamprey13

    SSCertifiable

    Points: 6190

    Admittedly, I’m not sure I fully understand the problem. But, the simple solution seems to be to use a IDENTITY column. Perhaps you need a different table for you “Numbers.” So if you Insert into that table you capture the newly created IDENTITY number and use that. It’s quick and sql handles the locking for you.

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    If you really need to do something like this, you don't need to worry about transactions if you use the OUTPUT clause of the UPDATE statement to return the new value.

    Also, you will reduce the posibility of deadlocking if you have one table for each table you are keeping a sequence number for, instead of having one table for all of them.

    As mentioned before, IDENTITY is a much better way to handle this.

    declare @MyOut table ( next_value int )

    update table_num_scheme

    set

    next_value = next_value + 1

    output

    inserted.next_value

    into

    @MyOut

    where

    name = 'ShipID'

    select next_value from @MyOut

  • Tergum Rufus

    SSChasing Mays

    Points: 651

    Thank you for the feedback

    Agreed, the IDENTITY column works fine for creating a unique RECNUM ID, but this type of process is required when creating a 'unique' sequential number that will link multiple records, possible in multiple tables, such as an INVOICE, EVENT or DOCUMENT number.

    Really my question is about when one is obliged to use this technique, what is the optimum way to do it to maximise concurrency performance and avoid deadlocks. ie minimise disk activity and lock time. Using a separate table for each entity is a bit clunky because it would create a large record set that would need regular truncation.

    In summary:

    What are the relative merits of handling the transaction ISOLATION LEVEL and/or the TABLOCKX, HOLDLOCK query Optimiser Hint at the SP level? Are they the same thing?

    Best wishes,

    Peter

  • Jeff Moden

    SSC Guru

    Points: 994239

    peter-970097 (2/5/2010)


    Thank you for the feedback

    Agreed, the IDENTITY column works fine for creating a unique RECNUM ID, but this type of process is required when creating a 'unique' sequential number that will link multiple records, possible in multiple tables, such as an INVOICE, EVENT or DOCUMENT number.

    Really my question is about when one is obliged to use this technique, what is the optimum way to do it to maximise concurrency performance and avoid deadlocks. ie minimise disk activity and lock time. Using a separate table for each entity is a bit clunky because it would create a large record set that would need regular truncation.

    In summary:

    What are the relative merits of handling the transaction ISOLATION LEVEL and/or the TABLOCKX, HOLDLOCK query Optimiser Hint at the SP level? Are they the same thing?

    Best wishes,

    Peter

    If you remove all ISOLATION LEVEL commands and remove the explicit BEGIN TRAN/COMMIT from Lynn's code, 90% of your deadlocks will simply vanish. You'll need to move usage of the proc outside of any external transactions to get rid of the 10%.

    So far as when to use something like this "sequence table" method in SQL Server goes, my answer would be "almost never". Improper design and use of a similar function caused an average of 640 deadlocks per day with spikes to 4,000 per day at a previous company I worked for. Yes, the new correctly written function solved most of that but even the new function which would allow you to "reserve" a given "increment" of numbers was a pain to use and, done incorrectly, would result in duplication of some IDs.

    Here's the code that my old DBA and I came up with. The day we replaced the original code with the following code, the deadlocks dropped for about 640 to 12. Most of the deadlocks that remained were from other sources. I also have to tell you that all of the error checking we built into the code is ridiculous because the core code has never failed since we installed it. Since it takes virtually no time to do those error checks, we left them in.

    If you can't figure out how to use the code to do setbased updates using the increment value instead of using a loop to do just one row at a time, please post back because it's an important concept to using such (ugh!) sequence tables in a setbased manner.

    CREATE PROCEDURE dbo.GetNextID

    /****************************************************************************************

    Purpose:

    This stored procedure is used to get a NextID for the table identified by the @KeyID

    parameter. It will "reserve" a block of ID's according to the @IncrementValue parameter.

    The @NextID returned is always the first ID of a reserved block of numbers. The reserved

    block size defaults to 1.

    Usage:

    EXEC @return = dbo.GetNextID @KeyID,@IncrementValue,@NextID=@NextID OUTPUT

    Outputs:

    1. Returns a -1 if error and 0 if success.

    2. @NextID will be a -1 if an error occured. Otherwise, it will contain the first

    NextID of the requested block of NextID's.

    Notes:

    1. This procedure has been enhanced compared to the original...

    a. The UPDATE statement sets both the @NextID variable and the NextID column in the

    NextID table eliminating the need for a separate SELECT from NextID after the

    UPDATE.

    b. Because of (1.a) above, there is no longer a need for a transaction. If the

    UPDATE didn't work, there is no need for a ROLLBACK because nothing was updated.

    c. Previous error handling did not correctly return the invalid KeyID if present.

    d. A test has been added to ensure a negative value for @IncrementValue was not

    passed in.

    e. A test to ensure that @NextID was correctly updated has been added.

    f. Repairs to the previous error routines have been made so that the values returned

    to @@ERROR and @@ROWCOUNT are correctly used by more than one statement.

    Revisions:

    REV 01 - 01 Mar 2005 - Kalpa Shah, Jeff Moden --Rewrite original

    REV 02 - 06 Feb 2010 - Jeff Moden -- Removed all company references

    ****************************************************************************************/

    --=======================================================================================

    -- Define the I/O parameters used by this procedure

    --=======================================================================================

    --===== Declare the passed parameters

    @KeyID INTEGER, --Identifies table to get the NextID for

    @IncrementValue INTEGER = 1, --Number of NextIDs to "reserve"

    @NextID INTEGER OUTPUT --Returns start # of block of IDs

    AS

    --=======================================================================================

    -- Main body of procedure

    --=======================================================================================

    --===== Suppress auto-display of row counts for appearance and speed

    SET NOCOUNT ON

    --===== Declare variables local to the loop

    DECLARE @MyError INTEGER --Holds @@ERROR for additional processing

    DECLARE @ErrMessage VARCHAR(100) --Holds calculated error messages because RaisError

    --cannot calulate messages on the fly.

    DECLARE @MyRowCount INTEGER --Hold @@ROWCOUNT for additional processing

    --===== Preset @NextID to an error condition

    SET @NextID = -1 --Defaults don't work consistently on OUTPUT parameters

    --===== If the increment is not greater than zero, raise and error and exit immediately

    IF @IncrementValue <= 0

    BEGIN --Start of error processing

    --===== Process errors (RaisError cannot do calcs for error message)

    SET @ErrMessage = 'The NextID row could not be updated. '

    + 'Increment was set to '

    + CONVERT(VARCHAR(11),@IncrementValue) + '.'

    RAISERROR (@ErrMessage,1,1)

    RETURN -1 --Returns an error indication to calling procedure

    END --End of error processing

    --===== Update the correct NextID row according to the KeyID passed in.

    -- Sets @NextID and the column to the previous value + the increment

    -- simultaneously so we don't need to read from the NextID table to

    -- get the value of @NextID in the following steps.

    UPDATE dbo.NextID WITH (UPDLOCK)

    SET @NextID = NextID = NextID + @IncrementValue

    WHERE KeyID = @KeyID

    -- Get the error value and rowcount

    SELECT @MyError = @@ERROR, @MyRowCount = @@ROWCOUNT

    --===== Check for errors, a rowcount of 1, and a non-default value for @NextID

    IF @MyError <> 0 --An error did occur

    OR @MyRowCount <> 1 --The row was not updated

    OR @NextID = -1 --A new value for @NextID was not returned

    BEGIN --Start of error processing

    --===== Process errors (RaisError cannot do calcs for error message)

    IF @MyError <> 0 --Error occured

    SET @ErrMessage = 'The NextID row could not be updated.'

    ELSE --1 row or @NextID was not not updated

    SET @ErrMessage = 'The NextID row could not be updated. KeyID '

    + CONVERT(VARCHAR(11),@KeyID)

    + ' may not exist.'

    RAISERROR (@ErrMessage,1,1)

    RETURN -1 --Returns an error indication to calling procedure

    END --End of error processing

    --===== Calculate and return the first number in the block of reserved NextID's

    -- to the @NextID output parameter

    SELECT @NextID = @NextID - @IncrementValue

    --===== Return a "success" indication to the calling procedure

    RETURN 0

    GO

    To reiterate, using sequence tables in SQL Server just isn't the right thing to do and it took me a lot to say it that nicely ;-).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • SQLRNNR

    SSC Guru

    Points: 281210

    Would a sequential guid work in place of an int?

    It should be less of problematic and it doesn't necessarily need to be the primary key (it could be a unique key and used as a foreign key as well).

    http://sqlblogcasts.com/blogs/martinbell/archive/2009/05/25/GUID-Ordering-in-SQL-Server.aspx

    http://developmenttips.blogspot.com/2008/03/generate-sequential-guids-for-sql.html

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Tergum Rufus

    SSChasing Mays

    Points: 651

    Thanks Jeff for the informative reply.

    Independently of this blog, we very quickly canned any further thought on ISOLATION LEVEL et aliter

    All the tests we did with this were catastrophic!

    We will work on your suggestion and blog the outcome.

    Rgds

    Peter

  • Jeff Moden

    SSC Guru

    Points: 994239

    peter-970097 (2/6/2010)


    Thanks Jeff for the informative reply.

    Independently of this blog, we very quickly canned any further thought on ISOLATION LEVEL et aliter

    All the tests we did with this were catastrophic!

    We will work on your suggestion and blog the outcome.

    Rgds

    Peter

    Thanks for the feedback, Peter. I look forward to your follow up especially since I went through it once myself. Let us know if we can be of any further assistance.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Paul White

    SSC Guru

    Points: 150341

    CirquedeSQLeil (2/6/2010)


    Would a sequential guid work in place of an int?

    Yes, but they have problems of their own. Quite apart from the issues of key width and getting the darn things in some sort of order so your system doesn't disappear in a puff of page-splits, they're horrible to work with. I mean that in a practical sense. Any DBA that has had to use GUIDs a lot gets plenty of practice with copy and paste 😉 It's also tedious typing UNIQUEIDENTIFIER all the time - how much easier is 'INT'?

    These may sound like small objections, and they probably are. I could go on about the technical weaknesses of 'ordered' GUIDs for a while, but I will try to resist the temptation. The everyday irritations are the thing that finally turned me off to GUIDs.

    Jeff knows what's coming next.

    Sequence Tables are a pretty perfect solution for the sort of problem that prompted this thread - if implemented correctly. This was nigh-on impossible to do in SQL Server 2000, but we've had SQL Server 2005 for a while now 😀

    I have written an article on Sequence Tables which I hope will be published here on SSC shortly. Jeff is one of the technical reviewers 😉

    BTW Jason, the second link in your post is now broken 🙁

    Paul

  • RBarryYoung

    SSC Guru

    Points: 143327

    Paul: So on RollBacks, will it leave permanent gaps, try to recycle the gaps, or does it guarantee no gaps, ever? (I am pretty sure that this last choice cannot be done, by anything, without some kind of frequent deadlocking or severe blocking). 😀

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Paul White

    SSC Guru

    Points: 150341

    RBarryYoung (2/28/2010)


    Paul: So on RollBacks, will it leave permanent gaps, try to recycle the gaps, or does it guarantee no gaps, ever? (I am pretty sure that this last choice cannot be done, by anything, without some kind of frequent deadlocking or severe blocking). 😀

    Like IDENTITY, it leaves gaps on ROLLBACK - as you say, all non-blocking methods do.

    It is, however, possible to avoid gaps if a 'pool' of pre-allocated keys is cached and managed by a separate component.

    That is an implementation detail to be handled by the application.

    Paul

  • RBarryYoung

    SSC Guru

    Points: 143327

    Cool. That would be my choice, simpler.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 15 posts - 1 through 15 (of 41 total)

You must be logged in to reply to this topic. Login to reply