4

As I was debugging a deadlock, I noticed that in the xml's resource-list, the first and third object locks are the same. Is there some reason for this?

The <resource-list> verbatim:

 <resource-list>
  <objectlock lockPartition="0" objid="580509447" subresource="FULL" dbid="7" objectname="censoredV2.dbo.pt_sales" id="lock1929b2d5500" mode="Sch-M" associatedObjectId="580509447">
   <owner-list>
    <owner id="process194df0e5468" mode="Sch-M" />
   </owner-list>
   <waiter-list>
    <waiter id="process1947a066ca8" mode="Sch-M" requestType="wait" />
   </waiter-list>
  </objectlock>
  <objectlock lockPartition="0" objid="94623380" subresource="FULL" dbid="7" objectname="censoredV2.dbo.pt" id="lock1929a8cf480" mode="IX" associatedObjectId="94623380">
   <owner-list>
    <owner id="process1907da684e8" mode="IX" />
   </owner-list>
   <waiter-list>
    <waiter id="process194df0e5468" mode="Sch-M" requestType="wait" />
   </waiter-list>
  </objectlock>
  <objectlock lockPartition="0" objid="580509447" subresource="FULL" dbid="7" objectname="censoredV2.dbo.pt_sales" id="lock1929b2d5500" mode="Sch-M" associatedObjectId="580509447">
   <owner-list>
    <owner id="process1947a066ca8" mode="Sch-M" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="process1907da684e8" mode="IX" requestType="wait" />
   </waiter-list>
  </objectlock>
 </resource-list>

1 Answer 1

6

It is not the same. If you look at it more closely the "owner id" is different. This is 'Schema Modification' lock. SQL Server only allows one schema modification lock.

On the first one it is owned by process194df0e5468 and it is granted. On the third one, it is requested by process1947a066ca8 and waiting, since the first one already has the lock.

This is nice reading about this lock by Michael J.Swart.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.