List:General Discussion« Previous MessageNext Message »
From:Rik Wasmus Date:August 31 2011 9:50am
Subject:Re: locked non-existent row
View as plain text  
> 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."
-- 
Rik Wasmus
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