Deadlock on DELETE statements (stored procedure & adhoc query)

Question

I am experiencing an issue with deadlocks when running a stored procedure that is run every 15 minutes. There is also an adhoc query that is being run by an external program (I don't have access to).

The stored procedure clears the tables in reverse order based on dependencies for each record related to the main table.

Things i've looked at:

• Taskpriority to different levels for procedure but didn't seem to fix anything.
• With(RowLock) - doesn't seem to be feasible as options table could delete over 100+ records
• Set Transaction Level Serializable for procedure - not sure if it would change anything

Key Information (all GUID):

• MainTable: Clustered (ID)
• Master: Unique Non-Clustered(MasterID)
• Items: Clustered (ID)
• Options: Unique Non-Clustered (ItemID)

Could someone point me in the right direction to avoid these deadlocks and see if I am missing something?

Estimated Execution Plan: https://www.brentozar.com/pastetheplan/?id=BJYGDs4Yh

Stored Procedure:

ALTER PROCEDURE [dbo].[ClearMainTable]
    -- Add the parameters for the stored procedure here
    @ID uniqueidentifier
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Clear Table in reverse order so no errors occur

    -- Clear Options
    DELETE  Options
    FROM    MainTable INNER JOIN Master  ON MainTable.ID = Master.ID
                      INNER JOIN Items   ON Master.MasterID = Items.MasterID
                      INNER JOIN Options ON Items.ItemID = Options.ItemID
    WHERE   MainTable.ID = @ID
    

    -- Clear Items
    DELETE  Items
    FROM    MainTable INNER JOIN Master  ON MainTable.ID = Master.ID
                      INNER JOIN Items   ON Master.MasterID = Items.MasterID
    WHERE   MainTable.ID = @ID


    -- Clear Master
    DELETE  Master
    FROM    MainTable    INNER JOIN Master  ON MainTable.ID = Master.ID
    WHERE   MainTable.ID = @ID


    -- Clear MainTable
    DELETE  MainTable
    FROM    MainTable
    WHERE   MainTable.ID = @ID

END

Deadlock XML:

<deadlock>
  <victim-list>
    <victimProcess id="process86809b498" />
  </victim-list>
  <process-list>
    <process id="process86809b498" taskpriority="0" logused="0" waitresource="PAGE: 9:1:32318 " waittime="4403" ownerId="470153035" transactionname="DELETE" lasttranstarted="2023-07-06T11:00:25.820" XDES="0x877a263a8" lockMode="IU" schedulerid="2" kpid="11324" status="suspended" spid="138" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-07-06T11:00:25.817" lastbatchcompleted="2023-07-06T11:00:25.817" lastattention="1900-01-01T00:00:00.817" clientapp="TestOptions" hostname="TEST" hostpid="8992" loginname="othrusr" isolationlevel="read committed (2)" xactid="470153035" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" sqlhandle="0x02000000b80ec128c7a76f4cff49415c7ff0000000000000000000000000000000000000000">
(@1 varchar(8000))DELETE [Options]  WHERE [ItemID]=@1    </frame>
        <frame procname="adhoc" line="1" sqlhandle="0x020000008384de2d4748b2e8ce75d29b5500000000000000000000000000000000000000000">
DELETE FROM Options WHERE ItemID in ('d7f79eb4-17d2-4f18-95d3-36e9dc934315')    </frame>
      </executionStack>
      <inputbuf>
DELETE FROM Options WHERE ItemID in ('d7f79eb4-17d2-4f18-95d3-36e9dc934315')   </inputbuf>
    </process>
    <process id="process85d526188" taskpriority="0" logused="0" waitresource="PAGE: 9:1:2399 " waittime="4399" ownerId="470153086" transactionname="DELETE" lasttranstarted="2023-07-06T11:00:25.843" XDES="0x877a26d28" lockMode="U" schedulerid="1" kpid="6112" status="suspended" spid="245" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-07-06T11:00:25.843" lastbatchcompleted="2023-07-06T11:00:25.843" lastattention="1900-01-01T00:00:00.843" clientapp=".Net SqlClient Data Provider" hostname="Prod" hostpid="67068" loginname="testuser" isolationlevel="read committed (2)" xactid="470153086" currentdb="9" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="TestDatabase.dbo.ClearMainTable" line="18" stmtstart="1106" stmtend="1890" sqlhandle="0x0300090099fb693000000000000000000000000000000000000000000000000000000">
DELETE  Options
    FROM    MainTable INNER JOIN Master  ON MainTable.ID = Master.ID
                      INNER JOIN Item    ON Master.MasterID = Item.MasterID
                      INNER JOIN Options ON Item.ItemID = Options.ItemID
    WHERE   MainTable.ID = @ID
    

    -- Clear Item    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 9 Object Id = 1013578649]   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <pagelock fileid="1" pageid="32318" dbid="9" subresource="FULL" objectname="TestDatabase.dbo.Options" id="lock7ebdbc680" mode="U" associatedObjectId="72057594042384384">
      <owner-list>
        <owner id="process85d526188" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process86809b498" mode="IU" requestType="wait" />
      </waiter-list>
    </pagelock>
    <pagelock fileid="1" pageid="2399" dbid="9" subresource="FULL" objectname="TestDatabase.dbo.Options" id="lock7ef5f5700" mode="IU" associatedObjectId="72057594042384384">
      <owner-list>
        <owner id="process86809b498" mode="IU" />
      </owner-list>
      <waiter-list>
        <waiter id="process85d526188" mode="U" requestType="wait" />
      </waiter-list>
    </pagelock>
  </resource-list>
</deadlock>

Answer 1

Deadlock occurs because SQL Server is scanning the entire "Options" table due to the absence of the index.

Try creating the missing index as shown in the estimated execution plan:

CREATE NONCLUSTERED INDEX idx_options_itemid ON [dbo].[Options] ([ItemID])

Also, consider replacing this stored procedure with an "on delete cascade" constraint in the MainTable.