SQL Server中一个非常奇怪的密钥锁死锁

Question

非常奇怪的是，当有两个连接在不同的行上运行下面的事务时，会检测到死锁。为什么查询优化器要求事务T1具有资源KEY2，即事务T2更新的行？

钥匙1（第1排）钥匙：5:72057594048348160（150fa2746fc）

钥匙2（第2排）钥匙：5:72057594048348160（1bec117e39ae）

2个事务同时更新不同的行。假设他们没有用UPLOCK和ROWLOCK互相干扰？

BEGIN TRAN
SELECT * FROM TABLE WITH(UPLOCK, ROWLOCK) WHERE PK_COL1 = ? and PK_COL2 = ?
UPDATE TABLE SET COL3 = ? WHERE PK_COL1 = ? and PK_COL2 = ?
END

以下是死锁列表

<process id="process10e7502c8" taskpriority="0" logused="0" waitresource="KEY: 5:72057594048348160 (150fa2746afc)" 
....
<process id="process10e750988" taskpriority="0" logused="0" waitresource="KEY: 5:72057594048348160 (1bec117e39ae)" 
...
<resource-list>
  <keylock hobtid="72057594048348160" dbid="5" objectname="" indexname="" id="locka6b73300" mode="U" associatedObjectId="72057594048348160">
    <owner-list>
      <owner id="process10e750988" mode="U" />
    </owner-list>
    <waiter-list>
      <waiter id="process10e7502c8" mode="U" requestType="wait" />
    </waiter-list>
  </keylock>
  <keylock hobtid="72057594048348160" dbid="5" objectname="" indexname="" id="locka5319b80" mode="U" associatedObjectId="72057594048348160">
    <owner-list>
      <owner id="process10e7502c8" mode="U" />
    </owner-list>
    <waiter-list>
      <waiter id="process10e750988" mode="U" requestType="wait" />
    </waiter-list>
  </keylock>
</resource-list>

以下是发生死锁时的sp_lock结果

spid    dbid    ObjId   IndId   Type    Resource    Mode    Status
51  5   0   0   DB                                      S   GRANT
52  6   0   0   DB                                      S   GRANT
53  4   0   0   DB                                      S   GRANT
54  5   0   0   DB                                      S   GRANT
54  5   1941581955  0   TAB                                     IX  GRANT
54  5   1941581955  1   KEY (1bec117e39ae)                      U   GRANT
54  5   0   0   MD  4(6:0:0)                            Sch-S   GRANT
54  5   1941581955  1   KEY (150fa2746afc)                      U   WAIT
54  5   1941581955  1   PAG 1:73626                             IU  GRANT
57  5   0   0   DB                                      S   GRANT
58  6   0   0   DB                                      S   GRANT
58  6   0   0   APP 16384:[Repl-LogRead]:(04dddec9)     X   GRANT
59  5   0   0   DB                                      S   GRANT
60  6   0   0   DB                                      S   GRANT
61  5   0   0   DB                                      S   GRANT
62  5   0   0   DB                                      S   GRANT
63  4   0   0   DB                                      S   GRANT
64  4   0   0   DB                                      S   GRANT
65  5   0   0   DB                                      S   GRANT
65  5   1941581955  1   KEY (1bec117e39ae)                      U   WAIT
65  5   1941581955  1   PAG 1:73626                             IU  GRANT
65  5   0   0   MD  4(6:0:0)                            Sch-S   GRANT
65  5   1941581955  1   KEY (150fa2746afc)                      U   GRANT
65  5   1941581955  0   TAB                                     IX  GRANT
66  6   0   0   APP 16384:[DC1ISGSD03I]:(152e28ac)     X   GRANT
66  6   0   0   DB                                      S   GRANT
67  1   1131151075  0   TAB                                     IS  GRANT
69  5   0   0   DB                                      S   GRANT

如果添加一个未聚集索引，该索引具有与主键创建的聚集索引相同的列和顺序，那么死锁问题就会消失。但是，为什么更新聚集索引键上的行需要更新其他聚集索引键的锁？

如果我有任何误解，请纠正我。如有任何答案，我们将不胜感激。

表模式描述如下SQL脚本

create table [dbo].[TABLE1]
(
[PK_COL1] char(10) not null,
[PK_COL2] char(10) not null,
[COL3] char(10) not null,
PRIMARY KEY ([PK_COL1],[PK_COL2])
);

Answer 1

添加READPAST提示：

BEGIN TRAN
    SELECT * FROM TABLE WITH(UPDLOCK, ROWLOCK, READPAST) 
    WHERE PK_COL1 = ? and PK_COL2 = ?
    UPDATE TABLE SET COL3 = ? WHERE PK_COL1 = ? and PK_COL2 = ?
COMMIT TRAN

正如@CJBS在评论中指出的那样，提示的使用应限于需要提示的情况，并且您完全理解其后果。

上面例子中的提示出现在使用表作为队列的例子中，在这个例子中，您希望选择一行，对其进行锁定，在单独的语句中更新（例如处理后删除行），但也允许其他读取器读取超过保持的更新锁定的行。

Answer 2

我终于发现需要非聚集索引来避免死锁。

CREATE NONCLUSTERED INDEX [TABLE1_IDX1] ON [TABLE1]([PK_COL1], [PK_COL2]);