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

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