為什麼選擇查詢需要 IX 鎖？

在檢查下面的死鎖圖時，我發現一個SELECT查詢（只有第一個程序正在執行的 SP 內的查詢process569f048）和一個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]

死鎖是由於兩個查詢爬取頁面而發生的。SELECT 在 S 模式下有頁面 3017343，並希望在 S 模式下有頁面 3017144。UPDATE 在 IX 中有第 3017144 頁，並希望在 IX 模式下有第 3017343 頁。這是濫用表作為隊列的典型症狀（提示：狀態列）。有關如何正確建模、避免死鎖的資訊，請參閱使用表作為隊列。單獨的事件形成狀態。

查看死鎖圖，SELECT 命令（process569f048）實際上採用共享鎖“S”，這是讀取數據的程序所持有的預設鎖。UPDATE 命令確實採用了意向鎖“IX”。

查看這篇Technet 文章

引用自：https://dba.stackexchange.com/questions/162724