question

MarkGordon-2676 avatar image
0 Votes"
MarkGordon-2676 asked MarkGordon-2676 commented

Deadlock XML - Mode displays

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



sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

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>
0 Votes 0 ·

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered MarkGordon-2676 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Erland.

0 Votes 0 ·