How is it I am getting deadlocks when inserting into two different tables?

Question

This is a DynamicsAX app, and I am seeing the following deadlock quite often nowadays. If I understand the output correctly, the INSERT into table CUSTTRANS is being blocked by the INSERT into table CUSTSETTLEMENT. I checked both tables, neither have triggers, so I am unclear how one caused a deadlock with the other? CUSTTRANS table does have change tracking enabled, but not CUSTSETTLEMENT.

<event name="xml_deadlock_report" package="sqlserver" timestamp="2016-07-19T23:43:24.567Z">
  <data name="xml_report">
    <value>
      <deadlock>
        <victim-list>
          <victimProcess id="processb7c273c38" />
        </victim-list>
        <process-list>
          <process id="processb7c273c38" taskpriority="0" logused="9000" waitresource="KEY: 5:72057881131876352 (9006753b740b)" waittime="37640" ownerId="221473485" transactionname="user_transaction" lasttranstarted="2016-07-19T16:42:46.317" XDES="0xb7caa3078" lockMode="RangeS-S" schedulerid="6" kpid="1940" status="suspended" spid="122" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-07-19T16:42:46.760" lastbatchcompleted="2016-07-19T16:42:46.737" lastattention="2016-07-19T13:12:49.450" clientapp="Microsoft Dynamics AX" hostname="DW22" hostpid="3076" loginname="xxx" isolationlevel="read committed (2)" xactid="221473485" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
              <frame procname="adhoc" line="1" stmtstart="2872" sqlhandle="0x020000005b952a20985c14b98773bd4fb5fd593a90918fd00000000000000000000000000000000000000000">
INSERT INTO CUSTTRANS (ACCOUNTNUM,TRANSDATE,VOUCHER,...,REPORTINGEXCHADJUSTMENTRE    </frame>
              <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>(@P1 nvarchar(21),@P2 datetime2...@P67 numeric(32,4)</inputbuf>
          </process>
          <process id="process1d15b0cf8" taskpriority="0" logused="3009456" waitresource="KEY: 5:72057881131876352 (e7873f5a29fe)" waittime="3566" ownerId="221391316" transactionname="user_transaction" lasttranstarted="2016-07-19T16:38:55.940" XDES="0x21b5e31d8" lockMode="RangeI-N" schedulerid="3" kpid="6320" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-07-19T16:43:20.997" lastbatchcompleted="2016-07-19T16:43:20.993" lastattention="2016-07-19T15:31:56.060" clientapp="Microsoft Dynamics AX" hostname="DW22" hostpid="3076" loginname="xxxxx" isolationlevel="read committed (2)" xactid="221391316" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
              <frame procname="adhoc" line="1" stmtstart="1262" sqlhandle="0x020000000af3f41be10a624051e59a5f47e43bd38431bc5f0000000000000000000000000000000000000000">
INSERT INTO CUSTSETTLEMENT (TRANSRECID,TRANSDATE,OFFSETTRANSVOUCHER,...,RECID) VALUES (@P1,@P2,@P3...,@P40)    </frame>
            </executionStack>
            <inputbuf>
(@P1 bigint,@P2 datetime2,@P3 nvarchar(21)...,@P40 bigint)INSERT INTO CUSTSETTLEMENT (TRANSRECID,TRANSDATE,OFFSETTRANSVOUCHER,...</inputbuf>
          </process>
        </process-list>
        <resource-list>
          <keylock hobtid="72057881131876352" dbid="5" objectname="MicrosoftDynamicsAX.dbo.CUSTSETTLEMENT" indexname="I_075TRANSINDEX" id="lock6d97d4380" mode="RangeX-X" associatedObjectId="72057881131876352">
            <owner-list>
              <owner id="process1d15b0cf8" mode="RangeX-X" />
            </owner-list>
            <waiter-list>
              <waiter id="processb7c273c38" mode="RangeS-S" requestType="wait" />
            </waiter-list>
          </keylock>
          <keylock hobtid="72057881131876352" dbid="5" objectname="MicrosoftDynamicsAX.dbo.CUSTSETTLEMENT" indexname="I_075TRANSINDEX" id="lock7102fa100" mode="RangeS-S" associatedObjectId="72057881131876352">
            <owner-list>
              <owner id="processb7c273c38" mode="RangeS-S" />
            </owner-list>
            <waiter-list>
              <waiter id="process1d15b0cf8" mode="RangeI-N" requestType="wait" />
            </waiter-list>
          </keylock>
        </resource-list>
      </deadlock>
    </value>
  </data>
  <action name="collect_system_time" package="package0">
    <value>2016-07-19T23:43:24.567Z</value>
  </action>
  <action name="client_hostname" package="sqlserver">
    <value />
  </action>
  <action name="context_info" package="sqlserver">
    <value />
  </action>
  <action name="attach_activity_id_xfer" package="package0">
    <value>CE2E9A01-A31C-464F-9288-AC45C7927760-574</value>
  </action>
  <action name="attach_activity_id" package="package0">
    <value>634B86DC-58AB-4870-9170-5575B9C12D0A-1</value>
  </action>
</event>

Answer 1

First thought (without looking at the graph) was - there is an FK between them. And I assume it actually is there.

CUSTTRANS probably is child table and CUSTSETTLEMENT is parent. When inserting into CUSTTRANS - server checks whether there is appropriate key in parent table CUSTSETTLEMENT (which is referenced from CUSTTRANS row). And puts shared lock on keys in parent table/index on that column.

Second transaction is inserting values in parent table CUSTSETTLEMENT and updating I_075TRANSINDEX index. I assume you are inserting many values in that table in one transaction hence in some moment you have some ranges already locked with exclusive lock and trying to lock some more. But there - on another range - is already put a shared lock from that first insert into CUSTTRANS which is checking for existence of corresponding row in parent table to ensure FK integrity.

Yes, FKs have some very common deadlock scenarios associated with them. Another one is: insert many (enough to turn on escalation) into child table and delete many from parent table - both will try to lock whole related table and will fail.

Solutions are (as always):

• raise isolation level, make those queries performed sequentially
• disable server's mechanisms of integrity control and do it yourself when needed (drop or disable FK - yes, this is a common solution in hard cases)
• shorten your transactions and lock ranges - proceed smaller number of rows in one time
• handle deadlock with try-catch and retry