阻塞程序報告 - lockmode 適用於哪個程序？

我正在分析一個阻塞程序報告，我的阻塞程序 XML 如下（為簡單起見，某些部分已編輯）

<blocking-process>
   <process status="running"
           spid="123"
           sbid="0"
           ecid="0"
           priority="0"
           trancount="2"
           lastbatchstarted="2019-11-22T03:00:00.347"
           lastbatchcompleted="2019-11-22T03:00:00.347"
           lastattention="1900-01-01T00:00:00.347"
           clientapp="MyAppName"
           hostname="MyServer"
           hostpid="6596"
           loginname="MyLogin2"
           isolationlevel="read committed (2)"
           xactid="184723528"
           currentdb="6"
           currentdbname="MyDB" 
           Timeout="4294967295"
           clientoption1="536870944"
           clientoption2="128056">
       <executionStack>

       </executionStack>
       <inputbuf>

       </inputbuf>
   </process>
</blocking-process>

我的阻塞程序 XML 如下

<blocked-process>
   <process id="process89e4c7f848"taskpriority="0"
               logused="256"
               waitresource="KEY: 6:72057594046447616 (4bc05a090cdd)"
           waittime="13730"
           ownerId="184723742"
           transactionname="user_transaction"
           lasttranstarted="2019-11-22T03:00:09.190"
           XDES="0x89d0786e80"
           lockMode="X"
           schedulerid="33"
           kpid="8176"
           status="suspended"
           spid="112"
           sbid="0"
           ecid="0"
           priority="0"
           trancount="2"
           lastbatchstarted="2019-11-22T03:00:09.207"
           lastbatchcompleted="2019-11-22T03:00:09.207"
           lastattention="1900-01-01T00:00:00.207"
           clientapp=".Net SqlClient Data Provider"
           hostname="MyServer"
           hostpid="1092"
           loginname="MyLogin"
           isolationlevel="read committed (2)"
           xactid="184723742"
           currentdb="6"
           currentdbname="MyDB"
           lockTimeout="4294967295"
           clientoption1="671088672"
           clientoption2="128056">
       <executionStack>

       </executionStack>
       <inputbuf>

       </inputbuf>
         </process>
</blocked-process>

我可以在阻塞程序節點中找到受影響的數據庫和對象：

waitresource="KEY: 6:72057594046447616 (4bc05a090cdd)"

但是，我不知道阻塞程序持有什麼鎖。我可以lockMode="X"在阻塞的程序節點中看到，所以我的問題確實lockMode="X"與阻塞程序所持有的鎖或被阻塞程序試圖獲得的鎖有關？

我剛剛在 Stack Overflow 範例數據庫的本地副本中進行了嘗試。我在一個會話中執行了以下查詢：

BEGIN TRANSACTION;

UPDATE dbo.Users
SET Reputation = 0
WHERE Id < 1000;

在 users 表中被授予了幾個 X 鍵鎖（來自EXEC sp_WhoIsActive @get_locks = 1）：

<Lock resource_type="KEY" index_name="PK_Users_Id" request_mode="X" request_status="GRANT" request_count="752" />

然後我在另一個會話中執行此查詢，該會話被阻止：

SELECT *
FROM dbo.Users
WHERE Id = 1;

根據相同的 sp_WhoIsActive 查詢，這現在被阻止並等待來自 Users 表的 S 鍵鎖定：

<Lock resource_type="KEY" index_name="PK_Users_Id" request_mode="S" request_status="WAIT" request_count="1" />

5 秒後，這產生了一個阻塞程序報告事件。“阻塞程序”節點的鎖定模式為“S”，因此這似乎證明了鎖定模式是被阻塞程序請求的鎖（而不是被阻塞程序持有的鎖）。

這是完整的報告 XML 供參考。不幸的是，除了查閱鎖兼容性矩陣並從中推斷之外，似乎沒有任何方法可以告訴阻塞程序的鎖定模式。

<blocked-process-report monitorLoop="587">
<blocked-process>
 <process id="process25a0d94a108" taskpriority="0" logused="0" waitresource="KEY: 6:72057594039173120 (8194443284a0)" waittime="5279" ownerId="61463" transactionname="SELECT" lasttranstarted="2019-12-18T08:41:40.603" XDES="0x259bbe81ac0" lockMode="S" schedulerid="3" kpid="41876" status="suspended" spid="54" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2019-12-18T08:41:40.600" lastbatchcompleted="2019-12-18T08:41:40.600" lastattention="1900-01-01T00:00:00.600" clientapp="Microsoft SQL Server Management Studio - Query" hostname="redacted" hostpid="39076" loginname="redacted" isolationlevel="read committed (2)" xactid="61463" currentdb="6" currentdbname="StackOverflow2010" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
  <executionStack>
   <frame line="1" stmtstart="24" stmtend="104" sqlhandle="0x02000000f1f6b108d092143d5a978eea9570bb0e96dc06ea0000000000000000000000000000000000000000" />
   <frame line="1" stmtend="74" sqlhandle="0x02000000669ce70e2be50eb44c883b0c891f586643da33df0000000000000000000000000000000000000000" />
  </executionStack>
  <inputbuf>
SELECT *
FROM dbo.Users
WHERE Id = 1;   </inputbuf>
 </process>
</blocked-process>
<blocking-process>
 <process status="sleeping" spid="53" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-12-18T08:39:20.630" lastbatchcompleted="2019-12-18T08:39:20.630" lastattention="1900-01-01T00:00:00.630" clientapp="Microsoft SQL Server Management Studio - Query" hostname="redacted" hostpid="39076" loginname="redacted" isolationlevel="read committed (2)" xactid="41842" currentdb="6" currentdbname="StackOverflow2010" lockTimeout="4294967295" clientoption1="671098976" clientoption2="390200">
  <executionStack />
  <inputbuf>
SET STATISTICS XML OFF   </inputbuf>
 </process>
</blocking-process>
</blocked-process-report>

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