How to Set & Get the Next ID Without Serializable Isolation

Say for some reason you can’t use identity columns or sequences, or maybe your application was built a long, long time ago – and you’ve got a table with a list of IDs to use next:

Whenever your application wants to insert a row, it needs to do two things: it needs to grab an ID for that table, and it needs to increment the CurrentID by one. This is a common design pattern I see with older applications that need to get an ID from the database, but then do some processing on the application side. For example, they want to reserve an OrderID, and then in the application code, they build a list of insert statements for not just the Order, but the line item tables as well.

One way to code this would be to use serializable isolation while you work, holding a transaction so that nobody can change the table while you’re working in it:

In case you haven’t seen output variables before for procs, here’s how you use them:

This stored procedure works, but it doesn’t scale well when you get into thousands of inserts per second, especially scattered across lots of tables. (And I’m simplifying here: in a real-world scenario, this kind of stored procedure would have some error checking built into it as well, especially given the blocking scenarios you can hit with serializable isolation levels.)

Here’s a quick improvement that doesn’t require ripping out the stored procedure and switching to identity columns or sequences:

This leverages the fact that you can both update data AND set variables during an update statement. With this trick, I don’t need to touch the Ids table multiple times, which means I don’t need a transaction, which means I don’t need serializable. Suddenly, this really opens the floodgates on concurrency with this table.

I still like identity columns & sequences better, though.

Want to learn more tricks like this?

If you like tricks like this, you’ll love my Mastering Query Tuning class. I’ve got two upcoming sessions: December 11-13 (Fri/Sat/Sun, iCal) and January 12-14 (Tues/Weds/Thurs- iCal.)

If you’ve got a Live Class Season Pass, you can drop in on this class or any of my live online classes at any time. Just head to my current training page, check out the schedule at the bottom of the page, and grab the calendar files for the classes you’re interested in. You don’t have to register ahead of time – just drop in anytime I’m streaming.

Previous Post
How to Insert Rows and Get Their Identity Values with the OUTPUT Clause
Next Post
When Do I Need to Use DESC in Indexes?

8 Comments. Leave new

  • You also could use a table variable with output, but that incurs additional tempdb space https://stackoverflow.com/questions/16847297/update-output-into-a-variable

    Reply
  • Is this reliable? I thought you couldn’t not guarantee the order of the columns being processed in the SET clause. If the use of OUTPUT is not an option I would recommend using the “quirky update” syntax…

    SET @Id = CurrentId = CurrentId + 1

    Reply
  • +1 Quirky update for the win

    Reply
  • A potentially trick of the syntax we often forget about.
    But as a use case, the Ids table is still a bottle-neck without serializable isolation. This was an issue at my first job. If the SQL version pre-dated sequences, I’d roll my own sequence with an identity table.

    CREATE TABLE OrderIDSequence (ID int IDENTITY (1, 1) NOT NULL, dummy int NULL)
    go;
    CREATE OR ALTER PROCEDURE GetNewID
    @id int OUTPUT
    AS
    DECLARE @RollbackWork int = 0;
    IF (@@TRANCOUNT = 0)
    BEGIN
    SET @RollbackWork = 1
    BEGIN TRANSACTION
    END

    SAVE TRANSACTION newID
    INSERT INTO OrderIDSequence (dummy) VALUES (null)
    SELECT @id = SCOPE_IDENTITY()

    IF (@RollbackWork = 1)
    ROLLBACK WORK
    ELSE
    ROLLBACK TRAN newID
    GO

    It’s been an age since I’ve done one of these, I feel old.

    Reply
    • Yes, and this is exactly how MS recommends emulating Sequence objects, before it had a Sequence object. They even have a whitepaper on it. As I recall though, the server needs to also run a specific trace flag for this implementation as well.

      Reply
  • Updating the variable with “trick” does not negate the need for the serialization/transaction. Moreover, in case proc fails then the variable value will be out of sync with the column value.

    Reply
    • you could add a BEGIN TRY / CATCH block and set the variable to NULL in an error case (as Deadlock). As Brent wrote, this is a simplified version of a real procedure (and of course, you may still face problems with blockings)

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu