Sql-server – How to determine why the stored procedure is getting deadlocked

Instead of checking to see if the data already exists and then doing an INSERT, try doing an INSERT IGNORE and seeing if the LAST_INSERT_ID() came back with the same value or not on two calls.

CREATE PROCEDURE sp_CreateCtrMsgTagIfNotExist
(
    siteId INT, logTimestamp DATETIME, IntervalId INT,
    digestMatched TINYINT(1), OUT returnpk INT
)
BEGIN

   DECLARE rpk INT;

   SELECT LAST_INSERT_ID() INTO rpk;
   START TRANSACTION;       
   INSERT IGNORE INTO Site_Msg_Tag
       (Site_Msg_Tag_Id,Unit_Id,Log_Timestamp,Interval_Id,Digest_Matched)
       VALUES (NULL,siteId,logTimestamp,IntervalId,digestMatched);
   SELECT LAST_INSERT_ID() INTO returnpk;
   IF returnpk = rpk THEN /*1*/
       SELECT Site_Msg_Tag_Id INTO rpk FROM Site_Msg_Tag
       WHERE Unit_Id = siteId AND Log_Timestamp = logTimestamp
       AND Interval_Id = IntervalId ;
       SET returnpk = 0 - rpk;
   END IF;/*1*/
   COMMIT;

END;

Checking LAST_INSERT_ID()

• If two calls produced different values, you should make the stored procedure set returnpk as is to indicate that the row was newly inserted.
• If two calls produced the same value, then go query for the Site_Msg_Tag_Id. You should make the stored procedure set returnpk as negative to let you know the Site_Msg_Tag_Id was already there.

Give it a Try !!!

Original post describes a classic deadlock scenario with a twist, namely, a single DELETE ... WITH statement, which acquires 2 different kinds of locks.

Recommendation - change table from All Page Lock (APL) to Data Row Lock (DRL) scheme

• clustered index will be replaced by placement index separate from data page
• index access / updates will use latches rather than logical locks
• row-level deadlock conflict is less probably (than page-level's)

Both SPIDs are executing the same sproc with the 1st DELETE ... WHERE statement demanding 2 different kinds of locks:

• initially an UPDATE lock on a page when scanning for a row to delete (UPDATE lock is compatible with shared locks only but not with another UPDATE lock or exclusive lock);
• once a target row is found on a page, then the UPDATE lock must be upgraded to an exclusive lockbefore deleting that row.

The DEADLOCK events posted in your original question confirm the scenario :

• SPID 134 has held an exclusive lock on a page while waiting to acquire an UPDATE lock on another page i.e. it has deleted at least one row already and is continuing with searching for another (row to delete);
• SPID 166 blocks the first SPID's acquiring an UPDATE lock on a page while waiting to apply an exclusive lock on another page, which has an exclusive lock held by SPID 134 already (after deleting 1 row or more).