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

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

PB