Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
For future reference, when you post a piece of code, select it in the text editor, and click on the curly braces icon (looks like this: { } ) in the toolbar. –  Jon Seigel Mar 27 '13 at 17:12
    
thanks , sorry for not making it more readable –  csf Mar 27 '13 at 17:14

1 Answer 1

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

share|improve this answer
    
Thanks for the quick response however , i still got the issue after modifying and following output from : show engine innodb status; –  csf Mar 28 '13 at 10:28
    
{ INSERT IGNORE INTO Site_Msg_Tag (Site_Msg_Tag_Id,Unit_Id,Log_Timestamp,Interval_Id,Digest_Matched)VALUES (NULL, NAME_CONST('siteId',254), NAME_CONST('logTimestamp',_latin1'2013-03-27 22:57:00' COLLATE 'latin1_swedish_ci'), NAME_CONST('IntervalId',1), NAME_CONST('digestMatched',NULL)) ------- TRX HAS BEEN WAITING 50 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 0 page no 108683 n bits 104 index PRIMARY of table g1_2_charts_212_new.Unit trx id 1805DC lock mode S locks rec but not gap waiting Record lock, heap no 19 PHYSICAL RECORD: n_fields 12; compact format; info bits 0 } –  csf Mar 28 '13 at 10:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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