Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: Table Partitioning - MERGE RANGE deadlocking

    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?

  2. #2
    Join Date
    Jun 2005
    Posts
    319
    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.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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:
    Code:
    BEGIN TRANSACTION
    
    SELECT COUNT(*)
       FROM DemoData (HOLDLOCK)
       WHERE DemoDate = '2012-01-20'
    
    DELETE FROM DemoData
       WHERE DemoDate = '2012-01-20'
    
    COMMIT TRANSACTION
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jun 2005
    Posts
    319
    Quote Originally Posted by Pat Phelan View Post
    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:
    Code:
    BEGIN TRANSACTION
    
    SELECT COUNT(*)
       FROM DemoData (HOLDLOCK)
       WHERE DemoDate = '2012-01-20'
    
    DELETE FROM DemoData
       WHERE DemoDate = '2012-01-20'
    
    COMMIT TRANSACTION
    -PatP
    Thanks Pat - good stuff.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •