Bug #41671 Semi-consistent read does not unlock rows in READ COMMITTED mode

• Submitted: 22 Dec 2008 12:03
• Modified: 20 Jun 2010 1:01
• Reporter: Marko Mäkelä
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.1
• OS: Any
• CPU Architecture: Any

[22 Dec 2008 12:03] Marko Mäkelä

Description:
In revision 370 of the InnoDB source repository (2006-03-27), every occurrence of
srv_locks_unsafe_for_binlog
should have been essentially replaced with
srv_locks_unsafe_for_binlog || isolation_level == TRX_ISO_READ_COMMITTED

That is, semi-consistent reads should be enabled with either of the following:

set global innodb_locks_unsafe_for_binlog=1;
set session transaction isolation level read committed;

However, this is not the case in ha_innobase::unlock_row(void).  The bug is that non-matching semi-consistently read rows will not be unlocked when both of the above commands have been specified.

How to repeat:
Note in innodb-semi-consistent.test that the SELECT statements are obviously not unlocking non-matching rows.

Suggested fix:
Index: handler/ha_innodb.cc
===================================================================
--- handler/ha_innodb.cc	(revision 3590)
+++ handler/ha_innodb.cc	(working copy)
@@ -4129,7 +4129,8 @@ ha_innobase::unlock_row(void)
 	switch (prebuilt->row_read_type) {
 	case ROW_READ_WITH_LOCKS:
 		if (!srv_locks_unsafe_for_binlog
-		|| prebuilt->trx->isolation_level == TRX_ISO_READ_COMMITTED) {
+		    && prebuilt->trx->isolation_level
+		    != TRX_ISO_READ_COMMITTED) {
 			break;
 		}
 		/* fall through */

[22 Dec 2008 19:16] Marko Mäkelä

Whoops, the bug title should have been READ COMMITTED, not READ UNCOMMITTED.

[13 Jan 2009 22:14] 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/63184

2742 Timothy Smith	2009-01-13
      Apply test case changes for Bug #41671 (innodb-semi-consistent.test) also to
      partition_innodb_semi_consistent.test, which was overlooked in the
      innodb-5.1-ss3603 snapshot.

[20 Jan 2009 19:00] Bugs System

Pushed into 6.0.10-alpha (revid:[e-mail]-20090119171328-2hemf2ndc1dxl0et) (version source revid:[e-mail]-20090113225329-ipqz6q1gh3teuebm) (merge vers: 6.0.10-alpha) (pib:6)

[26 Jan 2009 16:47] Vasil Dimov

Restore state to "Patch queued" because the bugs system wrongly updated it to "Documenting" (see [13 Jan 23:14] Bugs System) because. The change that was committed there is not the fix for this bug.

[3 Feb 2009 9:41] Bugs System

Pushed into 5.1.32 (revid:[e-mail]-20090203090549-gos3v4320vimrzg6) (version source revid:[e-mail]-20090123182108-2efo5zwexcrf0h3o) (merge vers: 5.1.32) (pib:6)

[17 Feb 2009 15:00] Bugs System

Pushed into 5.1.32-ndb-6.3.23 (revid:[e-mail]-20090217131017-6u8qz1edkjfiobef) (version source revid:[e-mail]-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

[17 Feb 2009 16:48] Bugs System

Pushed into 5.1.32-ndb-6.4.3 (revid:[e-mail]-20090217134419-5ha6xg4dpedrbmau) (version source revid:[e-mail]-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

[17 Feb 2009 18:24] Bugs System

Pushed into 5.1.32-ndb-6.2.17 (revid:[e-mail]-20090217134216-5699eq74ws4oxa0j) (version source revid:[e-mail]-20090202111723-1zzwax187rtls913) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)

[26 Feb 2009 18:54] Paul DuBois

Noted in 5.1.32, 6.0.10 changelogs.

Setting innodb_locks_unsafe_for_binlog should be equivalent to
setting the transaction isolation level to READ COMMITTED. However,
if both of those things were done, non-matching semi-consistently
read rows were not unlocked when they should have been.

[5 May 2010 15:13] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 16:58] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[28 May 2010 5:58] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:27] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 6:55] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[29 May 2010 23:07] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[15 Jun 2010 8:22] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100615080459-smuswd9ooeywcxuc) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

[15 Jun 2010 8:39] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615080558-cw01bzdqr1bdmmec) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (pib:16)

[17 Jun 2010 12:01] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:42] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:28] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)