sql-server - 为什么选择查询需要 IX 锁？

Question

在检查下面的死锁图时，我发现一个 SELECT 查询(仅在第一个进程 process569f048 执行的 SP 内部查询)和一个 UPDATE 查询形成死锁； SELECT 查询需要 IX 锁。

SELECT 在什么情况下需要这样的锁？我该怎么做才能避免死锁？

这是 SELECT 查询:

SELECT TOP (@p_takeCount)
     t.Id
    ,s.Column2
    ,t.STATUS
    ,t.Column3
    ,t.Column4
FROM Table2 t WITH (INDEX (IX_Table2))
INNER JOIN Table1 s ON s.Id = t.ParentId
WHERE t.STATUS != 0
    AND t.Column5 IS NULL
    AND s.SomeId = @p_someId
    AND s.Category = 2
ORDER BY t.id

计划如下:

这是UPDATE查询:

update Table2
set [Status] = @0, Column5 = null, Column6 = @1
where ([Id] = @2)

计划如下:

这是死锁图:

<deadlock>
  <victim-list>
    <victimProcess id="process569f048" />
  </victim-list>
  <process-list>
    <process id="process569f048" taskpriority="0" logused="0" waitresource="PAGE: 5:1:3017144" waittime="2867" ownerId="964271246" transactionname="SELECT" lasttranstarted="2017-01-29T10:10:49.643" XDES="0x800f9d20" lockMode="S" schedulerid="10" kpid="10108" status="suspended" spid="70" sbid="2" ecid="2" priority="0" trancount="0" lastbatchstarted="2017-01-29T10:10:49.643" lastbatchcompleted="2017-01-29T10:10:49.643" clientapp="EntityFramework" hostname="LOCALHOST" hostpid="4936" isolationlevel="read committed (2)" xactid="964271246" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="" line="17" stmtstart="1298" stmtend="1954" sqlhandle="0x03000500d21f5e3dd6d19700cca400000100000000000000" />
      </executionStack>
      <inputbuf />
    </process>
    <process id="process8ee3dc8" taskpriority="0" logused="17956" waitresource="PAGE: 5:1:3017343" waittime="2864" ownerId="964271345" transactionname="user_transaction" lasttranstarted="2017-01-29T10:10:49.667" XDES="0xafdbb03b0" lockMode="IX" schedulerid="17" kpid="9468" status="suspended" spid="61" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-01-29T10:10:49.703" lastbatchcompleted="2017-01-29T10:10:49.703" clientapp="EntityFramework" hostname="LOCALHOST" hostpid="20696" loginname="dbuser_d" isolationlevel="read committed (2)" xactid="964271345" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="" line="1" stmtstart="74" sqlhandle="0x02000000403aaa03bd8879de1c73d49641f1f81b6ca095af" />
        <frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000" />
      </executionStack>
      <inputbuf>
        (@0 tinyint,@1 varchar(64),@2 bigint)update [dbo].[Table2]
        set [Status] = @0, [Column5] = null, [Column6] = @1
        where ([Id] = @2)
      </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <pagelock fileid="1" pageid="3017144" dbid="5" objectname="" id="lockc296c6380" mode="IX" associatedObjectId="72057594073317376">
      <owner-list>
        <owner id="process8ee3dc8" mode="IX" />
      </owner-list>
      <waiter-list>
        <waiter id="process569f048" mode="S" requestType="wait" />
      </waiter-list>
    </pagelock>
    <pagelock fileid="1" pageid="3017343" dbid="5" objectname="" id="lockd33965a80" mode="S" associatedObjectId="72057594073317376">
      <owner-list>
        <owner id="process569f048" mode="S" />
      </owner-list>
      <waiter-list>
        <waiter id="process8ee3dc8" mode="IX" requestType="wait" />
      </waiter-list>
    </pagelock>
  </resource-list>
</deadlock>

索引详情:

[PK_Table2] PRIMARY KEY CLUSTERED ([Id] ASC);
[IX_Table2]([Column5] ASC, [Status] ASC) INCLUDE ( [Id],[ParentId],[Column3],[Column4]) WHERE ([Column5] IS NULL);

ID 为 72057594073317376 的对象 (associatedObjectId) 为:[IX_Table2]

Answer 1

如果仔细观察图表，您会发现:

读者，进程:process569f048 在页面:3017343 上有一个共享锁，正在等待对象 72057594073317376 的页面:3017144 上的共享锁

更新进程:process8ee3dc8 在页面 3017144 上有 IX 锁，正在等待对象 72057594073317376 的 3017343 上的 IX 锁。

这就是死锁所在。

要查找引用的对象，您可以使用从 stack overflow answer here 中收集的以下信息object id 指的是 hobts (Heap Or Binary Tree)，在 sys.partitions 中找到。

在数据库 5 中尝试以下查询，您将找到受影响的对象和索引。

SELECT hobt_id, object_name(p.[object_id]), index_id 
FROM sys.partitions p 
WHERE hobt_id = 72057594073317376

正如我在评论中指出的那样，如果表具有聚簇索引，则所有非聚簇索引都将聚簇键作为索引的一部分，因此在更新聚簇键时需要进行更新。我怀疑这个对象将是需要更新的二级索引，可能是因为它是最后一页。