SqlServer: 如何产生没有重复的报名编号

背景：

某一期的报名过程中，希望报名能做到报名从1开始，从小到大依次来。但实际生产环境中，用户集中大并发式报名，报名编号非常容易重复。

下面我们用简单的SQL来模拟和重现这个过程：

USE tempdb
GO
DROP TABLE IF EXISTS bm_data;
GO
CREATE TABLE bm_data(
	bmId INT IDENTITY(1,1) PRIMARY KEY,
	crId INT NOT NULL,
	regNo INT NOT NULL,
	addTime DATETIME NOT NULL	
)
GO
-- =============================================
-- Author:		yenange
-- Create date: 2024-06-04
-- Description:	测试报名
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[Proc_TestBm]
	@crId INT 
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @bmId INT=0,@errMsg NVARCHAR(512)=''
	
	BEGIN TRAN
	BEGIN TRY
		INSERT INTO bm_data
		(
			crId,
			regNo,
			addTime
		)
		SELECT 
			@crId,
 			(
 				SELECT ISNULL(MAX(b.regNo),0) + 1
 				FROM   bm_data AS b
 				WHERE  b.crId = @crId
 			) AS regno,
 			GETDATE()
		SET @bmId = SCOPE_IDENTITY();
		COMMIT TRAN;
	END TRY
	BEGIN CATCH
		SELECT @errMsg=ERROR_MESSAGE()
		ROLLBACK TRAN;
	END CATCH
	
	SELECT @bmId AS bmId, @errMsg AS errMsg
END
GO
--EXEC [dbo].[Proc_TestBm] @crId = 1
GO
--查询全部
SELECT * FROM [dbo].[bm_data]
--查询重复报名编号的记录
SELECT regNo,COUNT(1) AS cnt 
FROM [dbo].[bm_data] 
GROUP BY regNo
HAVING COUNT(1)>1

--TRUNCATE TABLE [bm_data]

模拟重现：

使用sql并发测试工具测试一下，重复10次，每次线程并发数50，总共500次。 

测试结果：有40个编号是重复的，其中还有2个编号是重复了3次。

解决方案：

-- =============================================
-- Author:		yenange
-- Create date: 2024-06-04
-- Description:	测试报名, 修正版
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[Proc_TestBm]
	@crId INT 
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @bmId INT=0,@errMsg NVARCHAR(512)='',@regNo INT=0
	
	BEGIN TRAN
	BEGIN TRY
		--先获取到报名编号
		SELECT @regNo=ISNULL(MAX(b.regNo),0) + 1
 		FROM   bm_data AS b WITH (ROWLOCK,XLOCK)
 		WHERE  b.crId = @crId
	
		INSERT INTO bm_data
		(
			crId,
			regNo,
			addTime
		)
		SELECT 
			@crId,
			@regNo,
 			GETDATE()
		SET @bmId = SCOPE_IDENTITY();
		COMMIT TRAN;
	END TRY
	BEGIN CATCH
		SELECT @errMsg=ERROR_MESSAGE()
		ROLLBACK TRAN;
	END CATCH
	
	SELECT @bmId AS bmId, @errMsg AS errMsg
END
GO

清空数据，再执行就是没有问题的了。