On a system with a high number of inserts I have a job that implements a sliding window in order to purge data that is 30 days old (table is partitioned on a Date_ID column).

The problem is the job fails a lot due to deadlock.

I have tried setting DEADLOCK_PRIORITY to HIGH and then to 10 and it is still chosen as the deadlock victim.

I ran a trace and added the deadlock graph event and found it is deadlocking with an insert statement to the same table where the Date_ID is today's date.

So it would be inserting into the right most partition, I am trying to MERGE RANGE on the left most partition. I am confused as to why this should be deadlocking.

I have asked the client if they can halt inserts by stopping the windows service that controls the inserts and then start the service up after this daily purge maintenance but they said that was not an acceptable solution.

Any ideas?

bump, they're still having issues - I really think they should change their application to halt inserts during this very brief maintenance window (less than a minute), but their company is so large and disorganized they claim it would take at least 6 months to push out a feature such as this.

Any other solutions from the db side? Originally there was data being inserted into the first partition and we assumed the extra time it took to merge these rows (compared to when it was empty) was causing the deadlock, but now we see it when the partition we are merging is empty.

The only solution I have come up with is to repeatedly attempt the merge with a 30 second delay between attempts until it is successful, it seems like a hack but I feel it is the best solution until they can change their application to pause the stream of inserts while this purge operation takes place.

This is brute force and uglier than the south end of a north-bound mule, but it should work if the other process ever releases its locks long enough for this code to start running:-PatP

Thanks Pat - good stuff.

The real test will be to see if this does what you need. Assuming that your server settings and the behavior of the other code is even a tiny bit forgiving, I think this will work but the proof is in the pudding.

Let me know if this works or if you need a bigger hammer.

-PatP