Heikki, Thank you very much for response. But I still don't understand why do I have lock in Conn1. Let's go through statement again.
> > Conn1: begin; > > Conn1: update test set name = 'rat' where id = 3; InnoDB docs: "UPDATE ... SET ... WHERE ... : sets an exclusive next-key lock on every record the search encounters." So InnoDB sets exclusive lock on index record. > > Conn2: set autocommit=1; > > Conn2: create temporary table Temp select id, name from test where id = 3; InnoDB docs: "INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive (non-next-key) lock on each row inserted into T. Does the search on S as a consistent read, but sets shared next-key locks on S if the MySQL logging is on... CREATE TABLE ... SELECT ... performs the SELECT as a consistent read or with shared locks, like in the previous item." InnoDB docs: "Performing a read in share mode means that we read the latest available data, and set a shared mode lock on the rows we read. If the latest data belongs to a yet uncommitted transaction of another user, we will wait until that transaction commits. A shared mode lock prevents others from updating or deleting the row we have read." So it means that Conn2 will wait until Conn1 transaction commits (or rollbacks). And it is truth - Conn2 is locked. After that: > > Conn1: update test set name = 'rabbit' where id = 3; And Conn1 is locked too! I can't understand - why??? It belongs to the same transaction that made first update (update test set name = 'rat' where id = 3). Unfortunately, I can't use > SELECT INTO OUTFILE > + > LOAD DATA INFILE for some reasons. But anyway, thank you very much for your ideas and help. I appreciate it very much. Mikhail. ----- Original Message ----- From: "Heikki Tuuri" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, June 28, 2002 10:42 AM Subject: Re: InnoDB: Lock wait timeout problem. Please help. > 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 > --------------------------------------------------------------------- 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
