MySQL Bugs: #48607: READ UNCOMMITTED uses more locks than READ COMMITTED in InnoDB 5.1+

Bug #48607	READ UNCOMMITTED uses more locks than READ COMMITTED in InnoDB 5.1+
Submitted:	6 Nov 2009 19:15	Modified:	20 Jun 2010 1:05
Reporter:	Harrison Fisk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:	Marko Mäkelä	CPU Architecture:	Any
Tags:	innodb_locks_unsafe_for_binlogs, read committed, read uncommited

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)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/106678

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/106679

This is a straightforward patch: whenever we relax locking with READ COMMITTED, we should do the same with READ UNCOMMITTED.

Please note that we advise against using READ UNCOMMITTED in a transactional database, as that can break transactional consistency, and it has not been tested extensively.

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:kristofer.pettersson@sun.com-20100503172109-f9hracq5pqsaomb1) (merge vers: 5.1.47) (pib:16)

Sorry, I forgot to update the bug status. This is already in 5.1.47.

Noted in 5.1.47 changelog.

In MySQL 5.1, READ COMMITTED was changed to use less locking due to the availability of row based binary logging (see the Note under READ COMMITTED at http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html ).  However, READ UNCOMMITTED did not have the same change, so it was using more locks than the higher isolation level which is unexpected.  This was changed so that READ UNCOMMITTED now also uses the lesser amount of locking and has the same restrictions for binary logging.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:alik@sun.com-20100512070920-xgpmqeytp0gc183c) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:alik@sun.com-20100507093037-7cykrx1n73v0tetc) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100507164602-8w09samq3mpvbxbn) (merge vers: 5.5.5-m3) (pib:16)

Noted in 5.5.5, 6.0.14 changelogs.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:martin.skold@mysql.com-20100616204905-jxjg342w35ks9vfy) (merge vers: 5.1.47-ndb-7.0.16) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100615090726-jotpykke96le59w5) (merge vers: 5.1.47-ndb-6.2.19) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:martin.skold@mysql.com-20100616120453-jh7wr05z1vf7r8pm) (merge vers: 5.1.47-ndb-6.3.35) (pib:16)