为何并发SSIS Execute SQL任务偶发停滞？如何解决？

为公司金融应用搭建的大型SSIS项目中，序列容器内7个按年份划分的Execute SQL任务并发执行，每个任务调用存储过程处理指定日期范围数据，通过INSERT写入按年份分区的attr.securities表（每日插入约25万行）。插入逻辑使用TABLOCKX提示，表的锁升级策略设置为LOCK_ESCALATION = AUTO。

插入代码片段：

BEGIN TRANSACTION
    INSERT INTO attr.securities WITH (TABLOCKX)
    (
        -- 列列表
    )
    SELECT
        -- 查询逻辑
    FROM    #securities
COMMIT
CHECKPOINT

偶发出现部分任务停滞现象：正常执行时各年份的end_date应大致同步，但停滞时对应年份的end_date明显滞后（如示例中2009-2011年），需重启SSIS或手动执行任务才能恢复。移除TABLOCKX提示后情况恶化。

• TABLOCKX强制全表排他锁，引发并发阻塞：尽管目标表是分区表，但TABLOCKX是显式表级排他锁提示，会覆盖LOCK_ESCALATION = AUTO的分区锁策略。7个并发任务都需要获取全表排他锁，实际只能串行执行；一旦某个任务因内部处理（如源数据查询缓慢、临时表计算耗时）导致事务持锁时间过长，后续任务会持续等待，表现为停滞。
• 长事务加剧锁竞争风险：整个插入操作包裹在单个事务中，25万行数据的写入加上前置计算逻辑，事务持续时间较长，增加了锁冲突概率和阻塞窗口。
• 手动CHECKPOINT引发IO瓶颈：事务提交后立即执行CHECKPOINT，会强制数据库将所有脏页写入磁盘，消耗大量IO资源。多个任务连续触发CHECKPOINT时，IO资源耗尽会导致后续任务停滞。
• 外部锁竞争干扰：若存在其他进程（如报表查询、其他ETL作业）访问attr.securities表，持有与TABLOCKX冲突的锁，会直接阻塞任务执行。

1. 替换TABLOCKX为TABLOCK提示：
   对于分区表，当INSERT操作仅针对单个分区时，TABLOCK会自动获取该分区的排他锁（而非全表锁），允许不同年份的任务真正并发执行。修改后的插入代码：

   BEGIN TRANSACTION
       INSERT INTO attr.securities WITH (TABLOCK)
       (
           -- 列列表
       )
       SELECT
           -- 查询逻辑
       FROM    #securities
   COMMIT
   

   此调整既能保留批量插入的性能优势（减少锁粒度切换、启用批量日志），又能避免全表锁的并发阻塞。

2. 缩小事务范围：
   若存储过程按日历日处理数据，将事务拆分到单日或小批量级别提交，减少单事务的锁持有时间。例如：

   -- 按单日循环处理
   DECLARE @current_date DATE = '2009-01-01'
   WHILE @current_date <= @end_date
   BEGIN
       BEGIN TRANSACTION
           INSERT INTO attr.securities WITH (TABLOCK)
           SELECT ... FROM #securities WHERE asof_date = @current_date
       COMMIT
       SET @current_date = DATEADD(DAY, 1, @current_date)
   END
   

3. 移除手动CHECKPOINT：
   SQL Server的自动检查点机制会根据恢复模型和系统负载自动处理脏页写入，手动执行CHECKPOINT会加剧IO竞争，直接移除即可。

4. 排查并消除外部锁竞争：
   使用以下查询监控锁等待和阻塞源，确认是否有外部进程干扰：

   -- 查看当前锁等待情况
   SELECT
       request_session_id,
       resource_type,
       resource_description,
       request_mode,
       request_status
   FROM sys.dm_tran_locks
   WHERE request_status = 'WAIT'
   
   -- 查看等待统计
   SELECT wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count
   FROM sys.dm_os_wait_stats
   WHERE wait_type LIKE '%LOCK%' OR wait_type LIKE '%IO%'
   ORDER BY wait_time_ms DESC
   

   调整外部任务的执行时间或隔离级别，避免与该SSIS作业的锁冲突。

5. 优化存储过程性能：

   • 为临时表#securities添加合适的索引，加速查询过滤和计算。
   • 检查源数据查询的执行计划，优化索引或查询逻辑，减少数据处理时间，缩短事务持续周期。

6. 调整SSIS并发数：
   若服务器CPU、IO资源有限，适当降低序列容器的并发任务数（如从7个调整为4-5个），避免资源耗尽导致任务停滞。

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