Deadlock caused by multiple `ON DELETE CASCADE` Foreign Keys-CodePudding

I am experiencing a deadlock issue, caused by chain of ON DELETE CASCADE FKs. The situation is the following:

Table C has ON DELETE CASCADE FK to B.

Table B has ON DELETE CASCADE FK to A.

A one or more requests comes in, resulting in the SQL Statement(s) DELETE FROM A where A.id = @0 AND A.userId = @1.

However, due to the cascading FKs, a massive execution plan is generated for each of the DELETE statements, some of the operators request X/IX locks on B/C.

When multiple delete statements try to do this at the same time, a deadlock occurs.

Is there a way to solve this without rewriting a whole bunch of delete methods(DB uses a couple of such FKs, didn't know it could come to this) to a SPs with manual deletion of related tables?

Any help is appreciated.

CodePudding user response：

Your issue is that the foreign keys on the child tables do not have an indexes, so every DELETE on the parent table needs to scan the whole child table to ensure there are no FK consistency issues. This seems to be the cause of the deadlocks.

Add the index to each child:

CREATE NONCLUSTERED INDEX IX_Parent ON Child (ParentID);

CREATE CLUSTERED INDEX IX_Parent ON Child (ParentID, SomeOtherCol);

It is essential that all primary keys and foreign keys have an index (with those columns as the leading key columns).

If you are missing an index on the foreign key then you will get locking issues on UPDATE and DELETE against the primary key of the parent table. If you are missing an index on the primary key then you will get locking issues on INSERT and UPDATE against the child table.

I note that most of your tables don't even have clustered indexes and are just heap tables, another bad idea.

You can add other columns as part of the key or as INCLUDE, but the PK or FK must be the leading column in the index.

You can see the effect of the index in this fiddle.