Bug #21409 Incorrect result returned when in READ-COMMITTED with query_cache ON

• Submitted: 2 Aug 2006 4:43
• Modified: 19 Jun 2010 0:08
• Reporter: Morgan Tocker
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 4.1,.5.0,5.1
• OS: Any (ALL)
• Assigned to: Sunny Bains
• CPU Architecture: Any

[2 Aug 2006 4:43] Morgan Tocker

Description:
Verified in 4.1.15, 5.0.21, 5.0.23, 5.1.11-beta; when running in READ-COMMITTED, the results returned are not always as expected.

How to repeat:
Steps to repeat:

1) == connection #1
2) == connection #2

1) set autocommit=0;
2) set autocommit=0;
1) CREATE TABLE foo ( q_id int) ENGINE=InnoDB;
1) CREATE TABLE foo2 ( q_id int) ENGINE=InnoDB;
1) select * from foo2;
2) INSERT INTO foo values (1);
2) commit;
1) select * from foo where q_id=1; # this correctly returns one row

disconnect both connections then reconnect again

1) set autocommit=0;
2) set autocommit=0;
1) select * from foo2;
2) INSERT INTO foo values (2);
2) commit;
1) select * from foo where q_id=2; # incorrectly returns no rows
1) select * from foo where q_id=2; # just re-issuing the query then returns the correct result

[2 Aug 2006 7:01] Heikki Tuuri

Morgan,

how do you set the isolation level to READ COMMITTED?

--Heikki

[2 Aug 2006 7:49] Morgan Tocker

> how do you set the isolation level to READ COMMITTED?

In my.cnf

[2 Aug 2006 8:09] Heikki Tuuri

Morgan,

please show the exact line.

--Heikki

[2 Aug 2006 11:45] Morgan Tocker

# The MySQL server
[mysqld]
core-file
transaction-isolation = READ-COMMITTED

The testcase is quite easy to reproduce, but it seems every step needs to be followed in order.

[9 Aug 2006 1:33] Morgan Tocker

Anybody else able to verify this?  Seemed quite easy to create from the test case.

[9 Aug 2006 12:48] Heikki Tuuri

Morgan,

I will try to look at this today. I am working to get the number of 'active' non-fixed InnoDB bug reports back under 25. Currently, it is 30. There is some backlog.

Regards,

Heikki

[10 Aug 2006 7:32] Morgan Tocker

Verified under 5.0.24 and...

mysql1> select version();
+-----------------------------+
| version()                   |
+-----------------------------+
| 5.0.25-nightly-20060803-log |
+-----------------------------+

Anything else that it could be?

My operating system is Ubuntu Dapper if it helps.  Hardware is Intel x86 (Intel(R) Pentium(R) M processor 1.50GHz)

[10 Aug 2006 19:37] Dean Ellis

I can repeat the behavior, but so far only on 5.1.12-beta-bk.  Sometimes repeating the statement causes the row to become visible, sometimes not.

If this is not a bug, we need to add some more information to our documentation regarding READ-COMMITTED.

I used this test case:

connect (c1,localhost,root,,);
connect (c2,localhost,root,,);
connection c1;
set tx_isolation='read-committed';
set autocommit=0;
drop table if exists t1, t2;
create table t1 ( a int ) engine=innodb;
create table t2 like t1;
select * from t2;
connection c2;
set tx_isolation='read-committed';
set autocommit=0;
insert into t1 values (1);
commit;
connection c1;
select * from t1 where a=1;
disconnect c1;
disconnect c2;
connect (c1,localhost,root,,);
connect (c2,localhost,root,,);
connection c1;
set tx_isolation='read-committed';
set autocommit=0;
select * from t2;
connection c2;
set tx_isolation='read-committed';
set autocommit=0;
insert into t1 values (2);
commit;
connection c1;
select * from t1 where a=2;
select * from t1 where a=2;

With output one time of:
...
select * from t1 where a=2;
a
select * from t1 where a=2;
a
2
ok 

And another:
...
select * from t1 where a=2;
a
select * from t1 where a=2;
a
ok

So it is not necessarily consistent.

[10 Aug 2006 20:54] Heikki Tuuri

Dean,

it might be that the 5.1 patch of http://bugs.mysql.com/bug.php?id=20213 fixes also this bug, if this actually is memory corruption.

Can you edit ::store_lock() in ha_innodb.cc and test if you still can repeat?

Regards,

Heikki

[14 Aug 2006 17:04] Dean Ellis

Heikki,

Yes, modifying ha_innobase::store_lock() seems to correct this in 5.1.

[31 Aug 2006 3:41] Morgan Tocker

An update to the testcase; It requires the query cache On. If all of the select queries in the test case has SQL_NO_CACHE in them, the testcase fails (note: it must be all, changing the last two selects and the testcase still works).

Verified again in 5.0.25-nightly-20060821-log.

[21 Sep 2006 0:10] Morgan Tocker

Updated bug title to mention query_cache must be on.

[9 Nov 2006 14:27] Heikki Tuuri

The bug found: InnoDB-5.0 only sets its internal isolation level at the end of the first processed SQL statement. Since the first statement for client 1 in the second session is served from the query cache, InnoDB does not set its internal isolation level yet to READ COMMITTED at its end.

http://bugs.mysql.com/bug.php?id=23165 is a duplicate of this bug report.

[9 Nov 2006 14:38] Heikki Tuuri

The fix in 5.0:

This code in ha_innodb.cc, in ::external_lock():

                if (trx->n_mysql_tables_in_use == 1) {
                        trx->isolation_level = innobase_map_isolation_level(
                                                (enum_tx_isolation)
                                                thd->variables.tx_isolation);
                }

should be replaced with:

                if (trx->n_mysql_tables_in_use == 1) {
                        trx->isolation_level = innobase_map_isolation_level(
                                                (enum_tx_isolation)
                                                thd->variables.tx_isolation);

                        if (trx->isolation_level <= TRX_ISO_READ_COMMITTED
                                                && trx->global_read_view) {

                                /* At low transaction isolation levels we let
                                each consistent read set its own snapshot */

                                read_view_close_for_mysql(trx);
                        }
               }

and tested carefully.

#####################

The fix in 5.1:

This code in ha_innodb.cc in ::store_lock():

        if (lock_type != TL_IGNORE
        && trx->n_mysql_tables_in_use == 0) {
                trx->isolation_level = innobase_map_isolation_level(
                                                (enum_tx_isolation)
                                                thd->variables.tx_isolation);
        }

should be replaced with:

        if (lock_type != TL_IGNORE
        && trx->n_mysql_tables_in_use == 0) {
                trx->isolation_level = innobase_map_isolation_level(
                                                (enum_tx_isolation)
                                                thd->variables.tx_isolation);

                if (trx->isolation_level <= TRX_ISO_READ_COMMITTED
                                                && trx->global_read_view) {

                         /* At low transaction isolation levels we let
                         each consistent read set its own snapshot */

                         read_view_close_for_mysql(trx);
                }
        }

Sunny, please put these to 5.0 and 5.1, and test.

Regards,

Heikki

[1 Mar 2007 10:36] Marko Mäkelä

Sent innodb-5.0-ss1319.tar.gz to MySQL

[6 Apr 2007 17:21] Bugs System

Pushed into 5.0.40

[6 Apr 2007 17:25] Bugs System

Pushed into 5.1.18-beta

[16 Apr 2007 12:12] Heikki Tuuri

I think Sunny's description for the docs is ok.

[17 Apr 2007 19:49] Paul DuBois

Noted in 5.0.40, 5.1.18 changelogs.

For InnoDB, fixed consistent-read behavior of the first read
statement, if the read was served from the query cache, for the READ
COMMITTED isolation level.

[5 May 2010 15:03] 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 2:22] Paul DuBois

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

[28 May 2010 5:46] 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:16] 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:43] 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 15:32] Paul DuBois

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

[17 Jun 2010 11:45] 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:23] 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:11] 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)