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
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[6 Nov 2009 19:15] Harrison Fisk
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)
[27 Apr 2010 13:55] Bugs System
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
[27 Apr 2010 13:55] Bugs System
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
[29 Apr 2010 12:59] Heikki Tuuri
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.
[5 May 2010 15:02] Bugs System
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)
[14 May 2010 6:29] Marko Mäkelä
Sorry, I forgot to update the bug status. This is already in 5.1.47.
[20 May 2010 18:25] Paul Dubois
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.
[28 May 2010 5:53] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:alik@sun.com-20100512070920-xgpmqeytp0gc183c) (pib:16)
[28 May 2010 6:22] Bugs System
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)
[28 May 2010 6:50] Bugs System
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)
[29 May 2010 23:10] Paul Dubois
Noted in 5.5.5, 6.0.14 changelogs.
[17 Jun 2010 11:53] Bugs System
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)
[17 Jun 2010 12:31] Bugs System
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)
[17 Jun 2010 13:19] Bugs System
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)