解释死锁并修复它 [英] Interpret deadlock and fix it

问题描述

我需要了解这是可能的，即我想了解这个机制。

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

受害者进程是一个大型联合类型的查询涉及约6个表（实体框架）

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.

我不能给SQL，因为它们在死锁事件中被截断。

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

我已经看到涉及2/3表和写操作的死锁，但我不能解释这一个。发生了什么？我看到索引涉及，这是有效的索引 - 我需要它。

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.

我每周可能会遇到这样的僵局，每分钟大约有100个这样的选择，每秒大约有1个这样的插页。

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>

推荐答案

看起来进程84持有共享意图更新pagelock A.它正在等待B上的共享密钥锁定。

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

进程77在B上持有专用密钥锁。它正在等待A上的意图专用页面锁定。

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

下一步是检索这些进程正在执行的SQL语句。

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

这篇关于解释死锁并修复它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！