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
