List:General Discussion« Previous MessageNext Message »
From:Walton Hoops Date:July 9 2009 7:12pm
Subject:Lock timeouts
View as plain text  
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
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
....

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

Thread
Lock timeoutsWalton Hoops9 Jul
  • RE: Lock timeoutsNathan Sullivan9 Jul