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!