Mikhail, I think MySQL in this case waits for a MySQL table level lock. Note that CREATE TABLE ... SELECT ... sets shared locks on the rows it reads in the SELECTed table.
Workaround: use SELECT INTO OUTFILE + LOAD DATA INFILE In that way you can avoid locking altogether and do a consistent read. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB ----- Original Message ----- From: ""Mikhail Entaltsev"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Friday, June 28, 2002 1:29 AM Subject: InnoDB: Lock wait timeout problem. Please help. > Hi all, > > I am using MySQL (version 3.23.51-max-log). I have a problem with locking. > Let's assume that we have 2 connections (Conn1 and Conn2) and table test: > ------------------------------------------------------------ > CREATE TABLE `test` ( > `id` int(3) NOT NULL auto_increment, > `name` char(10) default '', > PRIMARY KEY (`id`) > ) TYPE=InnoDB; > ------------------------------------------------------------ > and some rows in it: > ------------------------------------------------------------ > insert into test (id, name) values (1, 'cat') > insert into test (id, name) values (2, 'dog') > insert into test (id, name) values (3, 'bird') > > ------------------------------------------------------------ > > I try to execute these queries in order: > ------------------------------------------------------------ > Conn1: begin; > Conn1: update test set name = 'rat' where id = 3; > > Conn2: set autocommit=1; > Conn2: create temporary table Temp select id, name from test where id = 3; > ------------------------------------------------------------ > After that Conn2 is locked. Then > > ------------------------------------------------------------ > Conn1: update test set name = 'rabbit' where id = 3; > ------------------------------------------------------------ > Conn1 is locked too! After 50 seconds Conn2 receive: "ERROR 1205: Lock wait > timeout exceeded; Try restarting transaction". > > Please, help me to resolve it. > Thanks in advance, > Mikhail. > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
