問題描述

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模板網！