Deadlock on reorganize index-CodePudding

I have the following code

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

I get this error:

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.

The main question is, can I somehow run index reorganization in this logic?

Or do I have to finish all the transactions and only then do the reorganization?

Second question - why does this query get a deadlock if the previous transaction is confirmed?

Table and Table_Staging has columnstore index.

Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64)   
Copyright (C) 2019 Microsoft Corporation  Developer Edition (64-bit) 
on Windows Server 2022 Standard 10.0 <X64> (Build 20348: )

Prod is on Enterprise and Standard editions.

Update #1

I removed TABLOCK from the query and the DEADLOCK problem disappeared. Do I understand correctly that the TABLOCK hint applies to both tables (although I only specify it in INSERT, not SELECT)? Why does TRUNCATE TABLE work correctly then, since it also requires an exclusive lock? Or is it just a coincidence?

CodePudding user response：

can I somehow run index reorganization in this logic?

Yes. But commit your whole transaction before reorganize. Otherwise you're holding an exclusive lock on the table/partition before you start the reorganize. This makes it more likely that some other session is blocked by your session, and that you may, in turn, become blocked by a lock held by that session.

And if the reorganize is a deadlock victim, retry or just move on.