Columnstore Clustered Index - Deadlock issue ( SQL Server )

0
votes

I am working on ingesting data into TABLE A in SQL Server [ with a columnstore clustered index ]. There are 4 parallel threads of "INSERT BULK" writing into table, I am getting a deadlock error:

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

After researching, I found out that clustered columnstore index usually get deadlock issues, but could not figure out the resolution. Can you please suggest what could be done?

1
sql-serverdeadlockbulk-loadcolumnstore
This issue does not occur if you set the max degree of parallelism (MAXDOP) to 1 for the query. the following link might help-- support.microsoft.com/en-in/help/4057055/… – sacse
Post the deadlock_xml from the system_health XE trace. From SSMS Object Explorer, Management-->Extended Events-->Sessions-->system_helath-->package0.event_file and double-click to view the events. Then select Extended Events...-->Filters from the top menu bar and specify name = xml_deadlock_report to view the deadlock events. Then select one and copy the xml value to add to your question. – Dan Guzman
I need to get it from admins.. cant access myself. – Abhijeet Rajput
Also please provide the SQL Server version (` select @@version`). – Pio

1 Answers

0
votes

Check if your db has snapshot isolation level ON. For me changing snapshot isolation level to off solved deadlocks problems on Columnstore table.