Atomic Increment without use of transactions

Question

I'm trying to find a way to have atomic increment without transaction usage. I have the following table

CREATE TABLE [dbo].[DocumentCategoryInternalId]
(
    [Category] [nvarchar](450) NOT NULL,
    [Id] [bigint] NOT NULL,

    CONSTRAINT [PK_DocumentCategoryInternalId] 
        PRIMARY KEY CLUSTERED ([Category] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

This table holds a counter per document category (PK/FK relations with Category table is omitted).

Application code needs to get the next value for every document created in a category (like in sql sequences. Not related to the question, but I'm considering change to use sequences to achieve this).

Existing code that currently implements atomic increment is the following

UPDATE [DocumentCategoryInternalId] 
SET [Id] = [Id] + 1 
OUTPUT INSERTED.* 
WHERE Category = 'Category 1'

My understanding is that this is not correct. P1 & P2 processes can read the same value, so after both updates Id will remain as N + 1 (where N is initial value both P1 & P2 read). Whereas expected result should be N + 2.

In an attempt to guarantee atomic increment I came up with the following but it looks cumbersome

DECLARE @initialId BIGINT

INCREMENT_ID:
    SELECT @initialId = Id
    FROM [DocumentCategoryInternalId]
    WHERE Category = 'Category 1'   

    UPDATE [DocumentCategoryInternalId] 
    SET [Id] = [Id] + 1 
    OUTPUT INSERTED.* 
    WHERE Category = 'Category 1' and Id = @initialId

IF(@@ROWCOUNT = 0)
BEGIN
    GOTO INCREMENT_ID
END

So my question is, is there any way (better than what I have) to implement atomic increments without using transactions.

Atomic Increment without use of transactions

0 Answers