Deadlock on partitioned table

Question

we see deadlocks when loading a partitioned table. I wonder if someone can confirm what I think is the reason, or if I am looking at it wrong and we need to fix another thing.

Our setup is like this: A big data warehouse table gets data added to it every night. This is done through an SSIS Package, and in parallel. The data can be separated by column "country", and we run the same package for several countries at the same time to speed up things. One package alone simply does not use all resources of the machine.

With a non-partitioned table, we saw deadlocks once every few weeks. Not too bad, but too often to ignore.

We decided to partion the table by country, hoping to get rid of the deadlocks. I followed what I think is best practice, with an empty partition to the left and right, and one partition for each country. The country is encoded in an NVARCHAR(3) datatype. We recreated the clustered index using the partitioned column, and finally set lock escalation for the table to auto.

I can verify that all partitions are loaded with data belonging to it only, so all seems to be working fine.

However, we still keep getting deadlocks. I would have expected that my "write operation" (quotes will be explained next) would each focus on just one partition, and the parallel running SSIS packages could not interfere.

First question: Am I right in this expectation? Should I not be getting deadlocks now?

I think I have an answer to why they still appear: the "write operation" mentioned in quotes is somewhat complicated, it is an own SSIS component written in c# before my time with my employer. Each package creates and writes ALL DATA into it's own temp table, and then executes a

MERGE WHEN MATCHED UPDATE WHEN NOT MATCHED INSERT

from it's temp to the final table. When building this MERGE-Statement, the component uses a construction to compare NULLs, i.e.

WHERE @given IS NULL and col is NULL OR @given is not null and col = @given

and it does this also for the country-column, which is part of the clustered index and partition key.

I suspect, that by having a query relating NULL to the partition key, I trigger a "search and lock every partition" operation. Is this the case, does something like this exist? Or does the description point to another reason why we still get deadlocks?

Thanks a lot for reading and your suggestions!

Answer 1

There are a number of "features" of merge statements that can lead to issues like this. For a relatively comprehensive list of issues take a look at the following link:

Use Caution with SQL Server's MERGE Statement

I've seen similar issues occurring myself, the solution was either to re-write the code to avoid MERGE altogether or in once case where it was unavoidable, dropping indexes on the target table before the MERGE and then restoring them afterwards resolved the issue.