Interpret deadlock and fix it

Question

I need to understand how this is possible, i.e. I would like to understand mechanics of this.

Victim process is a big union-type query involves about 6 tables (Entity Frameworks)

Process on a right is an insert/update batch consisting of multiple statements.

I can't give SQL since they truncated in deadlock event.

I've seen deadlocks involving 2/3 tables and write operations, but I can't interpret this one. What happening? I see index involved, this is valid index - I need it.

I get this deadlock maybe 3 times a week on a system where about 100 such selects per minute and about 1 such insert per second

<deadlock-list>
 <deadlock victim="process2fdc4e088">
  <process-list>
   <process id="process2fdc4e088" taskpriority="0" logused="0" waitresource="KEY: 28:72057594054049792 (7303a0672d4e)" waittime="713" ownerId="928827354" transactionname="user_transaction" lasttranstarted="2012-06-27T06:32:35.030" XDES="0x9982e3b0" lockMode="S" schedulerid="3" kpid="15300" status="suspended" spid="84" sbid="2" ecid="0" priority="0" trancount="1" lastbatchstarted="2012-06-27T06:32:35.137" lastbatchcompleted="2012-06-27T06:32:35.030" clientapp="sss" hostname="aaa" hostpid="4080" loginname="aaa" isolationlevel="read committed (2)" xactid="928827354" currentdb="28" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" sqlhandle="0x020000009b2fc809e2d580f750aacd7697bcc9fab8b85647">
SELECT 
[UnionAll2].[C3] AS [C1], 
[UnionAll2].[C4] AS [C2], 
[UnionAll2].[C5] AS [C3], 
[UnionAll2].[C6] AS [C4], 
[UnionAll2].[C7] AS [C5], 
[UnionAll2].[C8] AS [C6], 
[UnionAll2].[C9] AS [C7], 
[UnionAll2].[C10] AS [C8], 
[UnionAll2].[C11] AS [C9], 
[UnionAll2].[C12] AS [C10], 
[UnionAll2].[C13] AS [C11], 
[UnionAll2].[C14] AS [C12], 
[UnionAll2].[C15] AS [C13], 
[UnionAll2].[C16] AS [C14], 
[UnionAll2].[C17] AS [C15], 
[UnionAll2].[C18] AS [C16], 
[UnionAll2].[C19] AS [C17], 
[UnionAll2].[C20] AS [C18], 
[UnionAll2].[C21] AS [C19], 
[UnionAll2].[C22] AS [C20], 
[UnionAll2].[C23] AS [C21], 
[UnionAll2].[C24] AS [C22], 
[UnionAll2].[C25] AS [C23], 
[UnionAll2].[C1] AS [C24], 
[UnionAll2].[C26] AS [C25], 
[UnionAll2].[C27] AS [C26], 
[UnionAll2].[C28] AS [C27], 
[UnionAll2].[C29] AS [C28], 
[UnionAll2].[C30] AS [C29], 
[UnionAll2].[C31] AS [C30], 
[UnionAll2].[C32] AS [C31], 
[UnionAll2].[C33] AS [C32], 
[UnionAll2].[C34] AS [C33], 
[UnionAll2].[C35] AS [C34], 
[UnionAll2].     </frame>
    </executionStack>
    <inputbuf>
SELECT 
[UnionAll2].[C3] AS [C1], 
[UnionAll2].[C4] AS [C2], 
[UnionAll2].[C5] AS [C3], 
[UnionAll2].[C6] AS [C4], 
[UnionAll2].[C7] AS [C5], 
[UnionAll2].[C8] AS [C6], 
[UnionAll2].[C9] AS [C7], 
[UnionAll2].[C10] AS [C8], 
[UnionAll2].[C11] AS [C9], 
[UnionAll2].[C12] AS [C10], 
[UnionAll2].[C13] AS [C11], 
[UnionAll2].[C14] AS [C12], 
[UnionAll2].[C15] AS [C13], 
[UnionAll2].[C16] AS [C14], 
[UnionAll2].[C17] AS [C15], 
[UnionAll2].[C18] AS [C16], 
[UnionAll2].[C19] AS [C17], 
[UnionAll2].[C20] AS [C18], 
[UnionAll2].[C21] AS [C19], 
[UnionAll2].[C22] AS [C20], 
[UnionAll2].[C23] AS [C21], 
[UnionAll2].[C24] AS [C22], 
[UnionAll2].[C25] AS [C23], 
[UnionAll2].[C1] AS [C24], 
[UnionAll2].[C26] AS [C25], 
[UnionAll2].[C27] AS [C26], 
[UnionAll2].[C28] AS [C27], 
[UnionAll2].[C29] AS [C28], 
[UnionAll2].[C30] AS [C29], 
[UnionAll2].[C31] AS [C30], 
[UnionAll2].[C32] AS [C31], 
[UnionAll2].[C33] AS [C32], 
[UnionAll2].[C34] AS [C33], 
[UnionAll2].[C35] AS [C34], 
[UnionAll2]    </inputbuf>
   </process>
   <process id="processd5471948" taskpriority="0" logused="1204" waitresource="PAGE: 28:1:102676" waittime="864" ownerId="928827514" transactionname="user_transaction" lasttranstarted="2012-06-27T06:32:35.363" XDES="0x1e0f0d3f0" lockMode="IX" schedulerid="4" kpid="19116" status="suspended" spid="77" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-06-27T06:32:35.377" lastbatchcompleted="2012-06-27T06:32:35.377" clientapp="DitatTMS" hostname="sss" hostpid="4080" loginname="sss" isolationlevel="read committed (2)" xactid="928827514" currentdb="28" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="50" stmtend="250" sqlhandle="0x020000003859c72d14e4f731cc12f95e6e3ed8b75668b3b9">
update [dbo].[MBLTripStopAttribute]
set [AttributeValue] = @0
where ([TripStopAttributeKey] = @1)     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@0 varchar(max) ,@1 int)update [dbo].[MBLTripStopAttribute]
set [AttributeValue] = @0
where ([TripStopAttributeKey] = @1)
select [RowVersion]
from [dbo].[MBLTripStopAttribute]
where @@ROWCOUNT &gt; 0 and [TripStopAttributeKey] = @1    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594054049792" dbid="28" objectname="DitatApp.dbo.MBLTripDriver" indexname="IX_MBLTripDriver_UpdatedOn_DriverKey" id="lock873ee900" mode="X" associatedObjectId="72057594054049792">
    <owner-list>
     <owner id="processd5471948" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process2fdc4e088" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <pagelock fileid="1" pageid="102676" dbid="28" objectname="DitatApp.dbo.MBLTripStopAttribute" id="lock92d2c200" mode="SIU" associatedObjectId="72057594055163904">
    <owner-list>
     <owner id="process2fdc4e088" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="processd5471948" mode="IX" requestType="convert"/>
    </waiter-list>
   </pagelock>
  </resource-list>
 </deadlock>
</deadlock-list>

Answer 1

Looks like process 84 holds a shared-intent-update pagelock A. It is waiting for a shared key lock on B.

Process 77 holds an exclusive key lock on B. It's waiting for an intent exclusive page lock on A.

The next step is retrieving the SQL statements these processes were executing.