I have a typical blocking scenario due to lock contention.
Lead blocker has these locks on an object.
<Object name="Table1" schema_name="dbo">
<Locks>
<Lock resource_type="OBJECT" request_mode="IS" request_status="GRANT" request_count="9" />
<Lock resource_type="PAGE" page_type="*" index_name="PK_Table1" request_mode="S" request_status="GRANT" request_count="8" />
</Locks>
</Object>
The blocked session has these locks. One of these is waiting to be converted on the same table and, hence, being blocked.
<Object name="Table1" schema_name="dbo">
<Locks>
<Lock resource_type="OBJECT" request_mode="SIX" request_status="GRANT" request_count="9" />
<Lock resource_type="OBJECT" request_mode="X" request_status="CONVERT" request_count="1" />
<Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="7" />
</Locks>
</Object>
Question: Why do I see GRANT and CONVERT at the same time? Is it saying SIX (Shared Intent Exclusive) needs to be CONVERTed to an X (Exclusive) lock? If yes, why is there already a GRANT and request_count of 7?
I see this in the sys.dm_tran_locks
DMV, which matches the XML; I am still confused about what to read from the data.
SELECT resource_type,
resource_associated_entity_id,
request_status,
request_mode,
request_session_id,
resource_description
FROM sys.dm_tran_locks
WHERE request_session_id = 90
AND request_mode = 'X';