Deadlock auf 2 Seiten

Ich behebe einige Deadlocks, die ich in einer Produktionsumgebung sehe und ich bin neu hier, aber etwas schien mir seltsam zu sein. Also habe ich die Deadlock-Grafik unten:

Deadlock-Diagramm

Die rechte Seite des Deadlocks ist ein Update, das folgende ist:

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

Es sieht mir in der Grafik an, dass dieses Update versucht, 2 Seitenschlösser zu erwerben. order_sub_line_id ist ein gruppierter Index.

Sollte dies versuchen, 2 Seitenschlösser zu erwerben und wenn ja warum?

Zusätzliche Information:

Das Deadlock-Opfer ist eine view (verbindet ein paar andere Tabellen einschließlich order_sub_line), die im Wesentlichen die folgende Abfrage auf dieser Tabelle ausführt:

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

Es gibt keine Indizes auf order_sub_line neben dem gruppierten Primärschlüsselindex auf order_sub_line.order_sub_line_id

Ausführungsplan: Bildbeschreibung hier eingeben

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> 

Die folgenden Schlussfolgerungen aus den vorgelegten data,

  1. Beide transactions laufen unter Lesebefehlungsisolationsebene.
  2. Eine Transaktion macht mehrere einzelne Zeilen UPDATEs. Das ergibt sich aus der Tatsache, dass einer der process eine IX-Sperre hält und auf einen anderen wartet. Nach dem Ausführungsplan verwendet die UPDATE-statement eine einzeilige gruppierte Indexsuche. So wird es ein X-Schloss auf der KEY-Ebene und eine IX-Sperre auf der Seite Ebene zu erwerben.
  3. Die SELECT-statement und erlangt Sperren auf PAGE-Ebene Granularität. Auch das SELECT behält die Sperren nach dem Lesen der Seiten. Unter normalen Umständen in READ COMMITTED ISOLATION LEVEL, eine SELECT-statement erwerben und freigeben SHARED Sperren sofort nach dem Lesen.

Mit diesen Erkenntnissen bin ich fast sicher, dass der Deadlock aufgrund eines speziellen Szenarios mit einer Abfrageoptimization namens UNORDERED PREFETCH passiert. Dies ist das einzige Szenario, in dem ich weiß, wo SELECT-statementen unter READ COMMITTED Isolation Ebene beibehalten die SHARED-Sperren bis zum Ende der statement.

Eine Repro und mögliche Auflösungen zu dieser Art von Deadlock finden Sie unter http://sqlindian.com/2012/07/13/deadlock-on-select-due-to-unordered-prefetch/