On Wednesday 31 August 2011 16:39:52 Peter Brawley wrote:
> >> While a transaction in one thread tries to update a non-existent InnoDB
> >> row with a given key value, an attempt to insert that value in another
> >> thread is locked out. Does anyone know where this behaviour is
> >> documented?
> >>
> >> -- connection 1
> >> drop table if exists t;
> >> create table t( lockid char(3), lockinfo char(8), primary
> >> key(lockid,lockinfo) );
> >> insert into t values('abc','def');
> >> begin work;
> >> update t set lockinfo='bar' where lockid='foo';
> >>
> >> -- connection 2:
> >> insert into t values('aaa','bbb');
> >> Query OK, 1 row affected (0.00 sec)
> >> insert into t values('foo','bar'); -- waits for connection 1 transaction
> >
> > It has to do with transaction isolation levels. I assume REPEATABLE READ
> > by default for InnoDB:
> >
> > http://dev.mysql.com/doc/refman/5.0/en/set-
> > transaction.html#isolevel_repeatable-read
> >
> > " For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE),
> > UPDATE, and DELETE statements, locking depends on whether the statement
> > uses a unique index with a unique search condition, or a range-type
> > search condition. For a unique index with a unique search condition,
> > InnoDB locks only the index record found, not the gap before it. For
> > other search conditions, InnoDB locks the index range scanned, using gap
> > locks or next-key (gap plus index-record) locks to block insertions by
> > other sessions into the gaps covered by the range."
>
> Yes, that's what the question is about, it says for a unique key value,
> as in this case, it "locks only the index record found". There is no
> index record. InnoDb behaves, though, as if there is one. Where is the
> documentation for that?
Is says it employs different strategies, I see it as:
(unique index with a unique search condition => InnoDB locks only the index
record found)
OR
(other search conditions => InnoDB locks the index range scanned, using gap
locks or next-key )
And 'lockinfo' is most certainly in your index, a primary key is a special
kind of UNIQUE, but still unique. The INSERT cannot complete as the
(lockid,lockinfo) location (foo,bar) is locked, until commit or rollback. See
the index as a seperate storage from your normale table-rows which your INSERT
needs to put something in to complete the insert. The UPDATE has locked that
location, so the INSERT waits for that. That's the 'index record' they're
talking about I gather.
Also: please respond to the list only, I have enough mail as it is, responding
to the list keeps things nicely organized :)
--
Rik
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
