我有有关死锁的数据，但我无法理解它们为什么会发生

发布于 2024-09-03 10:55:23 字数 8756 浏览 7 评论 0原文

我的大型网络应用程序中遇到了很多死锁。

在这里，我想重新运行死锁事务，但我被告知要摆脱死锁 - 这比尝试捕获死锁要好得多。

所以我花了一整天的时间使用 SQL Profiler，设置跟踪键等。这就是我得到的结果。

有一个Users 表。我有一个非常高的可用页面，其中包含以下查询（这不是唯一的查询，但它是导致麻烦的查询）

UPDATE Users
SET views = views + 1
WHERE ID IN (SELECT AuthorID FROM Articles WHERE ArticleID = @ArticleID)

然后在 ALL 页面中有以下查询：

User = DB.Users.SingleOrDefault(u => u.Password == password && u.Name == username);

这就是我从 cookie 获取 User 的地方。

通常会发生死锁，并且第二个 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 个用户在线）。那么它们可以很容易地同时运行，但是为什么会导致死锁呢？请帮忙。

谢谢

原文

I am receiving a lot of deadlocks in my big web application.

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.

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)

And then there's the following query in ALL pages:

User = DB.Users.SingleOrDefault(u => u.Password == password && u.Name == username);

That's where I get User from cookies.

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.

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.

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.

Thank you

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

人疚 2024-09-10 10:55:23

您需要捕获死锁图。附加探查器并捕获死锁图形事件类。保存 .XDL 图表并将该信息添加到您的帖子中。

在那之前，很明显，您的 DB.Users.SingleOrDefault 查询至少需要名称上的索引，如果不是名称和密码的话：

CREATE INDEX idxUsersNamePassword on Users(Name,Password);

我希望用户已经在 ID 上有一个索引，并且文章在 ArticleID 上有一个索引，它也涵盖了 AuthorID 。假设 Users.ID 和 Articles.ArticleID 是它们各自表中的 PK，它们可能是各自的聚集键，所以确实如此。不过，值得仔细检查。

而且，由于我已经在上一篇文章中回答过您一次，您决定继续前进并保持未答复，因此您应该考虑打开快照隔离：

ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON

除此之外，以明文形式存储密码是一个主要的#fail。

死锁信息后更新

共有三个进程（请求）：

A) ...F048 正在运行 SELECT ... FROM Users WHERE Password = ... and Name = ...
B) ...0988 正在运行 SELECT ... FROM Users WHERE Password = ... and Name = ...
C) ...FB88 正在运行 < code>UPDATE ...

死锁循环为：

C 等待 Page 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列都是可行的替代方案。

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.

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);

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.

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

Besides that, storing password in clear text is a major #fail.

Update after deadlock info

There are three processes (requests):

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 ...

The deadlock cycle is:

C waits on Page IX lock, is blocked by A's S lock
B waits on Page S lock, is blocked by C's IX lock
A waits on parallel exchange resources, is blocked by B

The cycle therefore is C->A->B->C.

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.

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.

回复收藏 0 原文