I am not totally convinced but the following shows the procedure seems to work.
SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
USE tempdb;
GO
-- Test Table
CREATE TABLE dbo.barcodecounter
(
prefix varchar(6) NOT NULL
CONSTRAINT PK_barcodecounter PRIMARY KEY
,LastValue int NOT NULL
);
GO
-- Test Procedure
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.GetNextBarcode
(
@prefix varchar(6)
,@NextBarCode varchar(14) OUTPUT
)
AS
SET NOCOUNT, XACT_ABORT ON;
DECLARE @tResult TABLE (Result int NOT NULL);
UPDATE dbo.barcodecounter
SET LastValue = LastValue + 1
OUTPUT inserted.LastValue INTO @tResult
WHERE prefix = @prefix;
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO dbo.barcodecounter
OUTPUT inserted.LastValue INTO @tResult
SELECT @prefix, 1
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.barcodecounter WITH (UPDLOCK, SERIALIZABLE)
WHERE Prefix = @prefix
AND LastValue = 1
);
IF @@ROWCOUNT = 0
-- Another process could have inserted 1
BEGIN
UPDATE dbo.barcodecounter
SET LastValue = LastValue + 1
OUTPUT inserted.LastValue INTO @tResult
WHERE prefix = @prefix;
END
END
SET @NextBarCode = (SELECT @prefix + RIGHT('00000000' + CAST(Result AS varchar(8)), 8) FROM @tResult);
GO
-- Simple Test
truncate table dbo.barcodecounter;
DECLARE @NextCode varchar(14);
EXEC dbo.GetNextBarcode 'Test1', @NextCode OUTPUT;
print @NextCode;
EXEC dbo.GetNextBarcode 'Test2', @NextCode OUTPUT;
print @NextCode;
EXEC dbo.GetNextBarcode 'Test3', @NextCode OUTPUT;
print @NextCode;
EXEC dbo.GetNextBarcode 'Test1', @NextCode OUTPUT;
print @NextCode;
EXEC dbo.GetNextBarcode 'Test2', @NextCode OUTPUT;
print @NextCode;
GO
-- *** Basic concurrency test ***
--create dbo.fnTally from:
-- https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
truncate table dbo.barcodecounter;
CREATE TABLE ##Results1
(
Barcode varchar(14) NOT NULL
);
CREATE TABLE ##Results2
(
Barcode varchar(14) NOT NULL
);
/*
Now copy the following:
TRUNCATE TABLE ##Results2;
DECLARE @NextCode varchar(14);
and the results of:
WITH Prefixes
AS
(
SELECT *
FROM
(
VALUES ('Test1'),('Test2'),('Test3'),('Test4'),('Test5'),('Test6'),('Test7')
) V (Prefix)
)
SELECT 'EXEC dbo.GetNextBarcode ''' + P.Prefix + ''', @NextCode OUTPUT;' +
'INSERT INTO ##Results2 SELECT @NextCode;'
FROM Prefixes P
CROSS JOIN dbo.fnTally(1, 1000) T
to another window. (Window2)
Then copy:
WAITFOR DELAY '00:00:01';
TRUNCATE TABLE ##Results1;
DECLARE @NextCode varchar(14);
and the results of:
WITH Prefixes
AS
(
SELECT *
FROM
(
VALUES ('Test1'),('Test2'),('Test3'),('Test4'),('Test5'),('Test6'),('Test7')
) V (Prefix)
)
SELECT 'EXEC dbo.GetNextBarcode ''' + P.Prefix + ''', @NextCode OUTPUT;' +
'INSERT INTO ##Results1 SELECT @NextCode;'
FROM Prefixes P
CROSS JOIN dbo.fnTally(1, 1000) T
to yet another Window (Window1)
Start running Window1 and a second later start running window2
*/
-- When Window1 and Window2 have finished running
-- the following should return an empty set. ie no race condition.
WITH AllResults
AS
(
SELECT Barcode FROM ##Results1
UNION ALL
SELECT Barcode FROM ##Results2
)
SELECT Barcode
FROM AllResults
GROUP BY Barcode
HAVING COUNT(*) > 1;
select * from ##Results1
select * from ##Results2;
-- Now close Window1 and Window2 and
--Tidy up
DROP TABLE IF EXISTS ##Results1;
DROP TABLE IF EXISTS ##Results2;
DROP FUNCTION IF EXISTS dbo.fnTally;
DROP PROCEDURE IF EXISTS dbo.GetNextBarcode;
DROP TABLE IF EXISTS dbo.barcodecounter;