Deadlock XML - Mode displays

Question

Deadlock XML - Mode displays

Mark Gordon 926

Fellow SQL dBAa,

I am trying to understand the difference between the MODE values in the XML dump.

I have solved the deadlock but trying to understand why it displayed a certain way in the graph or even shows what it does in the XML.

User 1 had an exclusive lock on table A - which was for an update. User 2 wanted a shared lock on table A for a Select (The select was reading table A and B).

User 2 had a shared lock on table B for a select (believe to be same select as above). User1 wanted an exclusive lock on table B and, if it got it, was going to do an update.

In the xml, when I look at resource section, I see a keylock with MODE of U but a U does not appear on the Modes for the two connections/processes - one has an S and one has an X.

What makes the MODE on the keylock lines different then the owner line and waiter line? Neither of those had a U.

thx

MG

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2021-05-04T21:14:32.767+00:00

Could help if you post the XML, so that we get a better understanding of what you are talking about.

Mark Gordon 926

Hi Erland,
Thx, here is what I see. The U is what is throwing me. Why does hte key have a U when the owner of the lock is an S?

<keylock hobtid="72057599548719104" dbid="5" objectname="dbo.DT_CLA" 
indexname="PK_DT_CLA" id="lock6b8971100" mode="U" associatedObjectId="72057599548719104">
  <owner-list>
    <owner id="process1c93079088" mode="S" />
  </owner-list>
  <waiter-list>
    <waiter id="process1bed1e4108" mode="X" requestType="convert" />
  </waiter-list>
</keylock>

Answer accepted by question author

0 additional answers

Your answer

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2021-05-04T21:14:32.767+00:00

Could help if you post the XML, so that we get a better understanding of what you are talking about.
Mark Gordon 926 Reputation points

2021-05-05T16:09:07+00:00

Hi Erland,
Thx, here is what I see. The U is what is throwing me. Why does hte key have a U when the owner of the lock is an S?

<keylock hobtid="72057599548719104" dbid="5" objectname="dbo.DT_CLA" indexname="PK_DT_CLA" id="lock6b8971100" mode="U" associatedObjectId="72057599548719104"> <owner-list> <owner id="process1c93079088" mode="S" /> </owner-list> <waiter-list> <waiter id="process1bed1e4108" mode="X" requestType="convert" /> </waiter-list> </keylock>

Answer 1

Erland Sommarskog 134.7K MVP Volunteer Moderator

The waiter process1bed1e4108 holds an update-lock on this index key.
Typically, an UPDATE statement will first acquire U locks on rows that are may be in for an update, and if they are, the U locks are converted to X locks, and that is what is happening here. Notice the part requestType="convert". The U lock is compatible with the S lock, so blocking occurred at that point. But it is not possible to convert it to an X lock, because of the other process.

Mark Gordon 926 Reputation points

2021-05-06T00:39:00.44+00:00

Thanks Erland.

Share via

Deadlock XML - Mode displays

0 additional answers

Your answer