我在我的大型Web应用程序中收到了很多死锁。
如何自动重新运行死锁事务?(ASP.NET MVC/SQL Server)
我想重新运行死锁事务,但被告知要摆脱死锁——这比尝试捕捉死锁要好得多。
所以我花了一整天的时间使用SQL Profiler、设置跟踪键等。这就是我得到的结果。
有一个
所以我花了一整天的时间使用SQL Profiler、设置跟踪键等。这就是我得到的结果。
有一个
Users
表。我有一个非常高可用页面,其中包含以下查询(不是唯一的查询,但它是引起麻烦的查询之一)。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);
我是从Cookie中获取用户的。
很多时候会发生死锁,并选择第二个Linq-to-SQL查询作为受害者,因此它不会运行,我的网站用户会看到错误屏幕。
这是通过SQL Profiler捕获的.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)。因此,它们可以很容易地同时运行,但为什么会导致死锁呢?请帮忙解答。
谢谢