3

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

enter image description here

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

enter image description here

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]))
1

1 Answer 1

0

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

1
  • And if adding the proper indexes doesn't fix it, escalate to adding TABLOCKX table hint in the query. Jul 14, 2022 at 22:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.