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