Question

我在大的网络应用中遇到了许多僵局。

在此,我想重开僵局,但我被告知要摆脱僵局——这比试图打破僵局要好得多。

因此,我整天花在Kall Profiler,确定追查钥匙等。这就是我所说的话。

缩略语我有一个非常可观的网页,有以下问询(它不是唯一的问答,而是造成麻烦的问答)。

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

之后,在<><<<>a>a/strong>网页上出现以下查询:

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

这是我从厨师那里获得用户的。

经常出现僵局,第二批Linq-to-SQL询问被选定为受害者,因此没有发生,我的网站的用户看到了一个错误筛选。

这是......提供的资料。页: 1 (这只是第一个僵局,它不是唯一的僵局。) 清单全文是:

<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)。因此,它们可以同时非常容易地运行,但为什么会造成僵局? 请提供帮助。

谢谢。

Answer 1

你们需要抓住僵局。 Attach Profiler andtake the Deadlockgraph Final 班级。 Save the . 页: 1

直到那时为止,很明显的是,你的银行。用户查询至少需要一个姓名索引,如果姓名和密码上没有这种索引:

CREATE INDEX idxUsersNamePassword on Users(Name,Password);

我期望用户已经拥有关于国际发展指数的指数,文章也有关于《发展信息》的索引,其中也包括作者信息数据库。消耗用户。 ID and Articles. 国际发展法组织是各自表格的缩略语,它们可能是各自组合的关键。但它值得双重检查。

而且,正如我在你先前的岗位上曾经回答过你,你决定搬走和离开无人回答,你应考虑在 :

ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON

此外,在明确案文中储存密码是一个重大标志。

www.un.org/Depts/DGACM/index_spanish.htm ∗∗∗∗∗

有三个进程(请求):

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

因此,周期为C->A->B->C。

从两个选任考试委员会决定(1)使用平行计划,2)使用页数明显可见,它们对整个用户表进行终端至终端扫描。因此,正如我所预测的那样,问题在于用户缺乏指数(Name, Password),导致难以扫描太多的数据。加上该指数将使选任考试委员会成为Nc指数的直截了当的SEEK,并研究分组指数,这将大大减少与统一选举制度重叠的窗口。如今,人民民主党与all<>m>/em>的冲突得到了很大的保障。选任考试,因为每个选任考试都保证每读一次。

增加指数将缓解眼前的问题。利用Snapshot Isolation will mask 由于终端到终端扫描仍会发生,除非加上(Name,密码)索引。或者只有(Name)才能工作。

今后可扩展性,将每页更新意见栏。延迟更新、批量合计数更新、用户表垂直分化和采纳意见栏是可行的替代办法。

Answer 2

页: 1 2005年服务器中的诊断器

(Linq tokou, Read 只有通过阅读书写交易陷入僵局的交易。

如果你在2005年5月上台,或随后也许会像当时所讨论的那样,建立天线隔离。否则,请向您提供您所使用版本的详细情况。

Answer 3

在这种情况下(即你重新阅读的数据类型和数据更新的性质) 我将处理用户的问候,看看看看看看看不出的孤立。

或者,更相关的变化。从您的描述来看,我认为不把看法保留在用户记录上。相反,我要记录《意见书》对该条的反对,然后从条款的总和中忽略用户的总的看法。作者意见。

友情链接