Justin, ----- Alkuperäinen viesti ----- Lähettäjä: "Justin Swanhart" <[EMAIL PROTECTED]> Vastaanottaja: "Sasha Pachev" <[EMAIL PROTECTED]> Kopio: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Lähetetty: Friday, May 14, 2004 9:22 PM Aihe: Re: unexpected create table as lock issue
> > --- Sasha Pachev <[EMAIL PROTECTED]> wrote: > > [EMAIL PROTECTED] wrote: > > > I have a table called web_master which has as a > > > primary key column called web_id. > > > > > > When I try to create another table as a select, I > > get > > > the following error message [note, that group_id > > is > > > indexed]: > > > > > > mysql> create table sort_test as select * from > > > web_master where group_id = 69; > > > ERROR 1205: Lock wait timeout exceeded; Try > > > restarting transaction > > > > > > > > > There aren't any other transactions running in the > > > database, so I do not understand why there is any > > lock > > > wait.. > > > > Very possibly a bug. INNODB deadlocks itself during > > the operation, then detects > > the deadlock and aborts the transaction. If this is > > indeed a bug, I am sure > > Heikki would like to fix it ASAP. > > > > > > -- > > Sasha Pachev > > Create online surveys at http://www.surveyz.com/ > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > There actually was a "create index" that was being > executed on the web_master table. > > Still, that shouldn't result in a deadlock creating > the other table should it? DDL against the web_master > table of course should lock timeout because of the > index creation, but you should be able to create > another table based off that table with no problem, > since you can obviously get a consistent read off the > base table. in CREATE TABLE t SELECT * FROM s; if you have the MySQL binlogging enabled, then InnoDB locks all the rows in s with shared locks. InnoDB cannot use a consistent read, because the binlog would not know what snapshot of s has been read to produce t. Once we have the 'row-level binlogging' implemented in MySQL, we can relax this and always use a consistent, non-locking read of s. > Should I create a simple test scenario and post it to > the bug list? If I understood this right this is not a bug. Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
