SQL Server索引重组时死锁问题及相关技术疑问求助

问题背景 #

执行的SQL代码 #

BEGIN TRANSACTION
    -- begin transaction to avoid truncate Table_Staging from parallel process
    INSERT INTO [dbo].[Table_Staging] WITH (TABLOCK) (COLUMN1)
        SELECT COLUMN1
        FROM [Table2]
        WHERE [RegistrationDate] BETWEEN '20221101' AND '20221112' 

    BEGIN TRANSACTION
        -- begin transaction to avoid reads from Table from other queries
        TRUNCATE TABLE [DB].[dbo].[Table] WITH (PARTITIONS (156));

        ALTER TABLE [DB].[dbo].[Table_Staging] 
            SWITCH PARTITION 156 TO [DB].[dbo].[Table] PARTITION 156;
        COMMIT TRANSACTION

        ALTER INDEX [ics_SplitEventAggregated]  
        ON [DB].[dbo].[Table] REORGANIZE PARTITION = 156;

        COMMIT TRANSACTION

报错信息 #

Msg 1205, Level 13, State 18, Procedure sys.sp_cci_tuple_mover, Line 7 [Batch Start Line 0]

Transaction (Process ID 99) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Msg 35373, Level 16, State 1, Line 80

ALTER INDEX REORGANIZE statement failed on a clustered columnstore index with error 1205. See previous error messages for more details.

核心疑问 #

1. 是否可以在当前事务逻辑中执行索引重组？还是必须完成所有事务后再执行？
2. 为何前序事务已提交仍会出现死锁？

补充信息 #

• Table和Table_Staging均为列存储索引表
• 使用SQL Server版本：Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64)，生产环境为企业版和标准版

更新1 #

移除INSERT语句中的TABLOCK提示后死锁问题消失，新增疑问：

• TABLOCK提示是否会同时作用于INSERT和SELECT涉及的两张表？
• 为何需要排他锁的TRUNCATE TABLE仍能正常执行？这是否只是巧合？

疑问解答 #

• 是否可以在事务内执行索引重组？
  可以执行，但不建议。列存储索引的REORGANIZE操作会触发后台tuple mover进程，该进程处理压缩段时锁持有时间较长。放在事务内会延长外层事务的锁持有窗口，大幅增加与其他事务锁冲突、引发死锁的概率。建议将ALTER INDEX REORGANIZE移到外层事务提交后执行，缩小锁持有时间以降低冲突风险。

• 前序事务已提交为何仍死锁？
  死锁发生在ALTER INDEX REORGANIZE阶段。虽然内部事务已提交，但外层事务未结束，之前分区切换等操作持有的部分锁可能被外层事务继承；或者tuple mover异步操作在后台执行时，与其他进程争抢锁资源形成循环等待，最终触发死锁。

• TABLOCK是否作用于INSERT和SELECT的两张表？
  TABLOCK是表级锁提示，仅作用于INSERT的目标表Table_Staging，不会影响SELECT的源表Table2。它会让INSERT操作获取Table_Staging的排他表锁（X锁），而非默认的行/页级锁。

• TRUNCATE TABLE为何能正常执行？是否是巧合？
  不是巧合。TRUNCATE TABLE需要的是目标表（此处为Table的分区156）的排他锁，而TABLOCK锁定的是Table_Staging，二者是不同对象，锁资源无冲突，因此可正常执行。之前的死锁是因为TABLOCK让Table_Staging的排他表锁持有了整个外层事务周期，与其他操作相关资源的进程形成循环等待；移除TABLOCK后锁粒度变小、持有时间缩短，自然减少了死锁触发条件。

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