How to Set & Get the Next ID Without Serializable Isolation

Last Updated 3 years ago

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:

DROP TABLE IF EXISTS dbo.Ids;

CREATE TABLE dbo.Ids
	(TableName NVARCHAR(255) PRIMARY KEY CLUSTERED,
	 CurrentId BIGINT);

INSERT INTO dbo.Ids (TableName, CurrentId)
	VALUES (N'Customers', 1000);
GO

DROP TABLE IF EXISTS dbo.Ids;

CREATE TABLE dbo.Ids

(TableName NVARCHAR(255) PRIMARY KEY CLUSTERED,

CurrentId BIGINT);

INSERT INTO dbo.Ids (TableName, CurrentId)

VALUES (N'Customers', 1000);

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:

CREATE OR ALTER PROC dbo.GetNextId 
	@TableName NVARCHAR(255), @Id INT OUTPUT AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

    UPDATE dbo.Ids SET CurrentId = CurrentId + 1 
		WHERE TableName = @TableName;

    SELECT @Id = CurrentId 
		FROM dbo.Ids 
		WHERE TableName = @TableName;

COMMIT;
END
GO

CREATE OR ALTER PROC dbo.GetNextId

@TableName NVARCHAR(255), @Id INT OUTPUT AS

BEGIN

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

UPDATE dbo.Ids SET CurrentId = CurrentId + 1

WHERE TableName = @TableName;

SELECT @Id = CurrentId

FROM dbo.Ids

WHERE TableName = @TableName;

COMMIT;

END

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

DECLARE @MyID INT = NULL;
EXEC GetNextId 
	@TableName = N'Customers', 
	@Id = @MyID OUTPUT;
SELECT @MyID;

DECLARE @MyID INT = NULL;

EXEC GetNextId

@TableName = N'Customers',

@Id = @MyID OUTPUT;

SELECT @MyID;

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:

CREATE OR ALTER PROC dbo.GetNextId 
	@TableName NVARCHAR(255), @Id INT OUTPUT AS
BEGIN

    UPDATE dbo.Ids 
		SET CurrentId = CurrentId + 1,
		    @Id = CurrentId + 1
		WHERE TableName = @TableName;

END
GO

CREATE OR ALTER PROC dbo.GetNextId

@TableName NVARCHAR(255), @Id INT OUTPUT AS

BEGIN

UPDATE dbo.Ids

SET CurrentId = CurrentId + 1,

@Id = CurrentId + 1

WHERE TableName = @TableName;

END

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.

How to Insert Rows and Get Their Identity Values with the OUTPUT Clause

When Do I Need to Use DESC in Indexes?

8 Comments. Leave new

Mordechai
November 25, 2020 9:09 am

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
Tony Fountain
November 25, 2020 10:10 am

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
- andreas.thell
  November 25, 2020 12:36 pm
  
  +1 on this. I’ve used this for years without issues.
  
  Reply
S morris
November 25, 2020 12:47 pm

+1 Quirky update for the win

Reply
Recce
November 25, 2020 2:29 pm

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
- Tony Fountain
  November 25, 2020 5:52 pm
  
  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
Joel Mamedov
November 25, 2020 4:18 pm

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
- Thomas Franz
  November 26, 2020 1:03 am
  
  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