多并发下PatientID生成重复致主键冲突问题求助

核心问题点 #

1. 事务嵌套导致锁提前释放
   你同时使用了ADO.NET的SqlTransaction外层事务和SQL语句内的BEGIN TRAN/COMMIT TRAN内层事务。SQL Server的嵌套事务是伪嵌套结构，内层COMMIT会直接释放锁，外层事务尚未提交时，其他并发会话就能读取到ID生成逻辑的结果，导致多个会话生成重复ID。

2. 无匹配记录时锁失效
   当对应labID的患者记录不存在时，SELECT TOP 1 ... WITH (UPDLOCK)不会返回任何行，也就没有行被加锁。此时所有并发会话都会进入IF @nextPatientId IS NULL分支，统一生成'01'，必然触发主键重复错误。

3. ID生成与插入的时间窗口竞态
   当前代码仅负责生成ID，插入操作在函数外执行。生成ID到插入数据之间存在时间差，其他会话可能在这个窗口内生成相同ID并完成插入，最终导致主键冲突。

修复方案 #

方案1：统一事务管理+修复锁逻辑

去掉SQL语句中的事务控制，用外层ADO.NET事务覆盖ID生成+插入的完整流程，同时处理无记录时的锁占位问题：

Private Function GetNextPatientId(labID As String) As String
    Dim connectionString As String = My.Settings.PatientsConnectionString
    Dim result As String = ""

    Using connection As New SqlConnection(connectionString)
        connection.Open()
        Dim retryCount As Integer = 0
        Dim maxRetries As Integer = 3
        Dim delayMilliseconds As Integer = 400

        Using transaction As SqlTransaction = connection.BeginTransaction()
            While retryCount < maxRetries
                Try
                    Dim queryString As String = "
                    DECLARE @nextPatientId AS VARCHAR(12);

                    -- 获取最大ID并加锁，HOLDLOCK延长锁周期
                    SELECT TOP 1 @nextPatientId = (CAST(SUBSTRING(PatientID, 3, 12) AS BIGINT) + 1)
                    FROM patientinfo WITH (UPDLOCK, HOLDLOCK)
                    WHERE SUBSTRING(patientid, 1, 2) = @labID
                    ORDER BY SUBSTRING(PatientID, 3, 12) DESC;

                    -- 无记录时，通过临时占位确保锁范围
                    IF @nextPatientId IS NULL
                    BEGIN
                        MERGE INTO patientinfo WITH (HOLDLOCK) AS target
                        USING (SELECT @labID + '01' AS PatientID) AS source
                        ON target.PatientID = source.PatientID
                        WHEN NOT MATCHED THEN INSERT (PatientID) VALUES (source.PatientID);
                        SET @nextPatientId = '01';
                        -- 回滚占位插入，仅保留锁效果
                        DELETE FROM patientinfo WHERE PatientID = @labID + '01';
                    END

                    SELECT @nextPatientId AS nextPatientIdValue;"

                    Dim command As New SqlCommand(queryString, connection, transaction)
                    command.Parameters.AddWithValue("@labID", labID)

                    Dim nextPatientIdValue As String = ""
                    Using reader As SqlDataReader = command.ExecuteReader()
                        If reader.Read() Then
                            nextPatientIdValue = Convert.ToString(reader("nextPatientIdValue"))
                        End If
                    End Using

                    result = nextPatientIdValue
                    Exit While
                Catch ex As SqlException When ex.Number = 1205 AndAlso retryCount < maxRetries
                    retryCount += 1
                    Threading.Thread.Sleep(delayMilliseconds)
                Catch ex As Exception
                    transaction.Rollback()
                    Throw
                End Try
            End While
            -- 注意：事务需在插入患者信息完成后再提交，不要提前执行此步骤
            -- transaction.Commit()
        End Using
    End Using

    Return result
End Function

方案2：改用IDENTITY+计算列彻底规避并发问题

这是最稳妥的方案，将PatientID拆分维护：

• 创建INT类型的Identity列（如PatientSeq），由SQL Server自动生成递增序列
• 用计算列生成最终PatientID：PatientID = labID + RIGHT('0000000000' + CAST(PatientSeq AS VARCHAR), 10)

完全无需手动生成ID，依赖SQL Server的原生机制保证序列唯一性，彻底解决并发冲突。

方案3：用独立序列表维护各labID的最大ID

先创建序列表：

CREATE TABLE LabPatientSequence (
    LabID VARCHAR(2) PRIMARY KEY,
    NextSeq BIGINT DEFAULT 1
)

再通过原子更新操作获取下一个序列：

UPDATE LabPatientSequence WITH (UPDLOCK)
SET NextSeq = NextSeq + 1
OUTPUT INSERTED.NextSeq - 1
WHERE LabID = @labID

-- 若不存在则插入初始值
IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO LabPatientSequence (LabID, NextSeq)
    VALUES (@labID, 2)
    SELECT 1 AS NextSeq
END

这种方式通过原子更新保证并发安全，比锁表操作更高效。

内容的提问来源于stack exchange，提问作者Siqueler