死锁2页 [英] Deadlock on 2 pages

查看：113
本文介绍了死锁2页的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在解决生产环境中遇到的一些僵局，这是我的新手，但我觉得有些奇怪。所以我有下面的死锁图：





死锁的右侧是以下更新：

  UPDATE order_sub_line SET sub_line_status = 300 WHERE order_sub_line_id ='75C387EC-A1A7-4587-9FA0-DD33A49009BC'

在我看来，该更新尝试获取2个页面锁。 order_sub_line_id是一个聚集索引。



这是否应该尝试获取2个页面锁？如果是，为什么？



其他信息：



死锁受害者是一个视图（连接了包括order_sub_line在内的其他一些表），该视图实际上在该表上运行以下查询：

 从order_sub_line osl中选择top（50）*，其中osl.sub_line_type = 1且osl.sub_line_status< 375

除了order_sub_line.order_sub_line_id上​​的集群主键索引之外，order_sub_line上没有索引。

执行计划：：



死锁xml： 

 <死锁列表> 
<死锁受害者= process4224eccf8> 
< process-list> 
< process id = process4224eccf8 taskpriority = 0 logused = 0 waitresource = PAGE：7：1：13448 waittime = 1628 ownerId = 1683307923 transactionname = SELECT lasttranstarted =  2013-07-31T08：45：53.157 XDES = 0x48afafc40 lockMode = S schedulerid = 2 kpid = 1208 status = suspended spid = 151 sbid = 0 ecid = 15  priority = 0 trancount = 0 lastbatchstarted = 2013-07-31T08：45：53.157 lastbatchcompleted = 2013-07-31T08：45：53.157 lastattention = 1900-01-01T00：00：00.157 clientapp = ExactaAOR hostname = BASTIAN-PC hostpid = 7336隔离级别=读取已提交（2） xactid = 1683307923 currentdb = 7 lockTimeout = 4294967295 clientoption1 = 671088672 clientoption2 = 128056 > 
< executionStack> 
< frame procname = adhoc line = 1 stmtstart = 72 sqlhandle = 0x0200000055b04f0c4d136173c4d51458bdb5002bfe5801370000000000000000000000000000000000000000000000> 
选择顶部（@ p0）this_.TRANSPORT_CNTNR_ID作为TRANSPORT1_9_0_，this_.CNTNR_NAME作为CNTNR2_9_0_，this_.CNTNR_TYPE作为CNTNR3_9_0_，this_.CNTNR_HEIGHT作为CNTNR4_9_0_，this_.CNTNR_WEIDP作为CNTNR_WIDTH。 CNTNR7_9_0_，this_.PARENT_CNTNR_ID为PARENT8_9_0_，this_.RESERVATION_LOC_ID为RESERVAT9_9_0_，this_.WORK_AREA_ID为WORK10_9_0_，this_.WORK_AREA_NAME为WORK11_9_0_，this_.GROUP_ID为GROUP12_9_0_，this_.RELEASE_STATUS为RELEASE13_9_0_，this_.RELEASE_TIME为RELEASE14_9_0_，this_.PRINT_STATUS为PRINT15_9_0_， this_.SUB_LINE_COUNT为SUB16_9_0_，this_.ORDER_ID为ORDER17_9_0_，this_.QTY_REQUESTED为QTY18_9_0_，this_.ORDER_NAME为ORDER19_9_0_，this_.ORDER_PRIORITY为ORDER20_9_0_，this_.ORDER_PRI_ITY为ORDER20_9_0_，this_.ORDER_ON_D，_9_9，UE_DER_STATUS，OR_17 ORDER_SUB_LINE_STATUS as ORDER24_9_0_ FROM V_CARTON_RELEASE this_ WHERE（this_.RELEASE_STATUS = @ p1和this_.ORDER_SUB_LINE_STATUS& lt; @ p2）ORDER到this_.RELEASE_TIME asc< / frame> 
< frame procname = unknown line = 1 sqlhandle = 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000> 
未知< / frame> 
< / executionStack> 
< inputbuf> 
（@ p0 int，@ p1 nvarchar（4000），@ p2 int）选择顶部（@ p0）this_.TRANSPORT_CNTNR_ID作为TRANSPORT1_9_0_，this_.CNTNR_NAME作为CNTNR2_9_0_，this_.CNTNR_TYPE作为CNTNR3_9_0_，this_.CNTNR_HE this_.CNTNR_WIDTH如CNTNR5_9_0_，this_.CNTNR_DEPTH如CNTNR6_9_0_，this_.CNTNR_WEIGHT如CNTNR7_9_0_，this_.PARENT_CNTNR_ID如PARENT8_9_0_，this_.RESERVATION_LOC_ID如RESERVAT9_9_0_，this_.WORK_AREA_ID如WORK10_9_0_，this_.WORK_AREA_NAME如WORK11_9_0_，this_.GROUP_ID如GROUP12_9_0_，THIS_。 RELEASE_STATUS作为RELEASE13_9_0_，this_.RELEASE_TIME作为RELEASE14_9_0_，this_.PRINT_STATUS作为PRINT15_9_0_，this_.SUB_LINE_COUNT作为SUB16_9_0_，this_.ORDER_ID作为ORDER17_9_0_，this_。 ORDER21_9_0_，this_.ON_HOLD为ON22_9_0_，this_.DUE_DATE为DUE23_9_0_，this_.ORDER_SUB_LINE_STATUS作为ORDER24_9_0_ FROM V_CARTON_RELEASE this_ WHERE（this_.RELEASE_STATUS = @ p1和this_.ORDER_ SUB_LINE_STATUS& lt; @ p2）ORDER< / inputbuf> 
< / process> 
< process id = process4bf7bdc38 taskpriority = 0 logused = 8608 waitresource = PAGE：7：1：13447 waittime = 1616 ownerId = 1683308190 transactionname = user_transaction lasttranstarted =  2013-07-31T08：45：53.450 XDES = 0x4ebd456a8 lockMode = IX schedulerid = 1 kpid = 6032 status = suspended spid = 85 sbid = 0 ecid = 0  priority = 0 trancount = 2 lastbatchstarted = 2013-07-31T08：45：53.450 lastbatchcompleted = 2013-07-31T08：45：53.450 lastattention = 1900-01-01T00：00：00.450 clientapp = ExactaAOR hostname = BASTIAN-PC hostpid = 7336 loginname = asapdb isolationlevel = read commit（2） xactid = 1683308190 currentdb = 7 lockTimeout = 4294967295 clientoption1 = 671088672  clientoption2 = 128056> 
< executionStack> 
< frame procname = adhoc line = 1 stmtstart = 60 sqlhandle = 0x02000000109639184c42e35fa55701e017640d83bd4818c300000000000000000000000000000000000000000000 
更新ORDER_SUB_LINE SET SUB_LINE_STATUS = @ p0 WHERE ORDER_SUB_LINE_ID = @ p1< / frame> 
< frame procname = unknown line = 1 sqlhandle = 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000> 
未知< / frame> 
< / executionStack> 
< inputbuf> 
（@ p0 int，@ p1 uniqueidentifier）更新ORDER_SUB_LINE SET SUB_LINE_STATUS = @ p0 WHERE ORDER_SUB_LINE_ID = @ p1< / inputbuf> 
< / process> 
< / process-list> 
< resource-list> 
< pagelock fileid = 1 pageid = 13448 dbid = 7 subresource = FULL objectname = Exactadb.dbo.order_sub_line id = lock4cf017000 mode = IX relatedObjectId = 72057594460962816 > 
< owner-list> 
< owner id = process4bf7bdc38 mode = IX /> 
< / owner-list> 
< waiter-list> 
< waiter id = process4224eccf8 mode = S requestType = wait /> 
< / waiter-list> 
< / pagelock> 
< pagelock fileid = 1 pageid = 13447 dbid = 7 subresource = FULL objectname = Exactadb.dbo.order_sub_line id = lock4a4554500 mode = S relatedObjectId = 72057594460962816 > 
< owner-list> 
<所有者id = process4224eccf8 mode = S /> 
< / owner-list> 
< waiter-list> 
< waiter id = process4bf7bdc38 mode = IX requestType = wait /> 
< / waiter-list> 
< / pagelock> 
< / resource-list> 
< / deadlock> 
< / deadlock-list>


解决方案

从提供的数据中得出以下推论，


  1. 两个事务都在读取的提交隔离级别下运行。

  2. 一个事务正在执行多个单独的行更新。从以下事实中可以明显看出这一点：一个进程持有IX锁，然后等待另一个。根据执行计划，UPDATE语句使用单行聚集索引查找。因此它将在KEY级别获取X锁，在页面级别获取IX锁。

  3. SELECT语句并以PAGE级别粒度获取锁。 SELECT在读取页面后仍保留锁。在正常情况下，在READ COMMITTED ISOLATION LEVEL中，SELECT语句将在读取后立即获取并释放SHARED锁。

有了这些发现，我几乎可以确定死锁是由于涉及查询优化（称为UNORDERED PREFETCH）的特殊情况而发生的。这是我唯一知道的情况，在READ COMMITTED Isolation级别下运行的SELECT语句将SHARED锁保留到语句结束。



一个复制和可能的解决方案可以在
> https://web.archive.org/web/20120806214319/http://sqlindian.com/2012/07/13/deadlock-on-选择由于无序预取/


I'm troubleshooting some deadlocks I'm seeing in a production environment and I'm new to this but something seemed odd to me. So I have the deadlock graph below:

The right side of the deadlock is an update that is the following: 

UPDATE order_sub_line SET sub_line_status = 300 WHERE order_sub_line_id = '75C387EC-A1A7-4587-9FA0-DD33A49009BC'

It looks to me in the graph that this update is trying to acquire 2 page locks. order_sub_line_id is a clustered index.

Should this be trying to acquire 2 page locks and if so why?

Additional Information:

The deadlock victim is a view (joins a few other tables including order_sub_line) that is essentially running the following query on this table:

select top(50) * from order_sub_line osl where osl.sub_line_type = 1 and osl.sub_line_status < 375

There are no indexes on order_sub_line besides the clustered primary key index on order_sub_line.order_sub_line_id

Execution plan:

Deadlock xml: 

<deadlock-list>
 <deadlock victim="process4224eccf8">
  <process-list>
   <process id="process4224eccf8" taskpriority="0" logused="0" waitresource="PAGE: 7:1:13448 " waittime="1628" ownerId="1683307923" transactionname="SELECT" lasttranstarted="2013-07-31T08:45:53.157" XDES="0x48afafc40" lockMode="S" schedulerid="2" kpid="1208" status="suspended" spid="151" sbid="0" ecid="15" priority="0" trancount="0" lastbatchstarted="2013-07-31T08:45:53.157" lastbatchcompleted="2013-07-31T08:45:53.157" lastattention="1900-01-01T00:00:00.157" clientapp="ExactaAOR" hostname="BASTIAN-PC" hostpid="7336" isolationlevel="read committed (2)" xactid="1683307923" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="72" sqlhandle="0x0200000055b04f0c4d136173c4d51458bdb5002bfe5801370000000000000000000000000000000000000000">
SELECT TOP (@p0)  this_.TRANSPORT_CNTNR_ID as TRANSPORT1_9_0_, this_.CNTNR_NAME as CNTNR2_9_0_, this_.CNTNR_TYPE as CNTNR3_9_0_, this_.CNTNR_HEIGHT as CNTNR4_9_0_, this_.CNTNR_WIDTH as CNTNR5_9_0_, this_.CNTNR_DEPTH as CNTNR6_9_0_, this_.CNTNR_WEIGHT as CNTNR7_9_0_, this_.PARENT_CNTNR_ID as PARENT8_9_0_, this_.RESERVATION_LOC_ID as RESERVAT9_9_0_, this_.WORK_AREA_ID as WORK10_9_0_, this_.WORK_AREA_NAME as WORK11_9_0_, this_.GROUP_ID as GROUP12_9_0_, this_.RELEASE_STATUS as RELEASE13_9_0_, this_.RELEASE_TIME as RELEASE14_9_0_, this_.PRINT_STATUS as PRINT15_9_0_, this_.SUB_LINE_COUNT as SUB16_9_0_, this_.ORDER_ID as ORDER17_9_0_, this_.QTY_REQUESTED as QTY18_9_0_, this_.ORDER_NAME as ORDER19_9_0_, this_.ORDER_PRIORITY as ORDER20_9_0_, this_.ORDER_STATUS as ORDER21_9_0_, this_.ON_HOLD as ON22_9_0_, this_.DUE_DATE as DUE23_9_0_, this_.ORDER_SUB_LINE_STATUS as ORDER24_9_0_ FROM V_CARTON_RELEASE this_ WHERE (this_.RELEASE_STATUS = @p1 and this_.ORDER_SUB_LINE_STATUS &lt; @p2) ORDER BY this_.RELEASE_TIME asc     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@p0 int,@p1 nvarchar(4000),@p2 int)SELECT TOP (@p0)  this_.TRANSPORT_CNTNR_ID as TRANSPORT1_9_0_, this_.CNTNR_NAME as CNTNR2_9_0_, this_.CNTNR_TYPE as CNTNR3_9_0_, this_.CNTNR_HEIGHT as CNTNR4_9_0_, this_.CNTNR_WIDTH as CNTNR5_9_0_, this_.CNTNR_DEPTH as CNTNR6_9_0_, this_.CNTNR_WEIGHT as CNTNR7_9_0_, this_.PARENT_CNTNR_ID as PARENT8_9_0_, this_.RESERVATION_LOC_ID as RESERVAT9_9_0_, this_.WORK_AREA_ID as WORK10_9_0_, this_.WORK_AREA_NAME as WORK11_9_0_, this_.GROUP_ID as GROUP12_9_0_, this_.RELEASE_STATUS as RELEASE13_9_0_, this_.RELEASE_TIME as RELEASE14_9_0_, this_.PRINT_STATUS as PRINT15_9_0_, this_.SUB_LINE_COUNT as SUB16_9_0_, this_.ORDER_ID as ORDER17_9_0_, this_.QTY_REQUESTED as QTY18_9_0_, this_.ORDER_NAME as ORDER19_9_0_, this_.ORDER_PRIORITY as ORDER20_9_0_, this_.ORDER_STATUS as ORDER21_9_0_, this_.ON_HOLD as ON22_9_0_, this_.DUE_DATE as DUE23_9_0_, this_.ORDER_SUB_LINE_STATUS as ORDER24_9_0_ FROM V_CARTON_RELEASE this_ WHERE (this_.RELEASE_STATUS = @p1 and this_.ORDER_SUB_LINE_STATUS &lt; @p2) ORDER    </inputbuf>
   </process>
   <process id="process4bf7bdc38" taskpriority="0" logused="8608" waitresource="PAGE: 7:1:13447 " waittime="1616" ownerId="1683308190" transactionname="user_transaction" lasttranstarted="2013-07-31T08:45:53.450" XDES="0x4ebd456a8" lockMode="IX" schedulerid="1" kpid="6032" status="suspended" spid="85" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-07-31T08:45:53.450" lastbatchcompleted="2013-07-31T08:45:53.450" lastattention="1900-01-01T00:00:00.450" clientapp="ExactaAOR" hostname="BASTIAN-PC" hostpid="7336" loginname="asapdb" isolationlevel="read committed (2)" xactid="1683308190" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="60" sqlhandle="0x02000000109639184c42e35fa55701e017640d83bd4818c30000000000000000000000000000000000000000">
UPDATE ORDER_SUB_LINE SET SUB_LINE_STATUS = @p0 WHERE ORDER_SUB_LINE_ID = @p1     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@p0 int,@p1 uniqueidentifier)UPDATE ORDER_SUB_LINE SET SUB_LINE_STATUS = @p0 WHERE ORDER_SUB_LINE_ID = @p1    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <pagelock fileid="1" pageid="13448" dbid="7" subresource="FULL" objectname="Exactadb.dbo.order_sub_line" id="lock4cf017000" mode="IX" associatedObjectId="72057594460962816">
    <owner-list>
     <owner id="process4bf7bdc38" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process4224eccf8" mode="S" requestType="wait"/>
    </waiter-list>
   </pagelock>
   <pagelock fileid="1" pageid="13447" dbid="7" subresource="FULL" objectname="Exactadb.dbo.order_sub_line" id="lock4a4554500" mode="S" associatedObjectId="72057594460962816">
    <owner-list>
     <owner id="process4224eccf8" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process4bf7bdc38" mode="IX" requestType="wait"/>
    </waiter-list>
   </pagelock>
  </resource-list>
 </deadlock>
</deadlock-list>

解决方案

The following inferences made from the data provided,

  1. Both the transactions are running under read committed isolation level.
  2. One transaction is doing multiple individual row UPDATEs. This is evident from the fact that one of the process is holding an IX lock and waiting for another. As per the execution plan, the UPDATE statement is using a single row clustered index seek. So it will be acquiring an X lock at the KEY level and an IX lock at the page level.
  3. The SELECT statement and is acquiring locks at PAGE level granularity. Also the SELECT is retaining the locks after reading the pages. Under normal circumstances in READ COMMITTED ISOLATION LEVEL, a SELECT statement will acquire and release SHARED locks immediately after reading.

With these findings, I am almost certain that the deadlock is happening due to a special scenario involving a query optimization called UNORDERED PREFETCH. This is the only scenario I am aware of where SELECT statements running under READ COMMITTED Isolation level retains the SHARED locks until the end of the statement.

A repro and possible resolutions to this this type of deadlock can be found at https://web.archive.org/web/20120806214319/http://sqlindian.com/2012/07/13/deadlock-on-select-due-to-unordered-prefetch/

这篇关于死锁2页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆