Deadlock with On Delete Cascade with multiple child tables RRS feed

  • Question

  • I am getting deadlock in the below scenario.

    I have table A, table B and table C. 

    Table A has the primary key on Col1. Table B has reference key on A(Col1) and Table C has reference key on A(Col1) and both have on delete cascade constraints. Also table B and table C has non-clustered index.

    In my application first thread is trying to insert entry into Table A, Table B and Table C in a transaction and second thread trying to delete a row from table A causing the deadlock.

    The deadlock is happening between index of table B locked by the first thread and index of table C locked by the second thread where both the threads need the locks on other table.

    How should I address the issue? Also why sqlserver is allowing two threads to locks index in different order?

    • Edited by Rocking Jai Thursday, October 10, 2013 8:54 PM
    Thursday, October 10, 2013 8:53 PM