Problem with InnoDB locks

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.


Send instant messages to your online friends http://uk.messenger.yahoo.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Problem with InnoDB locks

At 13:54 14/3/2005, K.V.Suresh wrote:
Dear Suresh,

Could you please file a bug report 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=3DInnoDB
>
>create table test1 (ID INTEGER NOT NULL , ID1 INTEGER
>NOT NULL,
>PRIMARY KEY(ID,ID1),
>FOREIGN KEY(ID) REFERENCES TEST(ID))
>type =3D 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=3D'ghi' where ID=3D2;
>
>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.
>
>
>Send instant messages to your online friends http://uk.messenger.yahoo.com
>
>--
>MySQL Bugs Mailing List
>For list archives: http://lists.mysql.com/bugs
>To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dmiguel@mysql.com

Regards,

Miguel Angel Sol=F3rzano
S=E3o Paulo - Brazil



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org

Re: Problem with InnoDB locks

Hi,

I don't believe the following is a bug. I will explain the behavior
below.

On Mar 14, 2005, at 3:35 PM, Miguel Angel Solorzano wrote:

> At 13:54 14/3/2005, K.V.Suresh wrote:
> Dear Suresh,
>
> Could you please file a bug report 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);

When you perform this insert you set a shared row level lock the parent
row in table 'test' because of the FOREIGN KEY constraint. The reason
this has to occur is to be able to guarantee that the row is still
present in the parent table when you do a commit statement. I believe
this behavior is fairly standard with most DBMS that don't do
deferrable constraints, as I know that PostgreSQL does the same thing
(but with an exclusive row level lock on the parent).

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

Now you are trying to update the parent row which requires an exclusive
lock. The other transaction has a shared-lock so you can imagine it
has to block and wait. InnoDB doesn't do column level locking (nor do
any other database engines) so it doesn't know that you are only
changing the column which doesn't matter due to the previous lock.

>>
>> 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.

I hope that helps to explain the behavior you saw and why it is occurs.

Regards,

Harrison

--
Harrison C. Fisk, Trainer and Consultant
MySQL AB, www.mysql.com

Get a jumpstart on MySQL Cluster --
http://www.mysql.com/consulting/packaged/cluster.html


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Problem with InnoDB locks

Dear Harrison,

Thanks a lot for your detailed explanation. I now
understand (and appreciate!) this behavior of InnoDB
engine. In fact, I should've read the "Locks Set by
Different SQL Statements in InnoDB" document before
sending this post! This clearly states the following:
....
If a FOREIGN KEY constraint is defined on a table,
any insert, update, or delete that requires checking
of the constraint condition sets shared record-level
locks on the records it looks at to check the
constraint. InnoDB also sets these locks in the case
where the constraint fails.
....
http://dev.mysql.com/doc/mysql/en/innodb-locks-set.html

Thanks,
Suresh.K.V.

--- Harrison Fisk wrote:
> Hi,
>
> I don't believe the following is a bug. I will
> explain the behavior
> below.
>
> On Mar 14, 2005, at 3:35 PM, Miguel Angel Solorzano
> wrote:
>
> > At 13:54 14/3/2005, K.V.Suresh wrote:
> > Dear Suresh,
> >
> > Could you please file a bug report 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);
>
> When you perform this insert you set a shared row
> level lock the parent
> row in table 'test' because of the FOREIGN KEY
> constraint. The reason
> this has to occur is to be able to guarantee that
> the row is still
> present in the parent table when you do a commit
> statement. I believe
> this behavior is fairly standard with most DBMS that
> don't do
> deferrable constraints, as I know that PostgreSQL
> does the same thing
> (but with an exclusive row level lock on the
> parent).
>
> >>
> >> and another transaction (say T2):
> >> update test set name='ghi' where ID=2;
>
> Now you are trying to update the parent row which
> requires an exclusive
> lock. The other transaction has a shared-lock so
> you can imagine it
> has to block and wait. InnoDB doesn't do column
> level locking (nor do
> any other database engines) so it doesn't know that
> you are only
> changing the column which doesn't matter due to the
> previous lock.
>
> >>
> >> 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.
>
> I hope that helps to explain the behavior you saw
> and why it is occurs.
>
> Regards,
>
> Harrison
>
> --
> Harrison C. Fisk, Trainer and Consultant
> MySQL AB, www.mysql.com
>
> Get a jumpstart on MySQL Cluster --
>
http://www.mysql.com/consulting/packaged/cluster.html
>
>
> --
> MySQL Bugs Mailing List
> For list archives: http://lists.mysql.com/bugs
> To unsubscribe:
>
http://lists.mysql.com/bugs?unsub=sureshvembu@yahoo.com
>
>

Send instant messages to your online friends http://uk.messenger.yahoo.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org