How this simple Stored Procedure ended up in Deadlock

Question

Thanks for all and this forum is really helpful.

After few minutes of system running we get “Try restarting the transaction.” And its end up in deadlock.

We use MySQL 5.5.28 and the Java code calling this Stored Procedure is in synchronised block. However, I am still we getting this deadlock. When I ran, show engine innodb status;, I see the following query in deadlock:

INSERT INTO Site_Msg_Tag (Site_Msg_Tag_Id,Unit_Id,Log_Timestamp,Interval_Id,Digest_Matched)
        VALUES (NULL,siteId,logTimestamp,IntervalId,digestMatched);

Please refer below for entire Stored Procedure and the table it's updating:

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

   SET returnpk = 0 ;
   START TRANSACTION;   

   SELECT Site_Msg_Tag_Id INTO returnPK FROM Site_Msg_Tag WHERE Unit_Id = siteId AND Log_Timestamp = logTimestamp
   AND Interval_Id = IntervalId ;

   IF (returnpk IS NULL OR returnpk = 0  )
   THEN /*1*/
        INSERT 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;
    END IF;/*1*/

    COMMIT;

END |

DELIMITER ;

Table :

| Site_Msg_Tag | CREATE TABLE `Site_Msg_Tag` (
  `Site_Msg_Tag_Id` int(11) NOT NULL AUTO_INCREMENT,
  `Unit_Id` int(11) NOT NULL,
  `Log_Timestamp` datetime NOT NULL,
  `Interval_Id` int(11) NOT NULL,
  `Digest_Matched` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`Site_Msg_Tag_Id`),
  UNIQUE KEY `ix_Site_Msg_Tag_Ts_Unit` (`Unit_Id`,`Log_Timestamp`,`Interval_Id`),
  UNIQUE KEY `ix_Site_Msg_Tag_Unit_Ts` (`Log_Timestamp`,`Unit_Id`,`Interval_Id`),
  KEY `FK_Site_Msg_Tag_Interval_Id` (`Interval_Id`),
  CONSTRAINT `FK_Site_Msg_Tag_Interval_Id` FOREIGN KEY (`Interval_Id`) REFERENCES `Interval` (`Interval_Id`),
  CONSTRAINT `FK_Site_Msg_Tag_Unit_Id` FOREIGN KEY (`Unit_Id`) REFERENCES `Unit` (`Unit_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

I saw in this thread Will these two queries result in a deadlock if executed in sequence?

in that they solved the issue by writing the query in more efficient way any suggestions ? . Thanks in advance.

RolandoMySQLDBA · Answer 1 · 2013-03-27T20:07:50.713

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 !!!

How this simple Stored Procedure ended up in Deadlock

1 Answers1