max(column_name) 返回相同的值，因为数据库上的负载很重

Question

以下查询连续命中，并且记录入到表中"TRANSACTION_MAIN"但@confrm对于几个事务来说，这个数字比max(TRN_CNFRM_NBR)是相同的，仅当数据库服务器上的负载过高时，才会看到此行为。对此有任何见解，为什么观察到这种行为，幕后可能发生了什么？

            BEGIN TRANSACTION
            DECLARE @Confrm as int;
            SET @Confrm = (SELECT isnull
            (MAX(CONVERT(int, TRN_CNFRM_NBR)),0) 
            FROM TRANSACTION_MAIN WHERE
            TRN_UC_LOC = @1) + 1;
            DECLARE @TMID as int;
            INSERT INTO TRANSACTION_MAIN(
                TRN_CNFRM_NBR
                ,TRN_UC_LOC
                ,TRN_STAT_ANID
                ,TRN_SRC_ANID
                ,PRSN_ANID
                ,TRN_DT
                ,TRN_ACTL_AMT
                ,TRN_MTHD
                ,TRN_IP_ADDR
                ,DSCT_CD
                ,TRN_PAID_AMT
                ,CSHR_ANID
                ,INVDEPTEQUIP_ANID
                ,TRN_CMNT
                ,CPN_DSCT_TOTAL
            )
            VALUES(@Confrm,@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14);
            SET @TMID = @@IDENTITY;");
               COMMIT TRANSACTION

Answer 1

嗯，这并不安全，所以它不起作用也就不足为奇了。

最终结果有几个贡献者。首先，同时选择最大值当然会返回相同的值，因为"新"行尚未插入。其次，根据事务隔离级别，选择看不到已插入但尚未提交的行。

作为快速解决方案，简单地将事务隔离级别设置得更高应该会有所帮助。这当然会降低吞吐量并增加死锁的风险，但至少是正确的。或者，如果你在足够高的 SQL 服务器上，请使用序列。或线程安全的 CLR 代码。

如果你被困在旧的SQL服务器上，无法处理更高的事务隔离，你可以尝试使用你自己的序列来实现这一点，其中递增序列是一个原子操作。在 SQL Server Migration Assistant for Oracle Microsoft有一个很好的例子。

Answer 2

你放错了 1 的加法。查询应如下所示：

SET @Confrm = (
        SELECT ISNULL(MAX(CONVERT(INT, TRN_CNFRM_NBR)), 0) + 1 -- you should add 1 here.
        FROM   TRANSACTION_MAIN
        WHERE  TRN_UC_LOC = @1
    );