我在我的大型Web应用程序中收到了很多死锁.
How to automatically re-run deadlocked transaction? (ASP.NET MVC/SQL Server)
在这里,我想重新运行死锁事务,但我被告知要摆脱僵局 – 它比试图赶上死锁要好得多.
所以我花了一整天的时间用sql Profiler,设置跟踪键等等.这就是我得到的.
有一个用户表.我有一个非常高的可用页面与以下查询(它不是唯一的查询,但它是导致麻烦的一个)
UPDATE Users SET views = views + 1 WHERE ID IN (SELECT AuthorID FROM Articles WHERE ArticleID = @ArticleID)
User = DB.Users.SingleOrDefault(u => u.Password == password && u.Name == username);
通常会发生死锁,并且第二个Linq-to-SQL查询被选为受害者,因此它不会运行,并且我的站点的用户会看到错误屏幕.
这是来自sql事件探查器捕获的.XDL图形的信息(它只是第一个死锁,它不是唯一的.整个列表都是巨大的.)：
<deadlock-list>
<deadlock victim="process824df048">
<process-list>
<process id="process824df048" taskpriority="0" logused="0" waitresource="PAGE: 7:1:13921" waittime="1830" ownerId="91418" transactionname="SELECT" lasttranstarted="2010-05-31T12:17:37.663" XDES="0x868175e0" lockMode="S" schedulerid="2" kpid="5076" status="suspended" spid="72" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2010-05-31T12:17:37.663" lastbatchcompleted="2010-05-31T12:17:37.663" clientapp=".Net sqlClient Data Provider" hostname="WIN-S41KV2CLS67" hostpid="6920" isolationlevel="read committed (2)" xactid="91418" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="74" sqlhandle="0x02000000de1cb30b5b2e40e31ffb345af3c7529430b559c2">
*password------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="process8765fb88" taskpriority="0" logused="216" waitresource="PAGE: 7:1:14196" waittime="1822" ownerId="91408" transactionname="UPDATE" lasttranstarted="2010-05-31T12:17:37.640" XDES="0x86978e90" lockMode="IX" schedulerid="2" kpid="5216" status="suspended" spid="73" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2010-05-31T12:17:37.557" lastbatchcompleted="2010-05-31T12:17:37.557" clientapp=".Net sqlClient Data Provider" hostname="WIN-S41KV2CLS67" hostpid="6920" loginname="sdfkj93jks9sl" isolationlevel="read committed (2)" xactid="91408" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="database.dbo.UpdateUserStats" line="31" stmtstart="1794" stmtend="2088" sqlhandle="0x03000700bac8836333e58f00879d00000100000000000000">
UPDATE Users
SET Views = Views + 1
WHERE ID IN (SELECT AuthorID FROM Articles WHERE ArticleID = @ArticleID) </frame>
<frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x01000700b7c78e0760dd3f81000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[UpdateUserStats] @UserID = @p0 </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p0 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[UpdateUserStats] @UserID = @p0 </inputbuf>
</process>
<process id="process86ce0988" taskpriority="0" logused="10000" waittime="1806" schedulerid="1" kpid="2604" status="suspended" spid="72" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2010-05-31T12:17:37.663" lastbatchcompleted="2010-05-31T12:17:37.663" clientapp=".Net sqlClient Data Provider" hostname="WIN-S41KV2CLS67" hostpid="6920" loginname="sdfkj93jks9sl" isolationlevel="read committed (2)" xactid="91418" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="74" sqlhandle="0x02000000de1cb30b5b2e40e31ffb345af3c7529430b559c2">
*password------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
*password-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="13921" dbid="7" objectname="database.dbo.Users" id="lock85535c80" mode="IX" associatedObjectId="72057594046382080">
<owner-list>
<owner id="process8765fb88" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="process824df048" mode="S" requestType="wait"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="14196" dbid="7" objectname="database.dbo.Users" id="lock8469f980" mode="SIU" associatedObjectId="72057594046382080">
<owner-list>
<owner id="process86ce0988" mode="S"/>
</owner-list>
<waiter-list>
<waiter id="process8765fb88" mode="IX" requestType="convert"/>
</waiter-list>
</pagelock>
<exchangeEvent id="Pipe894b0680" WaitType="e_waitPipeGetRow" nodeId="0">
<owner-list>
<owner id="process824df048"/>
</owner-list>
<waiter-list>
<waiter id="process86ce0988"/>
</waiter-list>
</exchangeEvent>
</resource-list>
</deadlock>
我读了很多关于死锁的内容……我不明白为什么这会造成死锁.
显然,这两个查询都经常运行.至少每秒一次.可能更频繁(在线300-400名用户).所以它们可以很容易地同时运行,但为什么会导致死锁呢？请帮忙.
谢谢
解决方法
您需要捕获死锁图.附加Profiler并捕获
Deadlock Graph Event类.保存.XDL图表并将该信息添加到您的帖子中.
在此之前,很明显你的DB.Users.SingleOrDefault查询至少要求Name上的索引,如果没有名称和密码：
CREATE INDEX idxUsersNamePassword on Users(Name,Password);
我希望用户已经拥有ID索引,而Article还有一个ArticleID索引,其中也包含AuthorID.假设Users.ID和Articles.ArticleID是它们各自的表中的PK,它们可能是各自的聚簇键,所以它是真的.值得仔细检查一下.
而且,由于我已经在你上一篇文章中回答过你,你决定继续前进并且没有回答,你应该考虑开启Snapshot Isolation：
ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON
除此之外,以明文形式存储密码是一个主要的#fail.
死锁信息后更新
有三个进程(请求)：
> A)… F048正在运行SELECT … FROM Users WHERE Password = …和Name = …
> B)… 0988正在运行SELECT … FROM Users WHERE Password = …和Name = …
> C)…正在运行UPDATE的FB88 ……
死锁周期是：
> C等待第IX页锁定,被A的S锁定阻止
> B等待Page S锁定,被C的IX锁定阻止
> A等待并行交换资源,被B阻止
因此,该循环是C-> A-> B-> C.
从涉及的两个SELECT决定1)使用并行计划和2)使用页面锁定显然他们对整个Users表进行端到端扫描的事实.所以问题是,正如我所预测的那样,用户上缺少索引(名称,密码)会导致查询扫描过多的数据.添加索引会将SELECT转换为Nc索引上的直接SEEK和Clustered索引上的查找,这将大大减少与UPDATE重叠的窗口.现在UPDATE几乎可以保证与所有SELECT冲突,因为每个SELECT都保证读取每一行.
添加索引将缓解当前的问题.使用快照隔离将掩盖问题,因为除非添加(名称,密码)索引,否则仍将发生端到端扫描.或者只有(姓名)可能也会起作用.
为了将来的可扩展性,更新每个页面视图上的“视图”列将不起作用.延迟更新,批量聚合计数更新,垂直分区Users表和取出Views列是可行的替代方案.
