List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 31 2011 2:39pm
Subject:Re: locked non-existent row
View as plain text  
On 8/31/2011 4:50 AM, Rik Wasmus 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?

PB

------

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