通過 Select 和 Delete 查詢對同一對象進行 RID 和 Key 鎖定

具體來說，選擇查詢（左側的 spid 128）如何能夠在 Index 而不是行上獲得 S（共享）鎖？

此外，刪除查詢（右側的 SPID 121）如何能夠在對像上獲得 X（獨占）鎖定但無法在索引上獲得 X 鎖定？

死鎖 XML：

<deadlock>
<victim-list>
 <victimProcess id="processa4c4804e8" />
</victim-list>
<process-list>
 <process id="processa4c4804e8" taskpriority="0" logused="0" waitresource="RID: 19:1:6635214:0" waittime="7365" ownerId="2047519827" transactionname="SELECT" lasttranstarted="2018-07-31T06:16:10.353" XDES="0x17db223920" lockMode="S" schedulerid="27" kpid="9788" status="suspended" spid="128" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2018-07-31T06:16:10.353" lastbatchcompleted="2018-07-31T06:16:10.303" lastattention="1900-01-01T00:00:00.303" clientapp="jTDS" hostname="abc_Server" hostpid="123" loginname="XYZ\SVC_ABC" isolationlevel="read committed (2)" xactid="2047519827" currentdb="19" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
  <executionStack>
   <frame procname="adhoc" line="1" stmtstart="7018" stmtend="47282" sqlhandle="0x0200000045a5451b6c3d47c0d921b0f2f2ca56ca9a4b26fa0000000000000000000000000000000000000000">
unknown    </frame>
  </executionStack>
  <inputbuf>
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 bigint,@P3 bigint,@P4 nvarchar(4000),@P5 bigint,@P6 bigint,@P7 nvarchar(4000),@P8 bigint,@P9 bigint,@P10 nvarchar(4000),@P11 bigint,@P12 bigint,@P13 nvarchar(4000),@P14 bigint,@P15 bigint,@P16 int,@P17 int,@P18 nvarchar(4000),@P19 bigint,@P20 bigint,@P21 nvarchar(4000),@P22 bigint,@P23 bigint,@P24 nvarchar(4000),@P25 bigint,@P26 bigint,@P27 nvarchar(4000),@P28 bigint,@P29 bigint,@P30 decimal(38,0),@P31 decimal(38,1),@P32 int,@P33 int,@P34 int,@P35 int,@P36 decimal(38,1),@P37 int,@P38 int,@P39 decimal(38,0),@P40 int,@P41 int,@P42 nvarchar(4000),@P43 bigint,@P44 bigint,@P45 nvarchar(4000),@P46 bigint,@P47 bigint,@P48 nvarchar(4000),@P49 bigint,@P50 bigint,@P51 nvarchar(4000),@P52 bigint,@P53 bigint,@P54 nvarchar(4000),@P55 bigint,@P56 bigint,@P57 nvarchar(4000),@P58 bigint,@P59 bigint,@P60 nvarchar(4000),@P61 bit,@P62 int,@P63 decimal(38,0),@P64 decimal(38,0),@P65 decimal(38,0),@P66 decimal(38,0),@P67 decimal(38,0),@P68 decimal(38,0),@P69 decimal(38,0),@P70 nvarchar(4   </inputbuf>
 </process>
 <process id="processa4d92f848" taskpriority="0" logused="2176492" waitresource="KEY: 19:72057594649706496 (e37a97d3049b)" waittime="7773" ownerId="2047625413" transactionname="DELETE" lasttranstarted="2018-07-31T06:30:05.207" XDES="0x11bd931bf0" lockMode="X" schedulerid="7" kpid="13108" status="suspended" spid="121" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-07-31T06:30:05.207" lastbatchcompleted="2018-07-31T06:30:05.207" lastattention="2018-07-31T04:28:01.577" clientapp="jTDS" hostname="abc_Server" hostpid="123" loginname="XYZ\SVC_ABC" isolationlevel="read committed (2)" xactid="2047625413" currentdb="19" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
  <executionStack>
   <frame procname="adhoc" line="1" stmtstart="84" stmtend="412" sqlhandle="0x020000006f180e1fffe8b18ae2942c8e9498c66bdd56e2600000000000000000000000000000000000000000">
unknown    </frame>
  </executionStack>
  <inputbuf>
(@P0 nvarchar(4000),@P1 bigint,@P2 bigint)DELETE TOP(100000) FROM DIL2_OBJECT_NAME_88888888888888 WHERE rowStatus =  @P0  OR (expirationDate >  @P1  AND expirationDate <  @P2 )   </inputbuf>
 </process>
</process-list>
<resource-list>
 <ridlock fileid="1" pageid="6635214" dbid="19" objectname="Cache.dbo.DIL2_OBJECT_NAME_88888888888888" id="lock21441c4400" mode="X" associatedObjectId="72057594616020992">
  <owner-list>
   <owner id="processa4d92f848" mode="X" />
  </owner-list>
  <waiter-list>
   <waiter id="processa4c4804e8" mode="S" requestType="wait" />
  </waiter-list>
 </ridlock>
 <keylock hobtid="72057594649706496" dbid="19" objectname="Cache6.dbo.DIL2_OBJECT_NAME_88888888888888" indexname="idx_" id="lock156e38f280" mode="S" associatedObjectId="72057594649706496">
  <owner-list>
   <owner id="processa4c4804e8" mode="S" />
  </owner-list>
  <waiter-list>
   <waiter id="processa4d92f848" mode="X" requestType="wait" />
  </waiter-list>
 </keylock>
</resource-list>
</deadlock>

選擇查詢（左側的 spid 128）如何能夠在 Index 上獲得 S（共享）鎖，而不是在行上獲得鎖？

非聚集索引是與表分離的對象。所以使用索引的讀取器首先在索引鍵上獲得一個 S 鎖，然後從索引的葉級檢索行定位器（這裡是 RID）。要讀取行定位器引用的行，需要在 RID 或聚集索引鍵上附加鎖。

刪除查詢（右側的 SPID 121）如何能夠在對像上獲得 X（獨占）鎖定但無法在索引上獲得 X 鎖定？

類似地，對錶（這裡是堆）進行操作的查詢在 RID 上獲得 X 鎖以刪除行，然後需要在非聚集索引中的相應索引鍵上獲得 X 鎖。但是這裡的索引鍵被閱讀器鎖定了。如果您查看 DELETE 查詢計劃，它可能正在執行堆的表掃描以查找要刪除的行。

因此，這是一個普通的讀寫器死鎖，可以通過讓讀者使用行版本控制（SNAPSHOT 隔離，或將數據庫設置為 READ_COMMITTED_SNAPSHOT 模式）而不是使用共享鎖 (S) 來確保一致的視圖視圖來避免數據庫。

由於這似乎是一個 Denodo 記憶體表，而死鎖是應用程序錯誤，您可能需要向供應商提出有關修復的案例，並詢問他們是否支持對記憶體表使用行版本控制。如果他們不這樣做，我會對他們感到失望。

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