Sql-server – Blocked Process Report – which process does lockmode apply to

blockinglockingsql serversql-server-2016

I am analyzing a blocked process report and my blocking-process XML is as follows (some sections redacted for simplicity)

<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>

my blocked-process XML is as follows

<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>

I can find the database and object affected in the blocking-process node:

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

However, what I don't know is what lock the blocking process held. I can see lockMode="X"in the blocked process node so my question ism does lockMode="X" relate to the lock that the blocking process was holding or the lock that the blocked process was trying to get?

Best Answer

I just tried this out in my local copy of the Stack Overflow sample database. I ran the following query in one session:

BEGIN TRANSACTION;

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

Which was granted several X key locks in the users table (from EXEC sp_WhoIsActive @get_locks = 1):

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

I then ran this query in another session, which got blocked:

SELECT *
FROM dbo.Users
WHERE Id = 1;

According to the same sp_WhoIsActive query, this is now blocked and waiting on and S key lock from the Users table:

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

After 5 seconds, this produced a blocked process report event. The "blocked process" node has a lockmode of "S" so this seems to prove that the lockmode is the lock being requested by the blocked process (not the lock being held by the blocking process).

Here's the full report XML for reference. Unfortunately, it doesn't look like there's any way to tell the lock mode of the blocking process, other than consulting the lock compatibility matrix and inferring from that.

<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>
Related Question