Lock wait timeout

cyrilo · Post by **cyrilo** » Wed 11 Oct 2006 08:58

Hi
Sometimes my console application raise exception with message "#HY000Lock wait timeout exceeded; try restarting transaction"

Code: Select all

      
var SQL1  : TMyQuery;
...
      SQL1.SQL.Clear;
      SQL1.SQL.Add(' INSERT bla-bla-bla');
      try
        SQL1.Execute;
        SQL1.Connection.ApplyUpdates;
        except on E: EMyError do
            Log_Error('['+SessionId+'] '+ E.Message);
      end; {try}

I get this exception sometimes with SQL1.Connection.ApplyUpdates in code and without it.
MySQL tables is not locked by mysqldump or same programs.

I cant find problem resolution myself.
Please help!

Delphi 7.0, Kylix 3.0
MyDAC verison 4.30.0.11
MySQL version linux-3.23.58 (with InnoDB support) and win-4.1.18
Type of table storage engine is InnoDB.

Antaeus · Post by **Antaeus** » Wed 11 Oct 2006 12:36

The information you have provided is not enough to help you in solving this problem. Please supply us with some things:
1) full text of INSERT statement you try to execute;
2) script to create table(s) used in the SQL statement;
3) exact error message you get.

You should know that if you work in CachedUpdates mode, execution of INSERT statement using MyQuery will not be cached. You should use MyQuery.Insert instead.

cyrilo · Post by **cyrilo** » Wed 11 Oct 2006 13:31

1)

Code: Select all

SQL1.SQL.Add('INSERT INTO vcalls VALUES (');
      SQL1.SQL.Add('"'+SessionId+'",');          
      SQL1.SQL.Add('"'+Phone+'",');              
      SQL1.SQL.Add('"'+StrConnectTime+'",');     
      SQL1.SQL.Add('0,');                        
      SQL1.SQL.Add(IntToStr(CardId)+',');       
      SQL1.SQL.Add('0,');                        
      SQL1.SQL.Add(E1No+',');                    
      SQL1.SQL.Add(SlotNo+',');                 
      SQL1.SQL.Add('"'+DisconnectCause+'",');    
      SQL1.SQL.Add(IntToStr(VoiceQuality)+',');  
      SQL1.SQL.Add(IntToStr(NASId));             
      SQL1.SQL.Add(')');

2)

Code: Select all


CREATE DATABASE `radius`;

#
# Structure for the `cards` table : 
#

CREATE TABLE `cards` (
  `Id` smallint(6) unsigned NOT NULL auto_increment,
  `E1` tinyint(3) unsigned NOT NULL default '1',
  `Slot` tinyint(3) unsigned NOT NULL default '1',
  `Phone` char(12) default NULL,
  `Code` char(4) default NULL,
  `Seconds` int(10) unsigned NOT NULL default '0',
  `Funds` float unsigned NOT NULL default '0',
  `RestFunds` float NOT NULL default '0',
  `PlanId` smallint(5) unsigned NOT NULL default '1',
  `Valid` tinyint(1) NOT NULL default '1',
  `NormalCalls` bigint(20) unsigned NOT NULL default '0',
  `FailedCalls` bigint(20) unsigned NOT NULL default '0',
  `Comment` char(3) default NULL,
  PRIMARY KEY  (`Id`),
  UNIQUE KEY `PhoneNo` (`Phone`),
  KEY `SlotNo` (`Slot`),
  KEY `Seconds` (`Seconds`),
  KEY `Funds` (`Funds`),
  KEY `RestFunds` (`RestFunds`)
) TYPE=InnoDB;

#
# Structure for the `nases` table : 
#

CREATE TABLE `nases` (
  `Id` tinyint(3) unsigned NOT NULL auto_increment,
  `IP` varchar(15) NOT NULL default '',
  `Comments` varchar(20) default NULL,
  PRIMARY KEY  (`Id`),
  UNIQUE KEY `IP` (`IP`)
) TYPE=InnoDB;

#
# Structure for the `plans` table : 
#

CREATE TABLE `plans` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `Name` varchar(30) NOT NULL default '',
  `MoneyLimit` float(6,4) NOT NULL default '1.0000',
  `BonusMinutes` smallint(5) unsigned NOT NULL default '0',
  `ConnectRate` float(6,4) unsigned NOT NULL default '0.0000',
  `FirstMinuteRate` float(6,4) unsigned NOT NULL default '0.0000',
  `NextMinuteRate` float(6,4) unsigned NOT NULL default '0.0000',
  `SMSGate` varchar(30) NOT NULL default 'sms.kyivstar.net',
  PRIMARY KEY  (`id`,`Name`),
  UNIQUE KEY `Name` (`Name`)
) TYPE=InnoDB;

#
# Structure for the `vcalls` table : 
#

CREATE TABLE `vcalls` (
  `CiscoId` varchar(17) NOT NULL default '',
  `Tel` varchar(12) NOT NULL default '',
  `Start` datetime NOT NULL default '0000-00-00 00:00:00',
  `Duration` smallint(5) unsigned NOT NULL default '0',
  `CardId` smallint(5) unsigned NOT NULL default '0',
  `Price` float default NULL,
  `E1` tinyint(3) unsigned NOT NULL default '0',
  `Slot` tinyint(3) unsigned NOT NULL default '0',
  `Cause` char(2) default NULL,
  `Quality` tinyint(3) unsigned default NULL,
  `NasId` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`CiscoId`),
  UNIQUE KEY `CiscoId` (`CiscoId`),
  KEY `CardId` (`CardId`),
  KEY `E1` (`E1`),
  KEY `Slot` (`Slot`),
  KEY `Duration` (`Duration`),
  KEY `Price` (`Price`),
  KEY `Start` (`Start`),
  KEY `Tel` (`Tel`),
  KEY `NasId` (`NasId`),
  FOREIGN KEY (`CardId`) REFERENCES `radius.cards` (`Id`),
  FOREIGN KEY (`NasId`) REFERENCES `radius.nases` (`Id`)
) TYPE=InnoDB;

3)
in IDE I get message:
"#HY000Lock wait timeout exceeded; try restarting transaction"

outside IDE message is:
"Lock wait timeout exceeded; Try restarting transaction"

Antaeus · Post by **Antaeus** » Wed 11 Oct 2006 14:20

We could not reproduce the problem.
Please send us (evgeniyD*crlab*com) a complete small sample to demonstrate it, including script to fill tables.

cyrilo · Post by **cyrilo** » Thu 12 Oct 2006 10:53

email is sent

Antaeus · Post by **Antaeus** » Thu 12 Oct 2006 15:17

We still have not received it. Please send it one more time.