Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2010-09-14 : 03:46:37
|
I found a query that returns XML information about deadlocks. The XML isn't valid but I can see the queries involved in the deadlock...UPDATE Subscription SET [Status] = 1, Completed = null WHERE StudentsID = 369880 AND CourseID = 17933 AND NOT [Status] = 1 OR NOT Completed = nullUPDATE Subscription SET [Status] = 1, Completed = null WHERE StudentsID = 280470 AND CourseID = 14472 AND NOT [Status] = 1 OR NOT Completed = nullBoth of these queries execute very quickly when run in SSMS so I don't understand what the problem is. Does the XML provide any other clues? I am getting quite a few of these deadlocks.<deadlock-list> <victim-list> <victimProcess id="process2f84a9dc8"/> <process-list> <process id="process2f84a9dc8" taskpriority="0" logused="0" waitresource="PAGE: 5:1:945123" waittime="6734" ownerId="5658610088" transactionname="UPDATE" lasttranstarted="2010-09-13T15:52:13.610" XDES="0x58f1b0e70" lockMode="U" schedulerid="4" kpid="3980" status="suspended" spid="130" sbid="0" ecid="9" priority="0" trancount="0" lastbatchstarted="2010-09-13T15:52:13.610" lastbatchcompleted="2010-09-13T15:52:13.610" clientapp="Internet Information Services" hostname="211633-WEB1" hostpid="10672" isolationlevel="read committed (2)" xactid="5658610088" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="" line="1" sqlhandle="0x020000009635f02e1c23a9d9346ff60fdfba1571141a68e8"> </frame> </executionStack> <inputbuf> </inputbuf> </process> <process id="process65d220bc8" taskpriority="0" logused="0" waitresource="PAGE: 5:1:943344" waittime="11619" ownerId="5658594445" transactionname="UPDATE" lasttranstarted="2010-09-13T15:52:04.887" XDES="0x6950022f0" lockMode="U" schedulerid="1" kpid="6956" status="suspended" spid="186" sbid="0" ecid="16" priority="0" trancount="0" lastbatchstarted="2010-09-13T15:52:04.887" lastbatchcompleted="2010-09-13T15:52:04.887" clientapp="Internet Information Services" hostname="211633-WEB1" hostpid="10672" isolationlevel="read committed (2)" xactid="5658594445" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="" line="1" sqlhandle="0x020000000c001702b3161a72a631c9a1be9e847a2583d619"> </frame> </executionStack> <inputbuf> </inputbuf> </process> <process id="process541880988" taskpriority="0" logused="0" waitresource="PAGE: 5:1:945123" waittime="1802" ownerId="5658601185" transactionname="UPDATE" lasttranstarted="2010-09-13T15:52:08.260" XDES="0x6544bf370" lockMode="U" schedulerid="5" kpid="5940" status="suspended" spid="103" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2010-09-13T15:52:08.260" lastbatchcompleted="2010-09-13T15:52:08.260" lastattention="2010-09-13T15:48:06.697" clientapp="Internet Information Services" hostname="211633-WEB1" hostpid="10672" loginname="Blatant" isolationlevel="read committed (2)" xactid="5658601185" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="" line="1" sqlhandle="0x0200000089423d0fd92cbbf155001d39b149e9786f91622c"> </frame> </executionStack> <inputbuf> UPDATE Subscription SET [Status] = 1, Completed = null WHERE StudentsID = 369880 AND CourseID = 17933 AND NOT [Status] = 1 OR NOT Completed = null </inputbuf> </process> <process id="process6a9baf288" taskpriority="0" logused="10000" waittime="5074" schedulerid="4" kpid="7124" status="suspended" spid="186" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2010-09-13T15:52:04.887" lastbatchcompleted="2010-09-13T15:52:04.887" clientapp="Internet Information Services" hostname="211633-WEB1" hostpid="10672" isolationlevel="read committed (2)" xactid="5658594445" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="" line="1" sqlhandle="0x020000000c001702b3161a72a631c9a1be9e847a2583d619"> </frame> </executionStack> <inputbuf> </inputbuf> </process> <process id="process2edc00088" taskpriority="0" logused="10000" waittime="5074" schedulerid="3" kpid="10520" status="suspended" spid="186" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2010-09-13T15:52:04.887" lastbatchcompleted="2010-09-13T15:52:04.887" clientapp="Internet Information Services" hostname="211633-WEB1" hostpid="10672" isolationlevel="read committed (2)" xactid="5658594445" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="" line="1" sqlhandle="0x020000000c001702b3161a72a631c9a1be9e847a2583d619"> </frame> </executionStack> <inputbuf> </inputbuf> </process> <process id="process541880748" taskpriority="0" logused="10000" waittime="5074" schedulerid="5" kpid="8312" status="suspended" spid="186" sbid="0" ecid="8" priority="0" trancount="0" lastbatchstarted="2010-09-13T15:52:04.887" lastbatchcompleted="2010-09-13T15:52:04.887" clientapp="Internet Information Services" hostname="211633-WEB1" hostpid="10672" isolationlevel="read committed (2)" xactid="5658594445" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="" line="1" sqlhandle="0x020000000c001702b3161a72a631c9a1be9e847a2583d619"> </frame> </executionStack> <inputbuf> </inputbuf> </process> <process id="process563d50088" taskpriority="0" logused="10000" waittime="5074" schedulerid="6" kpid="10672" status="suspended" spid="186" sbid="0" ecid="6" priority="0" trancount="0" lastbatchstarted="2010-09-13T15:52:04.887" lastbatchcompleted="2010-09-13T15:52:04.887" clientapp="Internet Information Services" hostname="211633-WEB1" hostpid="10672" isolationlevel="read committed (2)" xactid="5658594445" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="" line="1" sqlhandle="0x020000000c001702b3161a72a631c9a1be9e847a2583d619"> </frame> </executionStack> <inputbuf> </inputbuf> </process> <process id="process49a7f1dc8" taskpriority="0" logused="10000" waittime="5074" schedulerid="1" kpid="7436" status="suspended" spid="186" sbid="0" ecid="7" priority="0" trancount="0" lastbatchstarted="2010-09-13T15:52:04.887" lastbatchcompleted="2010-09-13T15:52:04.887" clientapp="Internet Information Services" hostname="211633-WEB1" hostpid="10672" isolationlevel="read committed (2)" xactid="5658594445" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="" line="1" sqlhandle="0x020000000c001702b3161a72a631c9a1be9e847a2583d619"> </frame> </executionStack> <inputbuf> </inputbuf> </process> <process id="process6cee41288" taskpriority="0" logused="10000" waittime="5074" schedulerid="2" kpid="7620" status="suspended" spid="186" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2010-09-13T15:52:04.887" lastbatchcompleted="2010-09-13T15:52:04.887" clientapp="Internet Information Services" hostname="211633-WEB1" hostpid="10672" isolationlevel="read committed (2)" xactid="5658594445" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="" line="1" sqlhandle="0x020000000c001702b3161a72a631c9a1be9e847a2583d619"> </frame> </executionStack> <inputbuf> </inputbuf> </process> <process id="process65cb58748" taskpriority="0" logused="10000" waittime="5074" schedulerid="7" kpid="4048" status="suspended" spid="186" sbid="0" ecid="4" priority="0" trancount="0" lastbatchstarted="2010-09-13T15:52:04.887" lastbatchcompleted="2010-09-13T15:52:04.887" clientapp="Internet Information Services" hostname="211633-WEB1" hostpid="10672" isolationlevel="read committed (2)" xactid="5658594445" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="" line="1" sqlhandle="0x020000000c001702b3161a72a631c9a1be9e847a2583d619"> </frame> </executionStack> <inputbuf> </inputbuf> </process> <process id="process599025708" taskpriority="0" logused="10000" waittime="5074" schedulerid="8" kpid="9492" status="suspended" spid="186" sbid="0" ecid="5" priority="0" trancount="0" lastbatchstarted="2010-09-13T15:52:04.887" lastbatchcompleted="2010-09-13T15:52:04.887" clientapp="Internet Information Services" hostname="211633-WEB1" hostpid="10672" isolationlevel="read committed (2)" xactid="5658594445" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="" line="1" sqlhandle="0x020000000c001702b3161a72a631c9a1be9e847a2583d619"> </frame> </executionStack> <inputbuf> </inputbuf> </process> <process id="process58975a748" taskpriority="0" logused="10000" waittime="11615" schedulerid="1" kpid="10872" status="suspended" spid="186" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2010-09-13T15:52:04.887" lastbatchcompleted="2010-09-13T15:52:04.887" clientapp="Internet Information Services" hostname="211633-WEB1" hostpid="10672" loginname="Blatant" isolationlevel="read committed (2)" xactid="5658594445" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="" line="1" sqlhandle="0x020000000c001702b3161a72a631c9a1be9e847a2583d619"> </frame> </executionStack> <inputbuf> UPDATE Subscription SET [Status] = 1, Completed = null WHERE StudentsID = 280470 AND CourseID = 14472 AND NOT [Status] = 1 OR NOT Completed = null </inputbuf> </process> </process-list> <resource-list> <pagelock fileid="1" pageid="945123" dbid="5" objectname="" id="lock10e19fb00" mode="U" associatedObjectId="72057594356957184"> <owner-list> <owner id="process58975a748" mode="U"/> </owner-list> <waiter-list> <waiter id="process2f84a9dc8" mode="U" requestType="wait"/> </waiter-list> </pagelock> <pagelock fileid="1" pageid="943344" dbid="5" objectname="" id="lock108aab600" mode="U" associatedObjectId="72057594356957184"> <owner-list> <owner id="process541880988" mode="U"/> </owner-list> <waiter-list> <waiter id="process65d220bc8" mode="U" requestType="wait"/> </waiter-list> </pagelock> <pagelock fileid="1" pageid="945123" dbid="5" objectname="" id="lock10e19fb00" mode="U" associatedObjectId="72057594356957184"> <owner-list/> <waiter-list> <waiter id="process541880988" mode="U" requestType="wait"/> </waiter-list> </pagelock> <exchangeEvent id="Pipe597c78900" WaitType="e_waitPipeGetRow" nodeId="9"> <owner-list> <owner id="process65d220bc8"/> </owner-list> <waiter-list> <waiter id="process6a9baf288"/> </waiter-list> </exchangeEvent> <exchangeEvent id="Pipe597c78800" WaitType="e_waitPipeGetRow" nodeId="9"> <owner-list> <owner id="process65d220bc8"/> </owner-list> <waiter-list> <waiter id="process2edc00088"/> </waiter-list> </exchangeEvent> <exchangeEvent id="Pipe597c78a00" WaitType="e_waitPipeGetRow" nodeId="9"> <owner-list> <owner id="process65d220bc8"/> </owner-list> <waiter-list> <waiter id="process541880748"/> </waiter-list> </exchangeEvent> <exchangeEvent id="Pipe597c78a80" WaitType="e_waitPipeGetRow" nodeId="9"> <owner-list> <owner id="process65d220bc8"/> </owner-list> <waiter-list> <waiter id="process563d50088"/> </waiter-list> </exchangeEvent> <exchangeEvent id="Pipe597c78b80" WaitType="e_waitPipeGetRow" nodeId="9"> <owner-list> <owner id="process65d220bc8"/> </owner-list> <waiter-list> <waiter id="process49a7f1dc8"/> </waiter-list> </exchangeEvent> <exchangeEvent id="Pipe597c78780" WaitType="e_waitPipeGetRow" nodeId="9"> <owner-list> <owner id="process65d220bc8"/> </owner-list> <waiter-list> <waiter id="process6cee41288"/> </waiter-list> </exchangeEvent> <exchangeEvent id="Pipe597c78980" WaitType="e_waitPipeGetRow" nodeId="9"> <owner-list> <owner id="process65d220bc8"/> </owner-list> <waiter-list> <waiter id="process65cb58748"/> </waiter-list> </exchangeEvent> <exchangeEvent id="Pipe597c78880" WaitType="e_waitPipeGetRow" nodeId="9"> <owner-list> <owner id="process65d220bc8"/> </owner-list> <waiter-list> <waiter id="process599025708"/> </waiter-list> </exchangeEvent> <exchangeEvent id="Pipe597c78380" WaitType="e_waitPipeGetRow" nodeId="7"> <owner-list> <owner id="process6cee41288"/> <owner id="process2edc00088"/> <owner id="process599025708"/> <owner id="process6a9baf288"/> <owner id="process65cb58748"/> <owner id="process541880748"/> <owner id="process563d50088"/> <owner id="process49a7f1dc8"/> </owner-list> <waiter-list> <waiter id="process58975a748"/> </waiter-list> </exchangeEvent> </resource-list> </deadlock> </deadlock-list> Thanks. |
|
|
|
|
|
Interpreting Deadlock XML