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 ;
| 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.