List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:December 21 2006 4:05pm
Subject:Re: Why innodb can give the same X gap lock to two transactions?
View as plain text  

Leo Huang wrote:
> Heikki,
> Thanks for you help!
> I also read the comment in file of innodbase/lock/lock0lock.c in which
> you said "Different transaction can have conflicting locks set on the
> gap at the same time.". I think that the innodb gap lock's behavior
> just like an IX lock's behavior.

hmm... yes, we could think that when a row is inserted, the inserted 
needs an 'X-lock on the whole gap'! Then the 'IX-locks' on the gap stop 
the insertion.

> When a transaction want to insert a
> record, it must also get the LOCK_INSERT_INTENTION of the gap, isn't
> it?

Yes, but in the above analogy, an insert is really requesting an 'X lock 
on the whole gap'. The name LOCK_INSERT_INTENTION is then somewhat 

> I have read some source code in innodbase/lock/lock0lock.c. But I
> can't get a clear view of innodb lock modes and lock ways?  Can you
> give me more information?

On tables, InnoDB has X, S, IX, IS, and AUTO-INC type locks.

InnoDB has basically just X and S type locks on records and gaps. The 
complexity comes from this:

lock0lock.h in 5.0:

#define LOCK_ORDINARY   0       /* this flag denotes an ordinary 
next-key lock
                                 in contrast to LOCK_GAP or 
#define LOCK_GAP        512     /* this gap bit should be so high that
                                 it can be ORed to the other flags;
                                 when this bit is set, it means that the
                                 lock holds only on the gap before the 
                                 for instance, an x-lock on the gap does not
                                 give permission to modify the record on 
                                 the bit is set; locks of this type are 
                                 when records are removed from the index 
                                 of records */
#define LOCK_REC_NOT_GAP 1024   /* this bit means that the lock is only on
                                 the index record and does NOT block inserts
                                 to the gap before the index record; this is
                                 used in the case when we retrieve a record
                                 with a unique key, and is also used in
                                 locking plain SELECTs (not part of UPDATE
                                 or DELETE) when the user has set the READ
                                 COMMITTED isolation level */
#define LOCK_INSERT_INTENTION 2048 /* this bit is set when we place a 
                                 gap type record lock request in order 
to let
                                 an insert of an index record to wait until
                                 there are no conflicting locks by other
                                 transactions on the gap; note that this 
                                 remains set when the waiting lock is 
                                 or if the lock is inherited to a 
                                 record */

Unfortunately, the only existing documentation of the details of gap 
locking is in the source code and comments in lock0lock.c.

> PS: hi, Eric, Our MySQL version is 4.1.18. Thx!


Why innodb can give the same X gap lock to two transactions?leo huang12 Dec
  • Re: Why innodb can give the same X gap lock to two transactions?Eric Bergen17 Dec
Re: Why innodb can give the same X gap lock to two transactions?Heikki Tuuri18 Dec
  • Re: Why innodb can give the same X gap lock to two transactions?Leo Huang19 Dec
    • Re: Why innodb can give the same X gap lock to two transactions?Heikki Tuuri21 Dec