From: Peter Brawley
Date: August 31 2011 2:39pm
Subject: Re: locked non-existent row
List-Archive: http://lists.mysql.com/mysql/225624
Message-Id: <4E5E47B8.6090004@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

------