sql-server – 我有关于死锁的数据,但我无法领略它们为什么会发

我在我的大型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);

这就是我从cookie中获取用户的处所.

凡是会发存亡锁,而且第二个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名用户).以是它们可以很轻易地同时运行,但为什么会导致死锁呢？请资助.

感谢

办理要领

在此之前,很明明你的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.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!