MySQLTalk.org

Miguel Angel Solorzano · Guest

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,

Miguel Angel Solórzano <miguel (AT) mysql (DOT) com>
Florianopolis Santa Catarina - Brazil