SQL在删除子表行时锁定父表

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?

设置:

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

连接 1:(先运行)

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

连接 2:

DELETE  dbo.Parent
WHERE   Parent.ID = 2;

<小时>

在上面的场景中，连接 2 的删除将被连接 1 阻塞，直到该连接完成打开的事务 - 即使在父级上删除的行与被删除的子级引用的行不同(并且实际上没有任何子条目).