Bug #9183 Lock Handling in InnoDB

• Submitted: 15 Mar 2005 4:23
• Modified: 15 Mar 2005 7:30
• Reporter: Suresh KV
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 4.0.20
• OS: Windows (Windows 2000)
• CPU Architecture: Any

[15 Mar 2005 4:23] Suresh KV

Description:
I'm witnessing a peculiar locking (mis)behavior with the InnoDB tables. Please see the 'How to repeat' for the complete scenario description.

How to repeat:
First create two tables
CREATE TABLE `test` (
  `ID` int(11) NOT NULL default '0',
  `name` varchar(100) default NULL,
  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?

[15 Mar 2005 7:30] Heikki Tuuri

Hi!

The FOREIGN KEY check has locked the parent row with an S-lock. The UPDATE would need an X-lock on the row to update it. That is why the UPDATE is waiting.

The UPDATE could proceed if the locking granularity of InnoDB were column-level, but it is row-level.

Regards,

Heikki