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?
