Post reply

Partitioned Table Deadlock

  • December 7, 2015 at 7:06 am

    #313731

    Hi All,

    We have table which is partitioned based on partitionkey column (int type)

    Data is inserted to this table using dynamic query and then some updates are done

    I have taken care of adding PartitionKey clause in the where clause for update statement.

    All this insert and updates are done within a procedure which is called by a multithreaded application.

    I can see deadlocks and on analyzing I see the deadlock between different threads ON

    insert statement (using dynamic sql) and Update Statement.

    Any help about the reason for such a deadlock

    i have attached the deadlock graph

  • December 7, 2015 at 7:10 am

    #1844363

    What's the lock escalation set to for the partitioned indexes?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 7, 2015 at 7:17 am

    #1844366

    The lock escalation is set to Auto, even when I check the properties of Table and Index, it shows partitioned with the proper partition Scheme

  • December 7, 2015 at 9:27 am

    #1844423

    Also from the deadlock graph, I can see that the Insert was requesting a Page Level Lock while the updates acquired HOBT lock.

    Also, my doubt is during Select and Updates we can use Partitionkey in the predicate for Partition elimination and get the lock escalated to a single partition instead of complete table.

    But while inserting data to a table , how sql achieves partition lock

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply