List:General Discussion« Previous MessageNext Message »
From:Rik Wasmus | GRIB Date:September 1 2011 6:58am
Subject:Re: locked non-existent row
View as plain text  
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
Thread
locked non-existent rowPeter Brawley31 Aug
  • Re: locked non-existent rowRik Wasmus31 Aug
    • Re: locked non-existent rowPeter Brawley31 Aug
      • Re: locked non-existent rowRik Wasmus | GRIB1 Sep
      • RE: locked non-existent rowJerry Schwartz1 Sep
        • Re: locked non-existent rowRik Wasmus1 Sep
          • Re: locked non-existent rowPeter Brawley1 Sep
          • RE: locked non-existent rowJerry Schwartz1 Sep
        • Re: locked non-existent rowPeter Brawley2 Sep
  • Re: locked non-existent rowJochem van Dieten2 Sep
    • Re: locked non-existent rowPeter Brawley2 Sep