Just made some additional testing... If you change ---- Conn2: create temporary table Temp select id, name from test where id = 3; ---- to ---- Conn2: insert into Temp select id, name from test where id = 3; ---- then it won't work too. But if you change it to ---- Conn2: select id, name from test where id = 3 with lock in share mode; ---- or ---- Conn2: select id, name from test where id = 3 with for update; ---- then everything works perfect...
Mikhail. ----- Original Message ----- From: "Mikhail Entaltsev" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, June 28, 2002 11:20 AM Subject: Re: InnoDB: Lock wait timeout problem. Please help. > 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 > --------------------------------------------------------------------- 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
