Nope, Nathan is right: INSERT...SELECT is a locking statement due to statement-based replication. There's lots of info on this if you google it. The common solution is to dump to disk and load back in with SELECT INTO OUTFILE and LOAD DATA INFILE.
- Perrin On Thu, Jul 9, 2009 at 3:55 PM, Walton Hoops<wal...@vyper.hopto.org> wrote: > 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. > > Thanks! > > -----Original Message----- > From: Michael Dykman [mailto:mdyk...@gmail.com] > 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<wal...@vyper.hopto.org> 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 >> 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 >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com >> >> > > > > -- > - michael dykman > - mdyk...@gmail.com > > - All models are wrong. Some models are useful. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=per...@elem.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
