sql-server - SQL在删除子表行时锁定父表

Question

TLDR:当尝试通过“子”表上的主键删除包含另一个“父”表的外键的行时，它会在子表的持续时间内锁定父表。交易。如何使用外键/子删除来防止发生锁定？

示例场景

<小时/>

设置:

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 阻止，直到该连接完成打开的事务 - 即使父级上删除的行与要删除的子级引用的行不同(事实上没有任何子条目)。

有什么方法可以修改约束以允许此场景工作吗？

Answer 1

在这种情况下，您只需要在列 Parent_ID 上创建索引。它将强制查询优化器使用 Index Seek 操作

在 dbo.Child(Parent_ID) 上创建索引 x

否则 Connection2 将对被 Connection1 阻塞的 Child 表进行聚集索引扫描