Getting SQL deadlock when inserting and deleting at the same time

Question

Currently I get occasional deadlocks in the MSSQL database of my .NET application. I am using stored procedures to add data to a table "DATA_CONT". Additionally there is a periodic task to delete old data from this table. Sometimes when inserting and deleting procedure perform at the same time, I get a deadlock.

The table DATA_CONT owns one clustered index containing the Foreign Key CID (uniqueidentifier) and the ResultDate (datetime2(3)).

This is the deadlock xml graph of MS SQL:

<deadlock>
 <victim-list>
  <victimProcess id="process6137528c8" />
 </victim-list>
 <process-list>
  <process id="process6137528c8" taskpriority="5" logused="0" waitresource="KEY: 7:72057594046185472 (0d3d2e12b103)" waittime="1637" ownerId="9357686" transactionname="user_transaction" lasttranstarted="2018-11-12T11:20:55.167" XDES="0x61e4703b0" lockMode="RangeS-U" schedulerid="7" kpid="14644" status="suspended" spid="53" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2018-11-12T11:20:55.167" lastbatchcompleted="2018-11-12T11:20:55.167" lastattention="1900-01-01T00:00:00.167" clientapp=".Net SqlClient Data Provider" hostname="FE-Z13YL" hostpid="24056" loginname="DE\afr3fe" isolationlevel="serializable (4)" xactid="9357686" currentdb="7" lockTimeout="1800" clientoption1="673316896" clientoption2="128056">
   <executionStack>
    <frame procname="MyDatabase.dbo.Proc_DeleteDataCont" line="12" stmtstart="708" stmtend="954" sqlhandle="0x03000700f2a6e25b72b58a0092a9000001000000000000000000000000000000000000000000000000000000">
Delete From
        [DATA_CONT]
    WHERE
        CID = @p_CID AND
        (@p_DeleteOlderThan IS NULL OR ResultDate &lt; @p_DeleteOlderThan    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 7 Object Id = 1541580530]   </inputbuf>
  </process>
  <process id="process658113468" taskpriority="0" logused="592" waitresource="KEY: 7:72057594046185472 (ae5185f64403)" waittime="1711" ownerId="9357687" transactionname="user_transaction" lasttranstarted="2018-11-12T11:20:55.170" XDES="0x65cd77000" lockMode="RangeI-N" schedulerid="6" kpid="21008" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-11-12T11:20:55.170" lastbatchcompleted="2018-11-12T11:20:55.170" lastattention="1900-01-01T00:00:00.170" clientapp=".Net SqlClient Data Provider" hostname="FE-Z13YL" hostpid="24056" loginname="DE\afr3fe" isolationlevel="serializable (4)" xactid="9357687" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
   <executionStack>
    <frame procname="MyDatabase.dbo.Proc_AddDataCont" line="8" stmtstart="266" stmtend="598" sqlhandle="0x030007005654c5593a84070188a9000001000000000000000000000000000000000000000000000000000000">
INSERT INTO [DATA_CONT] SELECT CID, Value, LocationData, ResultDate, GETUTCDATE() FROM @p_DataContLis    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 7 Object Id = 1506104406]   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <keylock hobtid="72057594046185472" dbid="7" objectname="MyDatabase.dbo.DATA_CONT" indexname="IX_DATA_CONT" id="lock620b19a80" mode="X" associatedObjectId="72057594046185472">
   <owner-list>
    <owner id="process658113468" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process6137528c8" mode="RangeS-U" requestType="wait" />
   </waiter-list>
  </keylock>
  <keylock hobtid="72057594046185472" dbid="7" objectname="MyDatabase.dbo.DATA_CONT" indexname="IX_DATA_CONT" id="lock620b19800" mode="RangeS-U" associatedObjectId="72057594046185472">
   <owner-list>
    <owner id="process6137528c8" mode="RangeS-U" />
   </owner-list>
   <waiter-list>
    <waiter id="process658113468" mode="RangeI-N" requestType="wait" />
   </waiter-list>
  </keylock>
 </resource-list>
</deadlock>

For me it seems that both locks are being escalated. How can I avoid this behavior?

Answer 1

I assume that it is not critical your delete process to skip some rows (from time to time). My idea is to skip locked rows when deleting using "READPAST" hint like that:

Delete From
    [DATA_CONT] WITH (READPAST)
WHERE
    CID = @p_CID AND
    (@p_DeleteOlderThan IS NULL OR ResultDate < @p_DeleteOlderThan