Post reply

Deadlocks in Partitioned Tables in Sql2012

  • October 31, 2015 at 7:41 am

    #304648

    Hello experts,

    We are using partitions and all the table are properly aligned as per the partition keys. When this particular sp, which is inserting data to a table from a different table based on the partitionkeys is called by Web UI where threading has been applied, dead lock appears.

    Let me make it more clear.

    ThreadOne:

    Insert into table A(partitionKey,BatchId,...)

    select * from table B where partitionkey = 1

    ThreadTwo:

    Insert into table A(partitionKey,BatchId,...)

    select * from table B where partitionkey = 2

    I can see sometimes it gives deadlock for this procedure, not sure about the reason, as far as I guess since the tables are partitioned and escalation is set to Auto the deadlock should not occur

    Can someone help me, I can provide more info as required.

  • October 31, 2015 at 11:11 am

    #1836871

    We'd only be guessing without seeing the deadlock graph and the DDL for both tables including any indexes you may have on the tables. Post those and someone might be able to help here.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 31, 2015 at 2:47 pm

    #1836876

    What is the current transaction isolation ?

    @JayMunnangi

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

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