How to deal with 'Lock wait timeout exceeded; try restarting transaction' in MySQL?

0
votes

My MySQL version is Server version: 5.1.51-ndb-7.1.9a-cluster-com-log MySQL Cluster Server (Commercial)

I have an update query which results in 'Lock wait timeout exceeded; try restarting transaction'. The table create statement is:

CREATE TABLE  `NGMP_OLTP`.`MT_Routing_Info` (  
  `Message_Id` varchar(70) CHARACTER SET latin1 NOT NULL,  
  `Route_Id` int(10) DEFAULT NULL,
  `Spf_Short_Code` varchar(32) CHARACTER SET latin1 DEFAULT NULL,
  `Network_Search_Option` smallint(5) DEFAULT NULL,
  `Priority` int(10) DEFAULT NULL,
  `Charge_Code` varchar(64) CHARACTER SET latin1 DEFAULT NULL,
  `Barred` varchar(1) CHARACTER SET latin1 DEFAULT NULL,
  `Dynamic_Spoofing` varchar(1) CHARACTER SET latin1 DEFAULT NULL,
  `AV_Required` varchar(1) CHARACTER SET latin1 DEFAULT NULL,
  `AV_Status` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
  `Price_Point` int(10) DEFAULT NULL,
  `NW_Connector_Id` int(10) DEFAULT NULL,
  `Sent_Time` datetime DEFAULT NULL,
  `Operator_Id` int(10) DEFAULT NULL,
  `Short_Code` varchar(15) CHARACTER SET utf8 DEFAULT NULL,
  `Service_Id` int(10) DEFAULT NULL,
  `SMPP_BIND_ID` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `Frgtotal` int(11) DEFAULT '0',
  PRIMARY KEY (`Message_Id`),
  KEY `IX_MT_Routing_Info` (`Message_Id`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC;

The stored procedure which is doing the updation is:

DELIMITER $$
DROP PROCEDURE IF EXISTS `NGMP_OLTP`.`usp_UpdateMTRouting_Info` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_UpdateMTRouting_Info`(
    msgid       varchar(50),
    senttime    varchar(30),
    routeid varchar(32),    
    operid      varchar(32),    
    scode       nvarchar(15),
    spfsc       varchar(32),
    nwopt       varchar(16),    
    prty        varchar(32),    
    ccode       varchar(64),
    barred      varchar(1),
    dynspf      varchar(1),
    svcid       varchar(32),    
    prpt        varchar(32),    
    avreqd      varchar(1),
    avstat      varchar(20),
    nwconid varchar(32),    
    tsstr       varchar(2000),
    isfinalstr  varchar(200),
    idstr       varchar(200)  ,
  bindid varchar(100)

)
BEGIN
  if routeid = '' then set routeid = null; end if;
  if operid = '' then set operid = null; end if;
  if nwopt = '' then set nwopt = null; end if;
  if prty = '' then set prty = null; end if;
  if svcid = '' then set svcid = null; end if;
  if prpt = '' then set prpt = null; end if;
  if nwconid = '' then set nwconid = null; end if;
  if bindid = '' then set bindid = null; end if;
    UPDATE MT_Routing_Info SET
    Route_Id = routeid,
    Spf_Short_Code = spfsc,
    Network_Search_Option = nwopt,
    Priority = prty,
    Charge_Code = ccode,
    Barred = barred,
    Dynamic_Spoofing = dynspf,
    AV_Required = avreqd,
    AV_Status = avstat,
    Price_Point = prpt,
    NW_Connector_Id = nwconid,
    Sent_Time=senttime,
    Operator_Id = operid,
    Short_Code = scode,
    Service_Id = svcid,
    SMPP_BIND_ID=bindid  
    WHERE Message_Id = msgid;
    Call usp_SaveMsgProcStatus (msgid, tsstr, ',', isfinalstr, ',', idstr, ',');
    END $$
    DELIMITER ;

In the MT_Routing_Info table I have an index on Message_Id column. show engine ndb status 

    Type            Name            Status
    ndbcluster  connection  cluster_node_id=5, connected_host=10.5.20.46, 
    connected_port=1186, number_of_data_nodes=2, number_of_ready_data_nodes=2, connect_count=0
    ndbcluster  NdbTransaction  created=2, free=2, sizeof=224
    ndbcluster  NdbOperation    created=4, free=4, sizeof=756
    ndbcluster  NdbIndexScanOperation   created=0, free=0, sizeof=888
    ndbcluster  NdbIndexOperation   created=0, free=0, sizeof=760
    ndbcluster  NdbRecAttr  created=0, free=0, sizeof=64
    ndbcluster  NdbApiSignal    created=16, free=16, sizeof=136
    ndbcluster  NdbLabel    created=0, free=0, sizeof=196
    ndbcluster  NdbBranch   created=0, free=0, sizeof=24
    ndbcluster  NdbSubroutine   created=0, free=0, sizeof=68
    ndbcluster  NdbCall created=0, free=0, sizeof=16
    ndbcluster  NdbBlob created=0, free=0, sizeof=344
    ndbcluster  NdbReceiver created=0, free=0, sizeof=92
    ndbcluster  NdbLockHandle   created=0, free=0, sizeof=32
    ndbcluster  binlog  latest_epoch=1412576088948745, latest_trans_epoch=1412541729210383, latest_received_binlog_epoch=0, latest_handled_binlog_epoch=0, latest_applied_binlog_epoch=0

I also tried increasing time-out values: TransactionDeadlockDetectionTimeout=10000 HeartbeatIntervalDbDb=3000 HeartbeatIntervalDbApi=3000 At the best I can drop the index. The table MT_Routing_Info had 695563 rows when the error started occurring. How can I get rid of this?

1
mysql

1 Answers