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

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

Morgan,

how do you set the isolation level to READ COMMITTED?

--Heikki

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

In my.cnf

Morgan,

please show the exact line.

--Heikki

# 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.

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

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

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)

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.

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

Heikki,

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

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.

Updated bug title to mention query_cache must be on.

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.

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

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

Pushed into 5.0.40

Pushed into 5.1.18-beta

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

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.

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

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

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

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

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)