多个AOS的死锁(Dynamics，2012 R2)

Question

我们使用2012 R2，并且有多个AOS服务器，在某些情况下，我们在特定的表上体验数据库死锁。我们正在使用SQL死锁检测器进行调查。

AOS1 psid XX (Delete) -><- AOS2 psid XX (Update)

它们都在访问表批处理。AOS1正在执行删除操作，而AOS2正在执行更新操作。

我对Dynamics并不是真正的技术，但是我想问一下这是否是一个配置设置，或者我们的选项是什么，因为我知道您可以使用Dynamics拥有多个AOS？目前，我们正在考虑使用单一的AOS来避免这种情况。蒂娅！

更新:这是从SQL死锁检测器提取的信息

<EVENT_INSTANCE>
  <EventType>DEADLOCK_GRAPH</EventType> 
  <PostTime>2016-05-18T08:23:26.457</PostTime> 
  <SPID>31</SPID> 
- <TextData>
- <deadlock-list>
- <deadlock victim="process187b921088">
- <process-list>
- <process id="process187b921088" taskpriority="0" logused="0" waitresource="KEY: 5:72057594054180864 (1012d480fedd)" waittime="1388" ownerId="7211112518" transactionname="user_transaction" lasttranstarted="2016-05-18T08:23:25.067" XDES="0x1561eaf0a0" lockMode="S" schedulerid="18" kpid="4772" status="suspended" spid="151" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-05-18T08:23:25.070" lastbatchcompleted="2016-05-18T08:23:25.070" lastattention="2016-05-16T15:37:08.080" clientapp="Microsoft Dynamics AX" hostname="XXX-PRODAOS3" hostpid="3796" loginname="XXX\axaos" isolationlevel="read committed (2)" xactid="7211112518" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
- <executionStack>
  <frame procname="adhoc" line="1" stmtstart="212" stmtend="1406" sqlhandle="0x0200000068d839079e1e808253171c305e0cb77c0e699b800000000000000000000000000000000000000000">unknown</frame> 
  </executionStack>
  <inputbuf>(@P1 int,@P2 datetime2,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int,@P11 int,@P12 int)UPDATE BATCH SET STATUS=@P1,MODIFIEDDATETIME=@P2,RECVERSION=@P3 WHERE ((STATUS=@P4) AND (CONSTRAINTTYPE=@P5)) AND EXISTS (SELECT 'x' FROM BATCHJOB T2 WITH ( READCOMMITTEDLOCK) WHERE ((T2.STATUS=@P6) AND (BATCH.BATCHJOBID=T2.RECID)) AND NOT (EXISTS (SELECT 'x' FROM BATCHCONSTRAINTS T3 WITH ( READCOMMITTEDLOCK) WHERE EXISTS (SELECT 'x' FROM BATCH T4 WITH ( READCOMMITTEDLOCK) WHERE (((T3.DEPENDSONBATCHID=T4.RECID) AND (T3.BATCHID=BATCH.RECID)) AND ((((T4.STATUS<>@P7) AND (T4.STATUS<>@P8)) OR ((T3.EXPECTEDSTATUS=@P9) AND (T4.STATUS=@P10))) OR ((T3.EXPECTEDSTATUS=@P11) AND (T4.STATUS=@P12))))))))</inputbuf> 
  </process>
- <process id="process187b911848" taskpriority="0" logused="1496" waitresource="KEY: 5:72057594054967296 (1f6c1d9698d0)" waittime="1339" ownerId="7211110381" transactionname="user_transaction" lasttranstarted="2016-05-18T08:23:25.050" XDES="0x187b9610d0" lockMode="U" schedulerid="16" kpid="2028" status="suspended" spid="87" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-05-18T08:23:25.117" lastbatchcompleted="2016-05-18T08:23:25.117" lastattention="1900-01-01T00:00:00.117" clientapp="Microsoft Dynamics AX" hostname="XXX-PRODAOS2" hostpid="3292" loginname="XXX\axaos" isolationlevel="read committed (2)" xactid="7211110381" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
- <executionStack>
  <frame procname="adhoc" line="1" stmtstart="50" stmtend="448" sqlhandle="0x020000007a68dc3969bbab373f6d3381cb08da5d66262e300000000000000000000000000000000000000000">unknown</frame> 
  </executionStack>
  <inputbuf>(@P1 int,@P2 int,@P3 int)DELETE FROM BATCH WHERE EXISTS (SELECT 'x' FROM BATCHJOB T2 WITH ( READCOMMITTEDLOCK) WHERE (((T2.FINISHING=@P1) AND (BATCH.BATCHJOBID=T2.RECID)) AND ((BATCH.RUNTIMETASK=@P2) OR (T2.RUNTIMEJOB=@P3))))</inputbuf> 
  </process>
  </process-list>
- <resource-list>
- <keylock hobtid="72057594054180864" dbid="5" objectname="TPI_AxNew_Prod.dbo.BATCHJOB" indexname="I_2096RECID" id="lockf9d3e0580" mode="X" associatedObjectId="72057594054180864">
- <owner-list>
  <owner id="process187b911848" mode="X" /> 
  </owner-list>
- <waiter-list>
  <waiter id="process187b921088" mode="S" requestType="wait" /> 
  </waiter-list>
  </keylock>
- <keylock hobtid="72057594054967296" dbid="5" objectname="TPI_AxNew_Prod.dbo.BATCH" indexname="I_2827BATCHJOBID" id="lock15d81c0a80" mode="U" associatedObjectId="72057594054967296">
- <owner-list>
  <owner id="process187b921088" mode="U" /> 
  </owner-list>
- <waiter-list>
  <waiter id="process187b911848" mode="U" requestType="wait" /> 
  </waiter-list>
  </keylock>
  </resource-list>
  </deadlock>
  </deadlock-list>
  </TextData>
  <TransactionID /> 
  <LoginName>sa</LoginName> 
  <StartTime>2016-05-18T08:23:26.457</StartTime> 
  <ServerName>XXX-AXDB2</ServerName> 
  <LoginSid>AQ==</LoginSid> 
  <EventSequence>546028</EventSequence> 
  <IsSystem>1</IsSystem> 
  <SessionLoginName /> 
  </EVENT_INSTANCE>

Answer 1

来自SQL死锁检测器的信息告诉我们，我们正在x++代码中查找以下结构的两个SQL语句：

UPDATE BATCH 
  SET STATUS=@P1,MODIFIEDDATETIME=@P2,RECVERSION=@P3 
  WHERE ((STATUS=@P4) AND (CONSTRAINTTYPE=@P5)) AND EXISTS 
    (SELECT 'x' FROM BATCHJOB T2 WITH ( READCOMMITTEDLOCK) 
      WHERE ((T2.STATUS=@P6) AND (BATCH.BATCHJOBID=T2.RECID)) AND NOT (EXISTS 
        (SELECT 'x' FROM BATCHCONSTRAINTS T3 WITH ( READCOMMITTEDLOCK) 
          WHERE EXISTS 
            (SELECT 'x' FROM BATCH T4 WITH ( READCOMMITTEDLOCK) 
              WHERE (((T3.DEPENDSONBATCHID=T4.RECID) AND (T3.BATCHID=BATCH.RECID)) AND ((((T4.STATUS<>@P7) AND (T4.STATUS<>@P8)) OR ((T3.EXPECTEDSTATUS=@P9) AND (T4.STATUS=@P10))) OR ((T3.EXPECTEDSTATUS=@P11) AND (T4.STATUS=@P12)))))))) 


DELETE FROM BATCH WHERE EXISTS 
  (SELECT 'x' FROM BATCHJOB T2 WITH ( READCOMMITTEDLOCK) 
    WHERE (((T2.FINISHING=@P1) AND (BATCH.BATCHJOBID=T2.RECID)) AND ((BATCH.RUNTIMETASK=@P2) OR (T2.RUNTIMEJOB=@P3))))

我能够在方法BatchRun (用于更新)和方法serverProcessFinishedJobs (用于删除)中找到这些内容。这告诉我们，这是由使用多线程的批处理作业造成的。有几个开箱即用的批处理作业使用多线程，其中最常用的是主调度批处理作业。但我怀疑您的问题是由多线程批处理作业的自定义实现引起的。

按照注释中的建议，查看死锁发生时正在运行的批处理作业，并分析它们是否使用多线程，以及它们是否是自定义批处理作业。您将需要一个有经验的开发人员来进行此分析。

临时解决方案可能是Vincent16在在批处理工作中运行作业时出现死锁问题中提议的，即将批处理服务器的“最大批处理线程”设置为1，并且只允许一个批处理服务器。但是这可能会严重影响批处理作业的性能，所以在生产中使用它之前一定要测试它。