Conceptually we have a table with a set of identifiers that are a four digit base number and two digit sequence number. Every year this table is cleared. Currently to get a new base number or a new sequence number a read of the table is made followed by an insert. e.g. Read getting the max base number, increment base by 1, insert it. The same sort of thing happens for sequence number for a given base number. This is a low volume, low creation table. Last year there were 4,392 rows in the table.

But... we managed to have conflict, two people getting a new sequence got the same one, which can obviously happen.

I created a test table and came up with this stored procedure to see if we can alleviate the possibility of conflict. In the table there is only one base number. I am a DB rookie and pieced this together from a day spent with Dr. Google.

Thoughts please. Thanks.

Code:
USE [LRTEST]
GO
/****** Object:  StoredProcedure [revision].[NewSeq]    Script Date: 6/8/2022 11:24:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [revision].[NewSeq]
       -- Add the parameters for the stored procedure here
AS
BEGIN TRANSACTION

       SET NOCOUNT ON;

  INSERT INTO[revision].[Test] WITH (ROWLOCK) 
           ([Base]
           ,[Seq]
           ,[Comment])
              OUTPUT Inserted.[ID], Inserted.[Base], Inserted.[Seq]
              SELECT TOP 1 [Base]
                       ,[Seq]+1
                       ,[Comment]
                FROM [revision].[Test]
                WITH (TABLOCKX, HOLDLOCK)
                WHERE Base=200
                ORDER BY Seq DESC
               

COMMIT TRANSACTION