List:General Discussion« Previous MessageNext Message »
From:Walton Hoops Date:July 9 2009 7:55pm
Subject:FW: Lock timeouts
View as plain text  
Forwarded response from Micheal.  I haven't tested it yet (have to wait till
I have more data to move), but this sounds about right.


-----Original Message-----
From: Michael Dykman [mailto:mdykman@stripped] 
Sent: Thursday, July 09, 2009 1:22 PM
To: Walton Hoops
Subject: Re: Lock timeouts

You have no index for 'Weekend' which means that InnoDB can't just
lock the appropriate rows..  it' does not know which rows will be
affected until it has touched ALL of them.  Try creating an index on
'Weekends'..  this should help you avoid those locks.

 - michael dykman

On Thu, Jul 9, 2009 at 3:12 PM, Walton Hoops<walton@stripped> wrote:
> Oh wise and all knowing MySQL Guru's:
> I've been running into a problem recently that has be confused.
> I have 2 tables, both with the structure:
> DROP TABLE IF EXISTS `acn_market_level`.`market_scans`;
> CREATE TABLE  `acn_market_level`.`market_scans` (
> `Retailer` char(3) NOT NULL,
> `Marketkey` int(11) NOT NULL,
> `UPCKEY` bigint(20) NOT NULL DEFAULT '0',
> `Weekend` int(10) unsigned NOT NULL,
> `Dollars` decimal(17,2) DEFAULT NULL,
> `Units` bigint(20) unsigned DEFAULT NULL,
> `PctAcv` float unsigned DEFAULT NULL,
> `SPMD` float unsigned DEFAULT NULL,
> `PromoDollars` float unsigned DEFAULT NULL,
> PRIMARY KEY (`Retailer`,`Marketkey`,`UPCKEY`,`Weekend`)
> (downloads.market_scans is identical)
> I have a long running query (ok 10 minutes):
> insert into acn_market_level.market_scans
> select * from downloads.market_scans
> where weekend > 20090613
> While this query is running I am also trying to insert rows into
> downloads.market_scans, all of them with a weekend < 20090613, but I keep
> seeing this error:
> Lock wait timeout exceeded; try restarting transaction
> com.mysql.jdbc.SQLError.createSQLException(
> com.mysql.jdbc.SQLError.createSQLException(
> ....
> If I understand this right, the select statement is preventing the insert
> statement from running, but isn't that exactly what InnoDB's row level
> locking is supposed to prevent?  It's not that big a deal, but I feel like
> I'm missing something here.
> Thanks in advance!
> Walton
> --
> MySQL General Mailing List
> For list archives:
> To unsubscribe:

 - michael dykman
 - mdykman@stripped

 - All models are wrong.  Some models are useful.

FW: Lock timeoutsWalton Hoops9 Jul
  • Re: FW: Lock timeoutsPerrin Harkins9 Jul