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/[email protected]