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.
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`)
> )ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
> (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
> 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!
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
- michael dykman
- All models are wrong. Some models are useful.