由Keylock和Pagelocks引起的死锁

Question

我有一个运行select、update和insert查询的数据库。有时，在更新和选择表中的数据时，它会给我造成死锁(键锁)，它认为select查询是受害者。在对我们的查询进行了一些研究和检查之后，我们添加并删除了一些有助于加快查询速度的索引。现在，在添加索引之后，我将获得相同的select和update查询的死锁(页面锁)。为什么会发生这种情况？有没有办法减少这些死锁？

死锁在

SELECT COUNT(*) AS [Id Count ]
FROM   TABLE
WHERE  (
           ([name]) = 'abc'
           AND (([Id]) <> '4284167' OR [Id] IS NULL)
           AND ([Stage] <> 0)
           AND (
                   ([Result]) = 'P'
                   OR ([Result] IS NULL OR [Result] = '')
                   OR ([Result]) = 'Delay'
               )
       )

...and

UPDATE TABLE
SET    [Result] = 'C'
WHERE  [Id] = 4284027
       AND [Result] = 'P'

在修改索引之前，这些查询由于密钥锁(Index)而陷入僵局。

索引涉及到Keylock

的死锁

1. 主键，列上的非群集
   • ID

2. 列上的聚集索引
   • [Date]
   • [Time]

3. 列上的非聚集索引
   • [Result]

4. 列上的非聚集索引
   • [Stage]

涉及死锁的对象是[Date][Time]上的聚集索引和[Result]上的非聚集索引。

索引涉及到PageLocks

的死锁

1. 列上的主键、聚集索引
   • ID

2. 列上的非聚集索引
   • [Date]
   • [Time]

3. 列上的非聚集索引：
   • Reffd
   • Date
   • Time
   • typeQuery
   • Result
   • Stage
   • Answer (包括列)
   • FollowUpdate (包括列)

涉及死锁的对象是[Id]和页面上的主键

密钥锁- Pagelock的

查询计划

1. https://www.brentozar.com/pastetheplan/?id=ByK26kfyj[1]
2. https://www.brentozar.com/pastetheplan/?id=SyzygeGJi

Pagelock - Pagelock

的

查询计划1.https://www.brentozar.com/pastetheplan/?id=S1wSNXzkj 2.https://www.brentozar.com/pastetheplan/?id=H1WhXmGys

Answer 1

因为，死锁图还没有包含在问题中。我试着根据这两个查询和更改的索引给出一些建议。在这两个查询中：

1.SELECT COUNT(*) as [Id Count ] FROM table WHERE ( ([name] ) = 'abc' AND (([Id] ) <> '4284167' or [Id] is null ) AND ([Stage]<>0) AND ( ([Result] ) = 'P' OR ([Result] Is Null OR [Result] = '' ) OR ([Result] ) = 'Delay' ))

2.update table set [Result]='C' where [Id] = 4284027 and [Result] ='P'

第二个查询非常好，因为Id是主键聚集索引，您不能为这个查询做任何改进。

对于第一个查询，这有许多不等式操作符和OR操作符，这将限制您的索引进行搜索操作，并且需要进行基于范围的扫描。

只有我看到的可以有选择性的列才是具有相等条件的name列。我更喜欢创建一个索引，名称是第一列，然后是列(阶段和结果)，然后是基于选择性的列--除了这三个列，不需要更多的列。因此，索引定义可以如下所示：

create index IX_SE on [dbo].[tablename](name, stage, Result)

为select *提供了执行计划，而不是select计数(*)-这并没有多大帮助。

索引非聚集索引- 日期，时间对于这两个查询并不是非常必要的，但是，在您的环境中可以在其他查询中有所帮助。

此外，如果可能，请在执行比较时使用适当的数据类型来删除查询中的隐式转换，这意味着Nvarchar应该仅与nvarchar值而不是varchar进行比较。

希望这能让你做出正确的判断。