使用 UPDLOCK 的 SqlTransaction

问：

所以我有一张桌子，我只需点击一个 .patientinfobutton

同一个按钮会生成患者的 ID，但由于某种原因，如果我运行计时器以同时从 5 个应用程序执行此代码，则只有一个会通过，而其他应用程序将失败。尽管我尝试在此查询中使用，，甚至.ROWlockUpdlockTablockx

关于可能导致这种情况的原因的任何想法？除了插入失败之外，没有其他错误，因为我插入的是相同的生成 ID。Primary key error

    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 = "
                    BEGIN TRAN
                    DECLARE @nextPatientId AS VARCHAR(12);

                    SELECT TOP 1 @nextPatientId = (CAST(SUBSTRING(PatientID, 3, 12) AS BIGINT) + 1)
                    FROM patientinfo WITH (UPDLOCK)
                    WHERE SUBSTRING(patientid, 1, 2) = @labID
                    ORDER BY SUBSTRING(PatientID, 3, 12) DESC;

                    IF @nextPatientId IS NULL
                        SET @nextPatientId = '01';

                    SELECT @nextPatientId AS nextPatientIdValue;
                    COMMIT TRAN;"

                    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