主页 / dba / 问题 / 298052
Accepted
ASh
Asked: 2021-08-15 02:12:45 +0800 CST

报表服务器数据库死锁

  • 772

从 ReportServer.dbo.ExpireExecutionLogEntries 存储过程中获取死锁数。我花了一整天的时间看有关死锁的教程，但无法弄清楚这一点。

<deadlock>
 <victim-list>
  <victimProcess id="Victim" />
 </victim-list>
 <process-list>
  <process id="Victim" taskpriority="0" logused="10000" waittime="101" schedulerid="1" kpid="596" status="suspended" spid="115" sbid="0" ecid="16" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1ee21307c28" taskpriority="0" logused="10000" waittime="419597" schedulerid="5" kpid="4200" status="suspended" spid="115" sbid="0" ecid="6" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1ef541ea108" taskpriority="0" logused="10000" waittime="419500" schedulerid="8" kpid="6792" status="suspended" spid="115" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1f225504ca8" taskpriority="0" logused="10000" waittime="419913" schedulerid="2" kpid="13276" status="suspended" spid="115" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1f8d4029088" taskpriority="0" logused="10000" waittime="419598" schedulerid="3" kpid="14192" status="suspended" spid="115" sbid="0" ecid="5" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1f8d5adc4e8" taskpriority="0" logused="10000" waittime="419598" schedulerid="6" kpid="26268" status="suspended" spid="115" sbid="0" ecid="4" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1e26338d848" taskpriority="0" logused="10000" waittime="419598" schedulerid="4" kpid="21616" status="suspended" spid="115" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1ee384044e8" taskpriority="0" logused="10000" waittime="419597" schedulerid="7" kpid="9460" status="suspended" spid="115" sbid="0" ecid="7" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1e0f41ed088" taskpriority="0" logused="10000" waittime="419598" schedulerid="13" kpid="16024" status="suspended" spid="115" sbid="0" ecid="10" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1ed54019c28" taskpriority="0" logused="10000" waittime="419596" schedulerid="14" kpid="25028" status="suspended" spid="115" sbid="0" ecid="9" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1e5f96828c8" taskpriority="0" logused="10000" waittime="419913" schedulerid="9" kpid="17316" status="suspended" spid="115" sbid="0" ecid="15" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1e4e925aca8" taskpriority="0" logused="10000" waittime="419598" schedulerid="15" kpid="24752" status="suspended" spid="115" sbid="0" ecid="8" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1f8d402c8c8" taskpriority="0" logused="10000" waittime="419913" schedulerid="12" kpid="10720" status="suspended" spid="115" sbid="0" ecid="14" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1e5eb190108" taskpriority="0" logused="10000" waittime="419596" schedulerid="11" kpid="17880" status="suspended" spid="115" sbid="0" ecid="11" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1f69dfae8c8" taskpriority="0" logused="10000" waittime="419913" schedulerid="16" kpid="14748" status="suspended" spid="115" sbid="0" ecid="13" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1ea5e987088" taskpriority="0" logused="0" waitresource="PAGE: 9:1:288319 " waittime="419598" ownerId="10654834267" transactionname="DELETE" lasttranstarted="2021-08-14T01:59:59.513" XDES="0x1f3d5876ca0" lockMode="U" schedulerid="10" kpid="17232" status="suspended" spid="115" sbid="0" ecid="12" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1e5eb1904e8" taskpriority="0" logused="0" waitresource="PAGE: 9:1:288319 " waittime="419669" ownerId="10654834619" transactionname="DELETE" lasttranstarted="2021-08-14T01:59:59.757" XDES="0x1f49a45b900" lockMode="U" schedulerid="11" kpid="18024" status="suspended" spid="88" sbid="0" ecid="12" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.757" lastbatchcompleted="2021-08-14T01:59:59.757" lastattention="1900-01-01T00:00:00.757" clientapp="Report Server" hostname="SERVER1" hostpid="24648" isolationlevel="read committed (2)" xactid="10654834619" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1eb3737aca8" taskpriority="0" logused="10000" waittime="419914" schedulerid="9" kpid="25564" status="suspended" spid="115" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" loginname="MYPCORP\mypadmin" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="6" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1ee21307c28" />
   </owner-list>
   <waiter-list>
    <waiter id="Victim" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="7" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1ee384044e8" />
   </owner-list>
   <waiter-list>
    <waiter id="process1ee21307c28" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="2" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1e26338d848" />
   </owner-list>
   <waiter-list>
    <waiter id="process1ef541ea108" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="1" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1ef541ea108" />
   </owner-list>
   <waiter-list>
    <waiter id="process1f225504ca8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="5" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="Victim" />
   </owner-list>
   <waiter-list>
    <waiter id="process1f8d4029088" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="4" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1f8d4029088" />
   </owner-list>
   <waiter-list>
    <waiter id="process1f8d5adc4e8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="3" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1f8d5adc4e8" />
   </owner-list>
   <waiter-list>
    <waiter id="process1e26338d848" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="8" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1e0f41ed088" />
   </owner-list>
   <waiter-list>
    <waiter id="process1ee384044e8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="9" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1e4e925aca8" />
   </owner-list>
   <waiter-list>
    <waiter id="process1e0f41ed088" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="11" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1ea5e987088" />
   </owner-list>
   <waiter-list>
    <waiter id="process1ed54019c28" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="16" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1eb3737aca8" />
   </owner-list>
   <waiter-list>
    <waiter id="process1e5f96828c8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="10" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1ed54019c28" />
   </owner-list>
   <waiter-list>
    <waiter id="process1e4e925aca8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="15" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1e5f96828c8" />
   </owner-list>
   <waiter-list>
    <waiter id="process1f8d402c8c8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="13" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1f69dfae8c8" />
   </owner-list>
   <waiter-list>
    <waiter id="process1e5eb190108" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="14" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1f8d402c8c8" />
   </owner-list>
   <waiter-list>
    <waiter id="process1f69dfae8c8" />
   </waiter-list>
  </exchangeEvent>
  <pagelock fileid="1" pageid="288319" dbid="9" subresource="FULL" objectname="ReportServer.dbo.ExecutionLogStorage" id="lock1f4bb84dc80" mode="U" associatedObjectId="72057594050052096">
   <owner-list>
    <owner id="process1e5eb1904e8" mode="U" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="process1ea5e987088" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="288319" dbid="9" subresource="FULL" objectname="ReportServer.dbo.ExecutionLogStorage" id="lock1f4bb84dc80" mode="U" associatedObjectId="72057594050052096">
   <owner-list>
    <owner id="process1e5eb190108" mode="U" />
   </owner-list>
   <waiter-list>
    <waiter id="process1e5eb1904e8" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Coordinator" nodeId="2" tid="0" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1f225504ca8" />
   </owner-list>
   <waiter-list>
    <waiter id="process1eb3737aca8" />
   </waiter-list>
  </exchangeEvent>
 </resource-list>
</deadlock>

如果有人能给我一些见解，我会松一口气的。

谢谢

sql-server deadlock

1 个回答

  1. Best Answer

    相关位在这里：

       <process id="process1ea5e987088" taskpriority="0" logused="0" waitresource="PAGE: 9:1:288319 " waittime="419598" ownerId="10654834267" transactionname="DELETE" lasttranstarted="2021-08-14T01:59:59.513" XDES="0x1f3d5876ca0" lockMode="U" schedulerid="10" kpid="17232" status="suspended" spid="115" sbid="0" ecid="12" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="VM-AE-SQL-2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
    </process>
    <process id="process1e5eb1904e8" taskpriority="0" logused="0" waitresource="PAGE: 9:1:288319 " waittime="419669" ownerId="10654834619" transactionname="DELETE" lasttranstarted="2021-08-14T01:59:59.757" XDES="0x1f49a45b900" lockMode="U" schedulerid="11" kpid="18024" status="suspended" spid="88" sbid="0" ecid="12" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.757" lastbatchcompleted="2021-08-14T01:59:59.757" lastattention="1900-01-01T00:00:00.757" clientapp="Report Server" hostname="VM-AE-SQL-1" hostpid="24648" isolationlevel="read committed (2)" xactid="10654834619" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
    </process>

    两个 SSRS 服务器 SERVER1 和 SERVER2 同时运行此 proc。SERVER2 正在使用并行计划，该计划占所有其他阻塞进程，但它们都属于 spid 115。

    不确定所有 ssrs 前端服务器是否应该同时运行此过程，因此您可以尝试更新到最新版本。

    但归根结底，这可能并不比烦恼和资源浪费更糟糕。其中一个会话成功完成了清理工作。

    您始终可以将 SSRS 配置为无限期保留 ExecutionLog 条目，并安排代理作业ExpireExecutionLogEntries按照您选择的时间表运行。

    • 6

相关问题