sql-server - SQL : Deadlock because DELETE from child table needs XLOCK on foreign key parent table

Question

我有两个外键关系的表，像这样:

CREATE TABLE table_a 
(
     id int IDENTITY(1,1) NOT NULL PRIMARY KEY
)

CREATE TABLE table_b 
(
    id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    a_id int FOREIGN KEY REFERENCES table_a(id),
    other_id int NOT NULL,
    info varchar(max) NOT NULL
)

table_b在 a_id 上有一个索引和 other_id :

CREATE NONCLUSTERED INDEX table_b_i1 
ON table_b (a_id ASC, other_id ASC)

两个线程通过 3 个步骤运行一个事务:

1. 创建临时 #table_b为 table_b 保留一些新值
2. 从 table_b 中删除其中 a_id匹配来自 #table_b 的值但是other_id没有
3. 合并到table_b其中 a_id和 other_id两者都匹配来自 #table_b 的值

交易:

-- step 1
SELECT /* some stuff */ INTO #table_b

-- step 2
DELETE FROM table_b
WHERE EXISTS (SELECT 1 FROM #table_b tb_t WHERE tb_t.a_id = table_b.a_id) AND
  NOT EXISTS (SELECT 1 FROM #table_b tb_t WHERE tb_t.a_id = table_b.a_id AND tb_t.other_id = table_b.other_id)

-- step 3
MERGE INTO table_b AS target USING #table_b AS source
ON target.a_id = source.a_id AND target.other_id = source.other_id
WHEN MATCHED THEN
UPDATE SET
  target.info = source.info
WHEN NOT MATCHED BY target THEN
INSERT (a_id, other_id, info)
VALUES (source.a_id, source.other_id, source.info)

当一个线程正在运行第 2 步而另一个线程正在运行第 3 步时，就会发生死锁。非常重要的是要注意从来没有是一个线程使用相同的a_id。作为另一个；即，临时 #table_b在一个线程中永远不会匹配单个 a_id在#table_b来自另一个线程，曾经，时期。死锁图如下所示:

<deadlock>
  <victim-list>
    <victimProcess id="process_step2" />
  </victim-list>
  <process-list>
    <process id="process_step2" ...>
      <executionStack>
        ...
      </executionStack>
      <inputbuf>
/* STEP 2 */
      </inputbuf>
    </process>
    <process id="process_step3" ...>
      <executionStack>
        ...
      </executionStack>
      <inputbuf>
/* STEP 3 */
      </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <pagelock ... subresource="FULL" objectname="table_b" ... mode="U" associatedObjectId==>(table_b.id)>
      <owner-list>
        <owner id="process_step3" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process_step2" mode="IU" requestType="wait" />
      </waiter-list>
    </pagelock>
    <pagelock ... subresource="FULL" objectname="table_a" ... mode="IX" associatedObjectId==>(table_b_i1)>
      <owner-list>
        <owner id="process_step2" mode="IX" />
      </owner-list>
      <waiter-list>
        <waiter id="process_step3" mode="S" requestType="wait" />
      </waiter-list>
    </pagelock>
  </resource-list>
</deadlock>

第一个页锁完全有意义——合并(第 3 步)正在更新 table_b 中的很多行，所以它有一个 U 页锁，而删除(第 2 步)是从 table_b 中删除单独的行，所以它试图获得 IU 页锁。

第二个对我来说毫无意义:当然，合并(第 3 步)应该尝试读取索引，其中包括 a_id , 以满足 table_a.id 上的外键约束;但为什么删除(第 2 步)会查看 table_a在所有？我可以理解为什么插入或更新需要确保满足相同的约束，但为什么要删除？第 2 步绝不会引用 table_a ，尤其不是 table_a.id .

我不明白为什么这个事务会导致死锁，尤其不明白为什么在外键约束的子表中删除需要在父表上加锁。有什么想法吗？

Answer 1

尝试提供第 2 步:DELETE FROM table_b with(tablock) ......

然后在您完成第 3 步并提交之前，不会有其他进程运行第 2 步。

如果您的进程在没有事务的情况下运行，那么您应该在两个语句中使用“with(tablock)”或添加“begin tran”和“commit”