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

问题描述

TLDR: 在尝试按主键删除包含指向另一个父"表的外键的子"表上的行时，它会在子表的持续时间内锁定父表交易.外键/子删除可以做什么来防止发生锁定?

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

---

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?

推荐答案

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

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)

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

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

这篇关于SQL在删除子表行时锁定父表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！