SQL locks parent table while deleting child table row

Question

TLDR: While trying to delete a row by primary key on a "child" table that contains a foreign key to another "parent" table, it locks the parent table for the duration of the child's transaction. What could be done with the foreign key / child delete to prevent that lock from happening?

Example Scenario

---

Setup:

IF ( SELECT OBJECT_ID('dbo.Child')
   ) IS NOT NULL
   DROP TABLE dbo.Child;
IF ( SELECT OBJECT_ID('dbo.Parent')
   ) IS NOT NULL
   DROP TABLE dbo.Parent;
GO
CREATE TABLE dbo.Parent
       (
         ID INT PRIMARY KEY
                IDENTITY(1, 1) ,
         Value TINYINT NOT NULL
       );
CREATE TABLE dbo.Child
       (
         ID INT PRIMARY KEY
                IDENTITY(1, 1) ,
         Parent_ID INT CONSTRAINT FK_Child_Parent_ID FOREIGN KEY REFERENCES Parent ( ID ) ,
         Value TINYINT NOT NULL
       );
GO
INSERT  INTO dbo.Parent
        ( Value )
VALUES  ( 1 ),
        ( 2 );
INSERT  INTO dbo.Child
        ( Parent_ID, Value )
VALUES  ( 1, 1 );
GO

Connection 1: (run first)

BEGIN TRANSACTION;
DELETE  dbo.Child
WHERE   Child.ID = 1;

Connection 2:

DELETE  dbo.Parent
WHERE   Parent.ID = 2;

---

In the above scenario, the delete from connection 2 will be blocked by connection 1 until that connection finishes the open transaction - even though the row being deleted on the parent is not the same as the row referenced by the child being deleted (and in fact doesn't have any child entries).

Is there any way to modify the constraint to allow this scenario to work?

Answer 1

In this scenario you just need to create index on the column Parent_ID. It will force query optimizer to use Index Seek operation

CREATE INDEX x ON dbo.Child(Parent_ID)

Otherwise Connection2 will have Clustered Index Scan on the table Child which blocks by Connection1

Answer 2

Well the problem is, you define a constraint and then you expect sql server to ignore that constraint when it comes to delete a row.

At the moment because the Parent table is being referenced by the child table, when you delete a row from the Parent table it looks up for any possible values in the Child table, because if there are any the Foreign Key constraint is there to make sure no orphan records are left in the Child table. Since there are no other indexes on any of the columns it need to look up the clustered index.

If you look at the Execution plans for both statements, The Delete statement for child table is Scanning the Clustered Index PK_Child__....

Now if you look at the execution plan for the Delete statement in Parent table it also is scanning the Clustered Index PK_Child__... in the child table.

Since you have an explicit Transaction in the first session, it obtains an exclusive lock on the Index and stops other processes to access it, until its done with it.

As suggested by Alex in the other post if you created an Index on the child table on the column referencing back to the parent table then The delete in Parent table will have that index to do the lookup and the clustered index is left alone and the delete statement completes without any blocking.

CREATE INDEX IX_Child_Parent_ID 
ON dbo.Child(Parent_ID)