logo标签列表

解释死锁并修复它

sql-serversql-server-2008entity-frameworkdeadlock
3
我需要了解这种情况是如何发生的，即我想了解其机制。
受害者进程是一个大型联合查询，涉及约6个表（实体框架）。
右侧的进程是由多个语句组成的插入/更新批处理。
由于死锁事件被截断，因此我无法提供SQL。
我曾看到涉及2/3个表和写操作的死锁，但我无法解释这个。发生了什么？我看到索引参与其中，这是有效的索引 - 我需要它。
在每分钟约有100个这样的选择和每秒约有1个这样的插入的系统上，我可能会遇到这种死锁3次左右。
<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>
图片被截断了吗？我看到箭头从侧面进来。 - Andomar
使用死锁源XML。该图像通常含糊不清。 - Remus Rusanu
@Andomar 我把图像分成了两部分 - 因为它太小了。在视觉上将顶部和底部部分合并为左侧和右侧 - 中间部分共享。 - katit
1个回答
0

看起来进程84持有一个共享意图更新页锁A。它正在等待B上的共享键锁。

进程77在B上持有一个排他键锁。它正在等待A上的意图排他页锁。

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

我可以发布近似的SQL查询，左侧的查询几乎完全正确，但右侧的查询仅用于“说明”，因为该事务中插入/更新的数量会有所变化。这样做有助于更好地诊断吗？就像我说的，XML没有帮助——查询太大而被截断了。 - katit
@katit：我们需要的是所有查询，而不仅仅是当前查询，但是所有属于该事务的查询。如果每周发生三次，您可以启用Trace标志3604，然后是1204。 - Andomar
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接