Hello,

I was able for to repeat the below behavior reported for you, however at first glance looks to me was already opened a bug. Did you opened a bug at:

http://bugs.mysql.com

Thanks in advance

Hi,

I'm running MySQL 4.0.20 in Windows and face the following problem with InnoDB.

First create two tables CREATE TABLE test ( ID INTEGER NOT NULL, name varchar(100) , PRIMARY KEY (ID) ) TYPE=InnoDB

create table test1 (ID INTEGER NOT NULL , ID1 INTEGER NOT NULL, PRIMARY KEY(ID,ID1), FOREIGN KEY(ID) REFERENCES TEST(ID)) type = InnoDB;

insert into test values (1,'abc'); insert into test values (2,'def');

insert into test1 values (1,1); insert into test1 values (2,1);

commit;

now from one Transaction (say T1) do the following

insert into test1 values(2,3);

and another transaction (say T2): update test set name='ghi' where ID=2;

The T2 is waiting on lock! I don't really understand this behavior. The two transaction are involving different tables and the index column is not 'updated' in T2, so why this lock? Can someone please throw some light on this behavior? or is this a bug in the InnoDB locking mechanism?

Regards, Suresh.K.V.

Regards,