本地运行快速的存储过程在服务器大数据量下异常（疑似死锁）

问题背景 #

编写了InsertMultipleChangeLogs存储过程，用于批量插入ChangeLog父对象，获取插入ID后批量插入关联的ChangeLogItems子对象。存储过程代码如下：

CREATE PROCEDURE InsertMultipleChangeLogs
(
    @ChangeLogs dbo.ChangeLogs readonly,
    @ChangeLogItems dbo.ChangeLogItems readonly,
    @BatchSize int = 300
)
AS

DECLARE @Map AS TABLE
                (
                    TempId int,
                    InsertedId int
                )

DECLARE @startIndex int = 0;
DECLARE @numRows int;
select @numRows = count(*) from @ChangeLogs;
    WHILE @startIndex <= @numRows
        BEGIN
            BEGIN TRANSACTION
                BEGIN TRY
                    MERGE INTO dbo.ChangeLogs USING (
                        SELECT *
                        FROM @ChangeLogs
                        ORDER BY Id
                        OFFSET @startIndex ROWS
                            FETCH NEXT @BatchSize ROWS ONLY
                    ) AS source
                    ON 1 = 0 -- Always not matched
                    WHEN NOT MATCHED THEN
                        INSERT (
                            State,
                            DateTime,
                            UpdatedBy
                        )
                                VALUES (
                                   source.State,
                                   source.DateTime,
                                   source.UpdatedBy
                               )
                        OUTPUT source.Id, inserted.Id
                            INTO @Map (TempId, InsertedId);

                    INSERT INTO dbo.ChangeLogItems(
                                                    ChangeLogId
                                                  , PropertyName
                                                  , OldValue
                                                  , NewValue
                    )
                    SELECT InsertedId
                         , PropertyName
                         , OldValue
                         , NewValue
                    FROM @ChangeLogItems as OD
                             INNER JOIN @Map as Map
                                        ON(OD.ChangeLogId = Map.TempId);
                    COMMIT TRANSACTION;
                END TRY
                BEGIN CATCH
                    IF @@TRANCOUNT > 0
                        ROLLBACK TRANSACTION;
                END CATCH
                SET @startIndex += @BatchSize;
        END

问题现象 #

• 本地环境处理大量数据时运行正常
• 服务器环境处理小数据量正常，但处理20000条数据时，会出现ChangeLog和ChangeLogItems表查询阻塞，服务器负载仅50-60%
• 该存储过程有时能在服务器处理大数据量，怀疑存在死锁问题，已实现批处理但问题仍存在

排查与解决建议 #

1. 修复临时映射表的累积问题 #

当前@Map表在循环中未清空，每批次都会累积之前的映射数据，导致后续子表插入的JOIN操作数据量越来越大，延长锁持有时间。必须在每个批次开始前清空该表：

WHILE @startIndex <= @numRows
    BEGIN
        -- 新增：清空临时映射表，避免数据累积
        TRUNCATE TABLE @Map;
        BEGIN TRANSACTION
        -- 后续原有逻辑...

2. 替换MERGE为INSERT...OUTPUT #

MERGE在ON 1=0的场景下本质就是插入，但它的锁策略更激进，改用INSERT...OUTPUT可以减少不必要的锁开销：

-- 替换原MERGE语句
INSERT INTO dbo.ChangeLogs (State, DateTime, UpdatedBy)
OUTPUT source.Id, inserted.Id INTO @Map (TempId, InsertedId)
SELECT State, DateTime, UpdatedBy
FROM @ChangeLogs
ORDER BY Id
OFFSET @startIndex ROWS
FETCH NEXT @BatchSize ROWS ONLY

3. 优化索引与统计信息 #

• 确保ChangeLogItems表的ChangeLogId字段存在非聚集索引，平衡插入时的索引维护开销与查询性能
• 更新两张表的统计信息，确保查询优化器生成高效执行计划：

  UPDATE STATISTICS dbo.ChangeLog;
  UPDATE STATISTICS dbo.ChangeLogItems;
  

• 确认ChangeLog表的主键（Id）为自增列，自增列的插入锁策略更友好，不会出现范围锁膨胀

4. 死锁精准排查 #

• 开启SQL Server死锁跟踪，死锁发生后会在日志中生成详细死锁图：

  DBCC TRACEON(1222, -1);
  

• 使用系统视图查看当前锁等待情况：

  -- 查看当前锁资源
  SELECT * FROM sys.dm_tran_locks;
  -- 查看等待统计
  SELECT * FROM sys.dm_os_wait_stats WHERE wait_type LIKE '%DEADLOCK%' OR wait_type LIKE '%BLOCK%';
  

5. 调整批次大小 #

当前默认批次为300，可尝试调整为100或500，观察阻塞情况变化。批次过小会增加事务数量，过大则延长锁持有时间，需根据服务器性能找到平衡点。

6. 隔离级别优化（业务允许时） #

开启READ COMMITTED SNAPSHOT隔离级别，让查询操作读取快照数据而非被写操作阻塞：

ALTER DATABASE YourDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE YourDatabaseName SET READ_COMMITTED_SNAPSHOT ON;

注意：此操作会增加版本存储的开销，需评估数据库存储容量。

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