首页 新闻 赞助 找找看

sql server 如何模拟 S锁和U锁引起的死锁呢?

0
悬赏园豆:5 [已关闭问题] 关闭于 2019-05-28 13:50
复制代码
<deadlock>
  <victim-list>
    <victimProcess id="processcfd4d708" />
  </victim-list>
  <process-list>
    <process id="processcfd4d708" taskpriority="0" logused="0" waitresource="KEY: 16:72057624703729664 (956a023ca49d)" waittime="3200" ownerId="453860481" transactionname="SELECT INTO" lasttranstarted="2018-03-13T22:08:17.280" XDES="0x8a9fd970" lockMode="S" schedulerid="4" kpid="5416" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-03-13T22:08:17.277" lastbatchcompleted="2018-03-13T22:08:17.277" clientapp=".Net SqlClient Data Provider" hostname="IN-TGSAPP1" hostpid="15084" loginname="" isolationlevel="read committed (2)" xactid="453860481" currentdb="16" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="" line="1" stmtend="356" sqlhandle="0x02000000e3e4731b8aec87ea3918bfa5ecb0825dd2597fd1" />
        <frame procname="" line="35" stmtstart="2178" stmtend="2216" sqlhandle="0x03001000c0fc4659332c58009da800000100000000000000" />
      </executionStack>
      <inputbuf>
Proc [Database Id = 16 Object Id = 1497824448]   </inputbuf>
    </process>
    <process id="process5769288" taskpriority="0" logused="64884" waitresource="KEY: 16:72057624723062784 (c784f77b6c90)" waittime="1695" ownerId="453860409" transactionname="user_transaction" lasttranstarted="2018-03-13T22:08:16.927" XDES="0x942dd970" lockMode="X" schedulerid="2" kpid="4100" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-03-13T22:08:18.803" lastbatchcompleted="2018-03-13T22:08:18.783" clientapp=".Net SqlClient Data Provider" hostname="IN-TGSAPP1" hostpid="15084" loginname="" isolationlevel="read committed (2)" xactid="453860409" currentdb="16" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="" line="43" stmtstart="2914" stmtend="4758" sqlhandle="0x03001000904e9225010f11015fa600000100000000000000" />
      </executionStack>
      <inputbuf>
Proc [Database Id = 16 Object Id = 630345360]   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057624703729664" dbid="16" objectname="" indexname="" id="lockbec53180" mode="X" associatedObjectId="72057624703729664">
      <owner-list>
        <owner id="process5769288" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="processcfd4d708" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057624723062784" dbid="16" objectname="" indexname="" id="lock12c467c80" mode="U" associatedObjectId="72057624723062784">
      <owner-list>
        <owner id="processcfd4d708" mode="S" />
      </owner-list>
      <waiter-list>
        <waiter id="process5769288" mode="X" requestType="convert" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>
复制代码

这是查询到的死锁日志,看日志应该是索引引起的,但是出现的频率又不是很高,

有没有办法通过一些测试的sql,模拟出这种死锁日志?  看这个日志应该是S锁和U锁引起的冲突产生了死锁,不知道理解的对不对。

枫轻的主页 枫轻 | 初学一级 | 园豆:51
提问于:2018-03-16 16:05
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册