Concurrency issues with Sequence in MS SQL Server?

Question

I have a sequence in my SQL Server database

CREATE SEQUENCE [dbo].[UserPKSequence] 
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE -2147483648
MAXVALUE 2147483647
NO CACHE 
GO

I am using this sequence on a table (lets name the table Users) that has a Primary Key (lets name that UserId) which is not an IDENTITY column. I am using my sequence as the DEFAULT for UserId, as shown below:

CREATE TABLE [dbo].[Users]
(
    [UserId] [INT] NOT NULL 
         CONSTRAINT [DF_User_UserId] DEFAULT (NEXT VALUE FOR [UserPKSequence]),
    [Email] [VARCHAR](254) NULL
    -- other columns defined for the table

    CONSTRAINT [pkUsers] 
        PRIMARY KEY CLUSTERED ([UserId] ASC)
) ON [PRIMARY]

The database is used by 2 different applications where both applications can insert data into this table, using the mentioned sequence. Can it cause any concurrency issues? Can one application can get the UserId generated by the other application - in case of concurrent requests ? if yes, then how? and what can I do to prevent ? I can not make UserId IDENTITY.

Also when a new row is inserted in the table, a trigger is fired which returns the newly generated UserId to one of the applications. Can that be problematic?

You will not get duplicate values from a sequence, unless you specify `CYCLE`, regardless of the number of users. — Dan Guzman, Oct 12 '18 at 12:37
Rather than use a trigger to obtain the new value, why not just use the `OUTPUT` clause? — Damien_The_Unbeliever, Oct 12 '18 at 14:23
that causes problems with the first app on which we do not have any control. That application is using Max + 1 approach. — Farooq Hanif, Oct 12 '18 at 17:55

Concurrency issues with Sequence in MS SQL Server?

0 Answers0