Description:
In MySQL 5.1, InnoDB when running in READ-COMMITTED uses less locking (and hence requires
binlog-format=row), such as releasing any row locks for not match rows that are read.
For more details, see:
http://harrison-fisk.blogspot.com/2009/02/my-favorite-new-feature-of-mysql-51.html
READ-UNCOMMITTED doesn't do this however, it still uses the higher locking.
READ-UNCOMMITTED should also use the lesser amount of locking.
How to repeat:
create table t1 (a int, b int, index (a), index (b)) ENGINE=innodb;
insert into t1 values (1, 1), (1, 2), (2, 1), (2, 2), (2, 3), (3, 3), (3, 1), (4, 1), (5,
10);
conn1> set session transaction isolation level repeatable read;
conn2> set session transaction isolation level repeatable read;
conn1> BEGIN;
conn2> BEGIN;
conn1> update t1 force index (a) set b = 10 where a = 1 and b =2;
-- this next update hangs due to locks
conn2> update t1 set b=0 where b = 1;
conn1> ROLLBACK;
conn2> ROLLBACK;
-- may need binlog_format=row if binlog enabled
conn1> set session transaction isolation level read committed;
conn2> set session transaction isolation level read committed;
conn1> BEGIN;
conn2> BEGIN;
conn1> update t1 force index (a) set b = 10 where a = 1 and b =2;
-- this does not hang, works immediately due to lower isolation level
conn2> update t1 set b=0 where b = 1;
conn1> ROLLBACK;
conn2> ROLLBACK;
conn1> set session transaction isolation level read uncommitted;
conn2> set session transaction isolation level read uncommitted;
conn1> BEGIN;
conn2> BEGIN;
conn1> update t1 force index (a) set b = 10 where a = 1 and b =2;
-- this hangs again, but really shouldn't
conn2> update t1 set b=0 where b = 1;
conn1> ROLLBACK;
conn2> ROLLBACK;
Suggested fix:
There are a lot of checks in InnoDB similar to:
if (srv_locks_unsafe_for_binlog || trx->isolation_level == TRX_ISO_READ_COMMITTED)
I suspect they should be switched to:
if (srv_locks_unsafe_for_binlog || trx->isolation_level <= TRX_ISO_READ_COMMITTED)
