When you run an INSERT SELECT statement against a clustered columnstore index (CCI) table in Microsoft SQL Server 2016, you may find an intra-query deadlock on communication buffer if the statement is running under the same transaction as a truncate statement on the same CCI table.
When there is a schema modification operation (such as TRUNCATE TABLE, and ALTER TABLE) in the same transaction with parallel INSERT SELECT, you may receive an intra-query deadlock. This issue occurs if the lock policy that's specific for parallel INSERT SELECT is only working when there is an X lock. In this scenario, you may receive a SCH_M lock that covers the access of X mode.
To work around this issue, you can do the following:
- Set MAXDOP to 1 to avoid the parallel INSERT.
- Remove the CCI during the INSERT SELECT operation.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Article ID: 4017154 - Last Review: 2017, മേയ് 15 - Revision: 6
Microsoft SQL Server 2016 Developer, Microsoft SQL Server 2016 Enterprise, Microsoft SQL Server 2016 Enterprise Core, Microsoft SQL Server 2016 Standard, Microsoft SQL Server 2016 Service Pack 1