哪位高手帮小弟我分析一上死锁日志

谁帮我分析一下死锁日志？
既然是死锁，为何只等待了665毫秒？

<deadlock-list>
  <deadlock>
    <victim-list>
      <victimProcess id="process3e7f288" />
    </victim-list>
    <process-list>
      <process id="process3e7f288" taskpriority="0" logused="524" waitresource="RID: 9:1:5419:39" waittime="665" ownerId="1038620" transactionname="user_transaction" lasttranstarted="2013-01-11T21:09:40.887" XDES="0x82679950" lockMode="U" schedulerid="4" kpid="5700" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-01-11T21:09:40.887" lastbatchcompleted="2013-01-11T21:09:40.887" clientapp=".Net SqlClient Data Provider" hostname="WIN-BD8F5B743NK" hostpid="5328" loginname="pa" isolationlevel="read committed (2)" xactid="1038620" currentdb="9" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
        <executionStack>
          <frame procname="" line="20" stmtstart="756" stmtend="864" sqlhandle="0x0300090027261038ca540d012ca100000100000000000000" />
        </executionStack>
        <inputbuf>
Proc [Database Id = 9 Object Id = 940582439]    </inputbuf>
      </process>
      <process id="process3e5b048" taskpriority="0" logused="664" waitresource="RID: 9:1:7430:66" waittime="670" ownerId="1038633" transactionname="user_transaction" lasttranstarted="2013-01-11T21:09:40.890" XDES="0x80c9ce80" lockMode="S" schedulerid="2" kpid="3228" status="suspended" spid="73" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-01-11T21:09:40.890" lastbatchcompleted="2013-01-11T21:09:40.890" clientapp=".Net SqlClient Data Provider" hostname="WIN-BD8F5B743NK" hostpid="5328" loginname="pa" isolationlevel="read committed (2)" xactid="1038633" currentdb="9" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
        <executionStack>
          <frame procname="" line="12" stmtstart="226" stmtend="754" sqlhandle="0x0300090027261038ca540d012ca100000100000000000000" />
        </executionStack>
        <inputbuf>
Proc [Database Id = 9 Object Id = 940582439]    </inputbuf>
      </process>
    </process-list>
    <resource-list>
      <ridlock fileid="1" pageid="5419" dbid="9" objectname="" id="lock819f0980" mode="X" associatedObjectId="72057594086096896">
        <owner-list>
          <owner id="process3e5b048" mode="X" />
        </owner-list>
        <waiter-list> 
 
          <waiter id="process3e7f288" mode="U" requestType="wait" />
        </waiter-list>
      </ridlock>
      <ridlock fileid="1" pageid="7430" dbid="9" objectname="" id="lock80b92200" mode="X" associatedObjectId="72057594086096896">
        <owner-list>
          <owner id="process3e7f288" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process3e5b048" mode="S" requestType="wait" />
        </waiter-list>
      </ridlock>
    </resource-list>
  </deadlock>
</deadlock-list>

[解决办法]

引用:

引用:spid53 : waitresource="RID: 9:1:5419:39" 可以看出databaseid=9 文件号是1 页号是5419 行号是39  此操作被牺牲
spid73 : waitresource="RID: 9:1:7430:66" 可以看出databaseid=9 文件号是1 页号是7430 行号是……

看一下这个：
  <ridlock fileid="1" pageid="5419" dbid="9" objectname="" id="lock819f0980" mode="X" associatedObjectId="72057594086096896">        <owner-list>          <owner id="process3e5b048" mode="X" />        </owner-list>        <waiter-list>          <waiter id="process3e7f288" mode="U" requestType="wait" />        </waiter-list>      </ridlock>      <ridlock fileid="1" pageid="7430" dbid="9" objectname="" id="lock80b92200" mode="X" associatedObjectId="72057594086096896">        <owner-list>          <owner id="process3e7f288" mode="X" />        </owner-list>        <waiter-list>          <waiter id="process3e5b048" mode="S" requestType="wait" />
[解决办法]
如果你对锁这块一点都不了解的话，还是先查下资料吧。

你的问题表面上看就是缺聚集索引，再者就是可能非聚集索引也不合适。

查看更多 下一篇