Generating Unique ID on Server

  • I'm fairly new to using SQL Server and figure there's probably a more elegant way to write this store procedure.  What I need is a stored procedure that can be accessed by many test systems that will send their data to a central server.   I'd like to be able to arbitrarily set the starting value of the sessionID.

    GlobalSessionID is a one column, one row table used to generate unique session ID's.

    The first select statement locks the table for the duration of the transacdtion. The update statement increments the ID.  The second select statement returns the value; the caller needs to fetch the value after executing the stored procedure.

    If anyone has suggestions on how this "should" be done or if there is a simpler solution I'd appreciate your thoughts.

    Thanks for the help.

    Fred

    =================================================

    CREATE PROCEDURE dbo.PROC_GetSessionID

    AS

    DECLARE @dummy as bigint

    BEGIN TRANSACTION

    select @dummy = SessionID from GlobalSessionID with (TABLOCK, HOLDLOCK)

    UPDATE GlobalSessionID set SessionID =

        (SELECT SessionID + 1 FROM GlobalSessionID)

    SELECT SessionID

    FROM GlobalSessionID

    COMMIT TRANSACTION

    GO

  • A third party company that we had the great misfortune of hiring, did something similar (nearly identical) in our database... it caused an average of 620 deadlocks a day.  The normal goal for deadlocks is, of course, 0.  Obviously, we fixed the bloody thing...

    If you don't want to use 36 character SessionID's that could be created by the use of NEWID(), then try this...

    Create your session id table like this...

    CREATE TABLE dbo.GlobalSessionID
    (
    SessionID INT IDENTITY(1,1),
    Dummy CHAR(1)
    )

    Then, create a proc that looks like this...

    CREATE PROCEDURE dbo.PROC_GetSessionID AS
    INSERT INTO dbo.GlobalSessionID (Dummy) VALUES ('X')
    SELECT SCOPE_IDENTITY
    RETURN

    Every record inserted will create a new auto-numbering sequential SessionID in the record created.  Selecting the SCOPE_IDENTITY will return that number regardless of any triggers firing or other tables being modified.  It is session sensitive and requires no table locking except during the microsecond to do the insert.

    Yes, the table will grow so you may want to consider using DECIMAL(19,0) (takes same number of bytes as DECIMAL(10,0)) or BIGINT for the datatype of the SessionID.  You may want to also consider deleting earlier records from the table on a regular basis but, before you do, add a datetime column to the table and default it to GETDATE() to create a historical record of when each SessionID was created (has helped me twice with FBI investigations).

     

    --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.

    Change is inevitable... Change for the better is not.


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

  • Jeff,

    Thanks for the help.  A colleague of mine wasn't too excited about maintaining the table, having to clean up rows that have been inserted.  A 36 char ID is too big since we'll have lots of rows.

    Do you think this may pose deadlock problems?  This should be the only stored procedure or even script for that matter that would ever access this table.

    Any other ideas?

    I do appreciate your help.

    Fred

  • Another clean way of doing it would be to use a Base-36 number (zero to Z).  Use a function to create it and default the value of the column to the function.

    10 characters will handle almost 4,000,000,000,000 rows of data.

  • In 7 years, this never deadlocked and it always retrieves a unique next number.  This is just an exerpt of a function, but it finds a unique @iBatch (integer batch sequence #) in a table called batch.  The key technique is to get the next iBatch:

        SELECT @iBatch = 1 +

         COALESCE((SELECT MAX(iBatch) FROM Batch),0)

    Assuming you have a unique constraint on the iBatch #, when you add your new record, you check to see if it worked

        INSERT Batch (jBatchPeriod, iBatch, MachineId, idUser, jStart, idJob, Lock, BatchTicketMicr)

          VALUES (@jBatchPeriod, @iBatch, @szMachineId, @idUser, @jStart, @idJob, @jstart, @szBatchTicketMicr)

        IF @@ERROR = 0 BEGIN -- hey it worked, so we can return the identity column of the record added

          SET @idBatch = @@IDENTITY

          BREAK

        END

        IF @@ERROR <> 2601 -- I think this was "constraint violated" or something.

          BREAK -- real bad error, trying again probably wont work

    If it failed, wait a couple ms and then try again.  Failure would only occur when some other process called this stored proc simultaneously.

        SET @cLoop = RAND() * 50

        WHILE @cLoop > 0

          SET @cLoop = @cLoop - 1

        SET @iTry = @iTry + 1

      END  -- loop

    Here is the code block taken all at once:

      SET @iTry = 0

      -- loop to find unused iBatch

      WHILE @iTry < 5 BEGIN

        SELECT @iBatch = 1 +

         COALESCE((SELECT MAX(iBatch) FROM Batch),0)

       

        INSERT Batch (jBatchPeriod, iBatch, MachineId, idUser, jStart, idJob, Lock, BatchTicketMicr)

          VALUES (@jBatchPeriod, @iBatch, @szMachineId, @idUser, @jStart, @idJob, @jstart, @szBatchTicketMicr)

        IF @@ERROR = 0 BEGIN

          SET @idBatch = @@IDENTITY

          BREAK

        END

        IF @@ERROR <> 2601

          BREAK

       

        SET @cLoop = RAND() * 50

        WHILE @cLoop > 0

          SET @cLoop = @cLoop - 1

        SET @iTry = @iTry + 1

      END

      -- we are done

      

      IF @@ERROR <> 0

        RAISERROR ('ERROR - Could not begin a new batch.', 16, 1)

      ELSE

        SELECT * FROM Batch WHERE idBatch = @idBatch

     

  • Following sample generates uniqueid but does not store in a table.

    DECLARE @IntTransactionID bigint, --Set @IntTransactionID@dteCurrentDate datetime 

    SET @dteCurrentDate = GETDATE()

    --Genreate unique transaction id

    SET @IntTransactionID = CAST(CAST(@@SPID AS VARCHAR(10)) +

                            CAST(DatePart(dd,@dteCurrentDate)AS VARCHAR(2)) +

                           CAST(DatePart(mm,@dteCurrentDate) AS VARCHAR(2)) +

                          CAST(DatePart(YY,@dteCurrentDate) AS VARCHAR(4)) +

                          CAST(DatePart(hh,@dteCurrentDate) AS VARCHAR(2)) +

                          CAST(DatePart(mi,@dteCurrentDate) AS VARCHAR(2)) +

                          CAST(DatePart(ss,@dteCurrentDate) AS VARCHAR(2)) +

                          CAST(DatePart(MS,@dteCurrentDate) AS VARCHAR(3)) AS BIGINT)

     

  • > Do you think this may pose deadlock problems?  This should be the only stored procedure or even script for that matter that would ever access this table.

    Fred,

    Yeah, your method could cause a deadlock depending on how it is used and how many people try to hit it and whether or not you use BEGIN TRANSACTION with the stuff that calls it.  On the other hand, you may never see a deadlock if you never call it from a long running transaction because your method runs very very fast.  I guess you could try it to see.

    The advantage of the method I presented is the table doesn't take much space, can be used for history, and will never deadlock.  Since it doesn't take much space, you really don't need to do the maintenance of deletions.  If the boss really does want it to stay short, a very simple scheduled job could be set up.

    The MAX+1 idea is a good one but you wanted to use a single record so it won't work for you.  Besides, if the table grew go each Session ID, you wouldn't need MAX+1... you could use an Identity field, instead.

    The date conversion above is a great idea but, be warned, although highly unlikely because of the millisecond listing, it is still possible to get a dupe if you have a lot of people asking for a Session ID at the same time.

    And, to reiterate, if you use the NEW(ID) method, although 36 characters long, you will never get a dupe and, since you don't need the Session ID table, you will never have a deadlock.  Since the 36 characters are hexidecimal in nature, you could do a conversion (kind of like the date method above) to decimal in each section of the ID and concatenate the pieces together to make a smaller more managable Sessiond ID.

    Anyway, lot's of folks gave some pretty darned good answers above... I guess all you need to do now is make a choice.

    --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.

    Change is inevitable... Change for the better is not.


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

  • Jeff,

    THe date approach as suggested above also takes SPID into consideration, which is suppose to be unique for any number of concurrent connected users to the database. Hence at any given point of time no two users with the same SPID can access the database. Therefore the ID generated suppose to be unique. Please do correct me  if I am wrong.

     

    Thanks

    nskr72

     

  • Thanks nskr72... I missed that.

    --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.

    Change is inevitable... Change for the better is not.


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

Viewing 9 posts - 1 through 8 (of 8 total)

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