Advertisement
Kristen

SQL Deadlock

Aug 2nd, 2011
466
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
XML 5.93 KB | None | 0 0
  1. <deadlock-list>
  2.     <deadlock victim="processb292e2c8">
  3.         <process-list>
  4.             <process id="processb292e2c8" taskpriority="0" logused="0" waitresource="PAGE: 6:1:5538641" waittime="2454" ownerId="934602261" transactionname="DELETE" lasttranstarted="2011-08-02T14:03:29.280" XDES="0x8fa77fbd0" lockMode="U" schedulerid="8" kpid="6192" status="suspended" spid="64" sbid="0" ecid="28" priority="0" trancount="0" lastbatchstarted="2011-08-02T13:47:24.437" lastbatchcompleted="2011-08-02T13:39:09.373" clientapp="SQL Query Analyzer" hostname="MyQuery" hostpid="3644" isolationlevel="read committed (2)" xactid="934602261" currentdb="6" lockTimeout="4294967295" clientoption1="673187936" clientoption2="128056">
  5.                 <executionStack>
  6.                     <frame procname="adhoc" line="11" stmtstart="728" stmtend="1974" sqlhandle="0x02000000573dc22b1bf6453a2467625f5d9acd0f0517098d">
  7. DELETE D
  8. FROM    MyTempTable AS X
  9.     JOIN dbo.MyTable AS D
  10.          ON D.ThePK_ID = X.ThePK_ID
  11. WHERE   T_ID BETWEEN @intBatchStart AND @intBatchStart + @intBatchSize     </frame>
  12.                 </executionStack>
  13.                 <inputbuf>
  14.                 </inputbuf>
  15.             </process>
  16.             <process id="process8c0ad9288" taskpriority="0" logused="1184" waitresource="PAGE: 6:1:5575207" waittime="2458" ownerId="934602634" transactionname="INSERT" lasttranstarted="2011-08-02T14:03:29.463" XDES="0x800dd8a0" lockMode="IX" schedulerid="15" kpid="12480" status="suspended" spid="72" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-08-02T14:03:29.443" lastbatchcompleted="2011-08-02T14:03:29.427" clientapp="Internet Information Services" hostname="WebServer" hostpid="2752" loginname="WebServerLogin" isolationlevel="read committed (2)" xactid="934602634" currentdb="6" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
  17.                 <executionStack>
  18.                     <frame procname="MyDatabase.dbo.rk_SP_CreateNewSession" line="444" stmtstart="27318" stmtend="29482" sqlhandle="0x0300060019e9131729676a00fc9e00000100000000000000">
  19. unknown     </frame>
  20.                     <frame procname="MyDatabase.dbo.rk_SP_PageLogInit" line="247" stmtstart="18792" stmtend="20128" sqlhandle="0x030006005f460964d4747300fc9e00000100000000000000">
  21. EXEC @intErrNo = dbo.rk_SP_CreateNewSession
  22.     @ThePK_ID               = @ThePK_ID OUTPUT,
  23.     ... various parameters ...
  24.      </frame>
  25.                 </executionStack>
  26.                 <inputbuf>
  27. Proc [Database Id = 6 Object Id = 1678329439]    </inputbuf>
  28.             </process>
  29.             <process id="process41eb288" taskpriority="0" logused="10000" waittime="2456" schedulerid="15" kpid="13132" status="suspended" spid="64" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2011-08-02T13:47:24.437" lastbatchcompleted="2011-08-02T13:39:09.373" clientapp="SQL Query Analyzer" hostname="MyQuery" hostpid="3644" isolationlevel="read committed (2)" xactid="934602261" currentdb="6" lockTimeout="4294967295" clientoption1="673187936" clientoption2="128056">
  30.                 <executionStack>
  31.                     <frame procname="adhoc" line="11" stmtstart="728" stmtend="1974" sqlhandle="0x02000000573dc22b1bf6453a2467625f5d9acd0f0517098d">
  32. DELETE D
  33. FROM    MyTempTable AS X
  34.     JOIN dbo.MyTable AS D
  35.          ON D.ThePK_ID = X.ThePK_ID
  36. WHERE   T_ID BETWEEN @intBatchStart AND @intBatchStart + @intBatchSize     </frame>
  37.                 </executionStack>
  38.                 <inputbuf>
  39.                 </inputbuf>
  40.             </process>
  41.             <process id="processb292e748" taskpriority="0" logused="10000" waittime="2639" schedulerid="8" kpid="13188" status="suspended" spid="64" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-08-02T13:47:24.437" lastbatchcompleted="2011-08-02T13:39:09.373" lastattention="2011-08-02T13:33:17.373" clientapp="SQL Query Analyzer" hostname="MyQuery" hostpid="3644" loginname="MyLogin" isolationlevel="read committed (2)" xactid="934602261" currentdb="6" lockTimeout="4294967295" clientoption1="673187936" clientoption2="128056">
  42.                 <executionStack>
  43.                     <frame procname="adhoc" line="11" stmtstart="728" stmtend="1974" sqlhandle="0x02000000573dc22b1bf6453a2467625f5d9acd0f0517098d">
  44. DELETE D
  45. FROM    MyTempTable AS X
  46.     JOIN dbo.MyTable AS D
  47.          ON D.ThePK_ID = X.ThePK_ID
  48. WHERE   T_ID BETWEEN @intBatchStart AND @intBatchStart + @intBatchSize     </frame>
  49.                 </executionStack>
  50.                 <inputbuf>
  51. DECLARE @intBatchStart int, @intBatchSize int, @intRowCount int, @dtStart datetime, @strMsg nvarchar(1000)
  52. SET NOCOUNT ON
  53. SELECT  @intBatchStart=MIN(T_ID), @intBatchSize=100, @intRowCount=1
  54. FROM    MyTempTable AS X
  55.     JOIN dbo.MyTable AS D WITH (NOLOCK)
  56.          ON D.ThePK_ID = X.ThePK_ID
  57.  
  58. WHILE @intRowCount &gt;= 1
  59. BEGIN
  60. SELECT  @dtStart = GetDate()
  61. DELETE D
  62. FROM    MyTempTable AS X
  63.     JOIN dbo.MyTable AS D
  64.          ON D.ThePK_ID = X.ThePK_ID
  65. WHERE   T_ID BETWEEN @intBatchStart AND @intBatchStart + @intBatchSize
  66. SELECT  @intRowCount = @@ROWCOUNT, @    </inputbuf>
  67.             </process>
  68.         </process-list>
  69.         <resource-list>
  70.             <pagelock fileid="1" pageid="5538641" dbid="6" objectname="MyDatabase.dbo.MyTable" id="lock6b8edf500" mode="IX" associatedObjectId="72057594238271488">
  71.                 <owner-list>
  72.                     <owner id="process8c0ad9288" mode="IX"/>
  73.                 </owner-list>
  74.                 <waiter-list>
  75.                     <waiter id="processb292e2c8" mode="U" requestType="wait"/>
  76.                 </waiter-list>
  77.             </pagelock>
  78.             <pagelock fileid="1" pageid="5575207" dbid="6" objectname="MyDatabase.dbo.MyTable" id="lock62de6d300" mode="U" associatedObjectId="72057594238271488">
  79.                 <owner-list>
  80.                     <owner id="processb292e748" mode="U"/>
  81.                 </owner-list>
  82.                 <waiter-list>
  83.                     <waiter id="process8c0ad9288" mode="IX" requestType="wait"/>
  84.                 </waiter-list>
  85.             </pagelock>
  86.             <exchangeEvent id="Pipe9ed160e00" WaitType="e_waitPipeGetRow" nodeId="20">
  87.                 <owner-list>
  88.                     <owner id="processb292e2c8"/>
  89.                 </owner-list>
  90.                 <waiter-list>
  91.                     <waiter id="process41eb288"/>
  92.                 </waiter-list>
  93.             </exchangeEvent>
  94.             <exchangeEvent id="Port802d9a00" WaitType="e_waitPortOpen" nodeId="14">
  95.                 <owner-list>
  96.                     <owner id="process41eb288"/>
  97.                 </owner-list>
  98.                 <waiter-list>
  99.                     <waiter id="processb292e748"/>
  100.                 </waiter-list>
  101.             </exchangeEvent>
  102.         </resource-list>
  103.     </deadlock>
  104. </deadlock-list>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement