3

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

enter image description here

2
  • 1
    I think the number 8 is your degree of parallelism, you see 7 granted + 1 convert (for any thread)
    – sepupic
    Apr 3 at 8:31
  • I confirm that the maximum degree of parallelism has been set to eight. Apr 3 at 13:06

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.