Deadlock on DELETE - DELETE for parallel execution of same stored procedure

Question

I have a stored procedure which ran parallely by two processes.

Code for sp :

DECLARE @inputXML XML
DECLARE @tempTable TABLE (afid INT, ccid INT)
INSERT INTO @tempTable 
SELECT  ref.value('@afid', 'INT'),ref.value('@ccid', 'INT')
FROM @inputXML.nodes('/Ilist/aidA/afid') R ( ref )
DELETE  FROM StylinInfoTemp
     FROM    @tempTable AS temp
     WHERE   temp.afid= StylinInfoTemp.afid
         AND temp.ccid= StylinInfoTemp.ccid

this is the schema for StylinInfoTemp

Columns :

cisid       int
afid        int
ccid        int
entity      int
dtime       int
guid        uniqueidentifier
addFlags    int

Index is present on

cisid, afid, ccid

here is the schema for tempTable

(afid INT, ccid INT)

This tempTable is populated on the input received by the stored proc. tempTable in itself can never have duplicate entries. However StylinInfoTemp table may have duplicate entries on afId and ccid. But is unique on cisid, afid, ccid

eg Check duplicate values on afid below

However the stored procedure tempTable will always have unique afid.

When this stored proc executes I am getting a deadlock on the same page, I am having trouble understanding how exactly is this leading to a deadlock and why do we have same page Id below

Can someone help me understanding why deadlock, why same page id, how can two process hold and request for the same page id at the same time(image above) and how can I resolve this

DeadLock XML : https://codebeautify.org/xmlviewer/y228f34a3

Query Plan:

 DELETE  FROM StylinInfoTemp FROM    @tempTable AS temp   WHERE   temp.afid = StylinInfoTemp.afid    AND temp.ccid = StylinInfoTemp.ccid
       |--Clustered Index Delete(OBJECT:([Magneto].[dbo].[StylinInfoTemp].[StylinInfoTemp_cis_afid_ccid_PK]))
            |--Nested Loops(Left Semi Join, WHERE:(@tempTable.[afid] as [temp].[afid]=[Magneto].[dbo].[StylinInfoTemp].[afId] AND @temp.[ccid] as [temp].[ccid]=[Magneto].[dbo].[StylinInfoTemp].[ccid]))
                 |--Clustered Index Scan(OBJECT:([Magneto].[dbo].[StylinInfoTemp].[StylinInfoTemp_cis_afid_ccidd_PK]))
                 |--Table Scan(OBJECT:(@tempTable AS [temp]))

score 0 · Answer 1 · answered Jul 14 '22 at 13:29

It's hard to say for sure from just the textual plan, but it appears you have a join between scans of the table variable and the table you are deleting. The table variable is not sorted, so individual rows and pages are appearing out of order, and therefore deadlocking between different processes.

The particular XDL graph you provided also has a case where it has gone parallel, underscoring the need for proper indexes.

CREATE INDEX IX_afid_ccid ON StylinInfoTemp (afid, ccid);

Alternately, switch round your primary key

ALTER TABLE StylinInfoTemp
  DROP CONSTRAINT YourPrimaryKeyName;
ALTER TABLE StylinInfoTemp
  ADD CONSTRAINT YourPrimaryKeyName PRIMARY KEY
    (afid, ccid, cisid);

And for the table variable:

DECLARE @tempTable TABLE (afid INT, ccid INT, PRIMARY KEY (afid, ccid))
INSERT ...

The index key columns can be in either order, but should be the same order in both the main table and the table variable

Deadlock on DELETE - DELETE for parallel execution of same stored procedure

1 Answers1