sql - 死锁 - 没有数据时锁定列

Question

执行此存储过程时出现死锁:

-- Delete transactions
delete from ADVICESEQUENCETRANSACTION
where ADVICESEQUENCETRANSACTION.id in (
  select TR.id from ADVICESEQUENCETRANSACTION TR
  inner join ACCOUNTDESCRIPTIONITEM IT on TR.ACCOUNTDESCRIPTIONITEMID = IT.id
  inner join ACCOUNTDESCRIPTION ACC on IT.ACCOUNTDESCRIPTIONID = ACC.id
  inner join RECOMMENDATIONDESCRIPTION RD on ACC.RECOMMENDATIONDESCRIPTIONID = RD.id
  inner join RECOMMENDATION REC on REC.id = RD.RECOMMENDATIONID
  inner join ADVICESEQUENCE ADV on ADV.id = REC.ADVICESEQUENCEID
  where adv.Id = @AdviceSequenceId AND (@RecommendationState is NULL OR @RecommendationState=REC.[State])
    );

这是表的架构:

这是死锁图:

you can see the detail of the deadlock graph here

因此，当我检索资源节点的关联对象 ID 时，我确定它是 AdviceSequenceTransaction 表的主键和索引:

SELECT OBJECT_SCHEMA_NAME([object_id]), * ,
OBJECT_NAME([object_id]) 
FROM sys.partitions 
WHERE partition_id = 72057595553120256 OR partition_id = 72057595553316864;

SELECT name FROM sys.indexes WHERE object_id = 31339176 and (index_id = 1 or index_id = 4)

PK_AdviceSequenceTransactionIX_ADVICESEQUENCEID_ADVICE

由于表 AdviceSequenceTransaction 上的键 ParentTransactionId 和键 Primary key 存在关系，因此我在列 ParentTransactionId 上创建了索引。

我不再有僵局了。 但问题是我不知道为什么不再出现死锁:-/

而且，在测试的这组数据上，ParentTransactionId中没有数据。全部为 NULL。

那么，即使ParentTransactionId中没有数据(空)，SQL Server是否可以访问主键？？？

另一件事是我想删除删除语句中的连接:

delete from ADVICESEQUENCETRANSACTION
where ADVICESEQUENCETRANSACTION.id in (
  select TR.id from ADVICESEQUENCETRANSACTION TR
  inner join ACCOUNTDESCRIPTIONITEM IT on TR.ACCOUNTDESCRIPTIONITEMID = IT.id
  inner join ACCOUNTDESCRIPTION ACC on IT.ACCOUNTDESCRIPTIONID = ACC.id
  inner join RECOMMENDATIONDESCRIPTION RD on ACC.RECOMMENDATIONDESCRIPTIONID = RD.id
  inner join RECOMMENDATION REC on REC.id = RD.RECOMMENDATIONID
  inner join ADVICESEQUENCE ADV on ADV.id = REC.ADVICESEQUENCEID
  where adv.Id = @AdviceSequenceId AND (@RecommendationState is NULL OR @RecommendationState=REC.[State])
    );

进入:

delete from ADVICESEQUENCETRANSACTION
where ADVICESEQUENCETRANSACTION.id in (
  select TR.id from ADVICESEQUENCETRANSACTION TR
  inner join ACCOUNTDESCRIPTIONITEM IT on TR.ACCOUNTDESCRIPTIONITEMID = IT.id
  inner join ACCOUNTDESCRIPTION ACC on IT.ACCOUNTDESCRIPTIONID = ACC.id
  inner join RECOMMENDATIONDESCRIPTION RD on ACC.RECOMMENDATIONDESCRIPTIONID = RD.id
  inner join RECOMMENDATION REC on REC.id = RD.RECOMMENDATIONID
  where TR.AdviceSequenceId = @AdviceSequenceId AND (@RecommendationState is NULL OR @RecommendationState=REC.[State])
    );

我删除了最后一个连接。但如果我这样做，我就会再次陷入僵局!再次，我不知道为什么......

感谢您的指教:)

Answer 1

在 WHERE 子句中使用复杂的复合联接通常会导致问题。 SQL Server 使用以下逻辑处理顺序处理子句( View here ):

1. 来自
2. 开启
3. 加入
4. 哪里
5. 分组依据
6. 使用多维数据集或使用 ROLLUP
7. 拥有
8. 选择
9. 独特
10. 排序依据
11. 顶部

使用 View 、派生表或 View 可以大大减少获得所需结果所需的迭代 (TABLE) 扫描次数，因为查询中的重点可以更好地与逻辑执行顺序对齐。每个派生表(或 View )的 FROM 子句首先执行，限制传递给 ON 原因的结果集，然后是 JOIN 子句等等，因为您将参数传递给“内部 FROM”而不是“最外面的地方”。

所以你的代码可能看起来像这样:

delete from (SELECT   ADVICESEQUENCETRANSACTION
             FROM     (SELECT tr.id 
                      FROM ADVICESEQUENCETRANSACTION WITH NOLOCK
                      WHERE AdviceSequenceId = @AdviceSequenceId
                      )TR
            INNER JOIN (SELECT [NEXT COLUMN]
                        FROM [NEXT TABLE] WITH NOLOCK
                        WHERE COLUMN = [PARAM]
                        )B
            ON        TR.COL = B.COL            
            )ALIAS
            WHERE [COLUMN] = COL.PARAM
);

等等...(我知道代码无法剪切和粘贴，但它应该能够传达总体思路)

通过这种方式，您首先将参数传递给“内部查询”，预加载有限的结果集(特别是如果您应该使用 View )，然后向外工作。在适当的地方使用锁定提示也将有助于防止您可能遇到的一些问题。此技术还可以帮助使执行计划更有效地帮助您诊断 block 的来源(如果您仍然有 block )。