Leo Huang wrote:
> 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
> When a transaction want to insert a
> record, it must also get the LOCK_INSERT_INTENTION of the gap, isn't
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
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
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
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!