我有死锁的数据，但他们为什么会出现我不明白 [英] I have data about deadlocks, but I can't understand why they occur
问题描述
我在我的大网络应用程序接收大量的死锁。
I am receiving a lot of deadlocks in my big web application.
<一个href=\"http://stackoverflow.com/questions/2941233/how-to-automatically-re-run-deadlocked-transaction-asp-net-mvc-sql-server\">http://stackoverflow.com/questions/2941233/how-to-automatically-re-run-deadlocked-transaction-asp-net-mvc-sql-server
在这里，我想重新运行死锁的事务，但有人告诉我，摆脱僵局的 - 这是好多了，比试图赶上死锁
Here I wanted to re-run deadlocked transactions, but I was told to get rid of the deadlocks - it's much better, than trying to catch the deadlocks.
于是，我花了整整一天，SQL事件探查器，设置跟踪键等。而这就是我得到了。
So I spent the whole day with SQL Profiler, setting the tracing keys etc. And this is what I got.
有一个用户表。我有以下查询很高的可用页面（这不是唯一的查询，但它是引起麻烦的）
There's a Users table. I have a very high usable page with the following query (it's not the only query, but it's the one that causes troubles)
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);
这就是我从饼干获得用户。
That's where I get User from cookies.
很多时候发生死锁和第二LINQ到SQL查询被选为受害者，因此它不跑，我的网站的用户看到错误画面。
Very often a deadlock occurs and this second Linq-to-SQL query is chosen as a victim, so it's not run, and users of my site see an error screen.
这是由SQL事件探查器捕获的（这只是第一个僵局，这不是唯一的一个，整个列表是巨大的。）的.XDL图形信息：
This is information from the .XDL graph captured by SQL Profiler (It's just the first deadlock, it's not the only one. The whole list is gigantic.):
<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>
我读了很多关于死锁......我不明白为什么这是造成僵局。
I read a lot about deadlocks... And I don't understand why this is causing a deadlock.
所以，很显然这个查询都非常经常运行。至少一次第二。甚至更多的时候（300-400用户在线）。这样他们就可以在同一时间很容易运行，但为什么它会引起死锁？请帮助。
So obviously both of this queries run very often. At least once a second. Maybe even more often (300-400 users online). So they can be run at the same time very easily, but why does it cause a deadlock? Please help.
感谢您
推荐答案
您需要捕获死锁图形。连接Profiler并捕获死锁图形事件类。保存.XDL图形和信息添加到您的文章。
You need to capture the deadlock graph. Attach Profiler and capture the Deadlock Graph Event class. Save the .XDL graph and add that info to your post.
在此之前，为pretty明显，你DB.Users.SingleOrDefault查询需要在名称索引至少，如果不是名和密码：
Until then, is pretty obvious that your DB.Users.SingleOrDefault query requires an index on Name at least, if not on Name and Password:
CREATE INDEX idxUsersNamePassword on Users(Name,Password);
我期望用户已经拥有ID的索引，和文章对条款ArticleID覆盖的AuthorID太索引。假设Users.ID和Articles.ArticleID在他们的PK正在各自的表，他们很可能是各自的集群密钥，因此它真实的。值得双重检查，虽然。
I expect Users already has an index on ID, and Articles has an index on ArticleID which covers AuthorID too. Assuming the Users.ID and Articles.ArticleID are PKs in they're respective tables, they are probably the respective's clustered key so it true. It worth double checking, though.
和，因为我已经在你的previous后回答你，一旦你决定继续前进，并留下未回答，你应该考虑在打开的快照隔离：
And, as I already answered you once in your previous post you decided to move on and leave un-answered, you should consider turning on Snapshot Isolation:
ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON
此外，在明文存储密码是一个重大的#fail。
Besides that, storing password in clear text is a major #fail.
后僵局信息更新
有三个进程（请求）：
- A）... F048是运行
SELECT ... FROM用户WHERE密码= ...和Name = ... - B）... 0988是运行
SELECT ... FROM用户WHERE密码= ...和Name = ... - C）...这是运行在
更新FB88 ...
- A) ...F048 which is running the
SELECT ... FROM Users WHERE Password = ... and Name = ... - B) ...0988 which is running the
SELECT ... FROM Users WHERE Password = ... and Name = ... - C) ...FB88 which is running the
UPDATE ...
僵局周期是：
- C对第IX锁等待，由A公司的S锁阻塞
- B关于页S锁等待，用C的IX锁阻塞
- 在平行交换资源为等待，被B阻断
因此，周期为C-> A-> B->℃。
The cycle therefore is C->A->B->C.
这一个事实，即所涉及的两个的SELECT决定1）使用并行计划和2）用页锁明显，他们做的端至端扫描整个用户表中。所以问题是，我predicted，对用户缺乏对（名，密码）指数而导致查询扫描太多的数据。添加该指数将打开选入在NC指数，并在聚集索引查找直SEEK，这将大大减少重叠的窗口进行更新。眼下更新是pretty多少保证抵触的所有的进行选择，因为每一个SELECT保证阅读每一行。
From the fact that the two SELECTs involved decide to 1) use a parallel plan and 2) use page locks is obvious that they do an end-to-end scan of the entire Users table. so the problem is, as I predicted, a lack of index on (Name, Password) on Users which causes the query to scan way too much data. Adding the index would turn the SELECT into a straight SEEK on the Nc index and a lookup on the Clustered index, and this would dramatically reduce the window of overlap with the UPDATE. Right now the UPDATE is pretty much guaranteed to conflict with all SELECTs, since every SELECT is guaranteed to read every row.
添加索引将aleviate眼前的问题。使用快照隔离会的屏蔽的问题，因为终端到终端的扫描还是会发生，除非添加了（名，密码）指数。或者只（名称），可能会工作过。
Adding the index will aleviate the immediate problem. Using Snapshot Isolation will mask the problem, since the end-to-end scans are still going to occur unless the (Name, Password) index is added. Or only (Name) will likely work too.
有关未来可扩展性，更新视图的列的每个页面视图的将无法工作。延迟更新，批量汇总计数更新，垂直分区表的用户，并采取了视图的列都可行的替代方案。
For future scalability, updating the Views column on every page view will not work. Delayed update, batch aggregate count update, vertically partition the Users table and take out the Views column are viable alternatives.
这篇关于我有死锁的数据，但他们为什么会出现我不明白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
