M
Mike
Guest
Mike Asks: Atomic Increment without use of transactions
I'm trying to find a way to have atomic increment without transaction usage. I have the following table
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
My understanding is that this is not correct. P1 & P2 processes can read the same value, so after both updates Id will remain as
In an attempt to guarantee atomic increment I came up with the following but it looks cumbersome
So my question is, is there any way (better than what I have) to implement atomic increments without using transactions.
I'm trying to find a way to have atomic increment without transaction usage. I have the following table
Code:
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
Code:
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
Code:
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.
SolveForum.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your thoughts here to help others.