How this simple Stored Procedure ended up in Deadlock

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 Un

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

StackExchange Database Administrators Q#38694, answer score: 2