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
------
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
