0

I've encountered a deadlock on SQL Server 2014, and created a deadlock report using extended events. Below is an excerpt from the report.

What does it mean for objectlock(objid="554979041") to have multiple owners (namely, process14f3f2108 and processf32b5848 (twice)). I understand how a lock may have multiple waiters, but what does it mean to have multiple owners? I thought that a lock could only be owned by a single process, and that all other processes interested in the lock would have to wait for the lock. What am I missing?

<deadlock>

    ... content deleted ...

  <resource-list>
      <keylock hobtid="72057633537982464" dbid="15" objectname="BasketHeader" indexname="UX_BasketHeader_BasketID_Account" id="lock2d9b59f80" mode="X" associatedObjectId="72057633537982464">
        <owner-list>
          <owner id="process203dad848" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process14f3f2108" mode="RangeS-U" requestType="wait" />
        </waiter-list>
      </keylock>
      <objectlock lockPartition="0" objid="554979041" subresource="FULL" dbid="15" objectname="BasketItem" id="lock1e64cbc80" mode="IX" associatedObjectId="554979041">
        <owner-list>
          <owner id="process14f3f2108" mode="IX" />                     !!!     OWNER 1
        </owner-list>
        <waiter-list>
          <waiter id="processf32b5848" mode="X" requestType="convert" />
        </waiter-list>
      </objectlock>
      <objectlock lockPartition="0" objid="554979041" subresource="FULL" dbid="15" objectname="BasketItem" id="lock1e64cbc80" mode="IX" associatedObjectId="554979041">
        <owner-list>
          <owner id="processf32b5848" mode="IX" />                      !!!     OWNER 2
          <owner id="processf32b5848" mode="X" requestType="convert" /> !!!     OWNER 3
        </owner-list>
        <waiter-list>
          <waiter id="process203dad848" mode="IX" requestType="wait" />
        </waiter-list>
      </objectlock>
    </resource-list>
</deadlock>

1 Answer 1

1

The processes use and request different types of locks on the tables

  • Exclusive (X)
  • Shared (S)
  • Intent exclusive (IX)
  • Intent shared (IS)
  • Shared with intent exclusive (SIX)

And the compatability matrix looks like this:

      (X)  (S)  (IX) (IS) (SIX)
(X)    ✗    ✗    ✗    ✗    ✗
(S)    ✗    ✓    ✗    ✓    ✗
(IX)   ✗    ✗    ✓    ✓    ✗
(IS)   ✗    ✓    ✓    ✓    ✓
(SIX)  ✗    ✗    ✗    ✓    ✗ 

process203dad848 (A) has a X (exclusive) lock on the BasketHeader table and is requesting an IX (Intent Exclusive) lock on the BasketItem table

process14f3f2108 (B) has an IX (Intent Exclusive) lock on BasketItem and is waiting to get a RangeS-U on BasketHeader.

processf32b5848 (C) has an IX lock on BasketItem and is waiting for it to be turned into a X lock

As you can see on the table above IX locks are compatible so seeing two of those on the BasketItem table is perfectly normal.

The RangeS-U makes this interesting as range locks only happen when you are running transactions if you are using serializable isolation level

Whats happening is that (A) holds an exclusive lock on BasketHeader and is waiting for an IntentExclusive lock on BasketItem. (B) is running in serializable isolation level and and is waiting for getting an exclusive Shared lock on BasketHeader and holds an IntentExclusive lock on BasketItem While (C) is Converting its IX lock to X lock.

(B) who is running in serializable will not be able to continue unless it gets its RangeS-U on BasketHeader and will not yield for (C). (A) cannot continue until it can get its lock on BasketItem and there you have your deadlock

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.