Bug #100293 The SERIALIZABLE transactions are not blocked for query cache
Submitted: 22 Jul 2020 14:45 Modified: 27 Jul 2020 4:31
Reporter: Ze Yang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Query Cache Severity:S3 (Non-critical)
Version:5.7, 5.7.31, 5.7.30, 5.6.48 OS:Any
Assigned to: CPU Architecture:Any

[22 Jul 2020 14:45] Ze Yang
Description:
When innobase_query_caching_of_table_permitted the trx->isolation_level not set.

The SERIALIZABLE transactions will not be blocked for query cache.
This will cause the records not locked.

And also as trx->isolation_level not set, the trx->isolation_level may be wrong for query cache check.

How to repeat:
query_cache_bug.test

```
CREATE TABLE t(c1 INT);

INSERT INTO t VALUES(1),(2);

BEGIN;
SELECT * FROM t;
COMMIT;

flush status;
SHOW STATUS LIKE "QCACHE_HITS";
SET TRANSACTION_ISOLATION='SERIALIZABLE';
BEGIN;
SELECT * FROM t;
SHOW STATUS LIKE "QCACHE_HITS";

connect (con1, localhost, root);
connection con1;
INSERT INTO t VALUES(3);
connection default;
COMMIT;

disconnect con1;
DROP TABLE t;
```

query_cache_bug.result

```
CREATE TABLE t(c1 INT);
INSERT INTO t VALUES(1),(2);
BEGIN;
SELECT * FROM t;
c1
1
2
COMMIT;
flush status;
SHOW STATUS LIKE "QCACHE_HITS";
Variable_name Value
Qcache_hits 0
SET TRANSACTION_ISOLATION='SERIALIZABLE';
BEGIN;
SELECT * FROM t;
c1
1
2
SHOW STATUS LIKE "QCACHE_HITS";
Variable_name Value
Qcache_hits 1
INSERT INTO t VALUES(3);
COMMIT;
DROP TABLE t;
```

Suggested fix:
```
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index c8c99a9d523..a0f872c13da 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -3010,13 +3010,6 @@ innobase_query_caching_of_table_permitted(

        ut_a(full_name_len < 999);

-       if (trx->isolation_level == TRX_ISO_SERIALIZABLE) {
-               /* In the SERIALIZABLE mode we add LOCK IN SHARE MODE to every
-               plain SELECT if AUTOCOMMIT is not on. */
-
-               return(static_cast<my_bool>(false));
-       }
-
        if (trx->has_search_latch) {
                sql_print_error("The calling thread is holding the adaptive"
                                " search, latch though calling"
@@ -3036,6 +3029,17 @@ innobase_query_caching_of_table_permitted(

        }

+  trx->isolation_level =
+      innobase_map_isolation_level((enum_tx_isolation)thd_tx_isolation(thd));
+
+       if (!is_autocommit && trx->isolation_level == TRX_ISO_SERIALIZABLE) {
+               /* In the SERIALIZABLE mode we add LOCK IN SHARE MODE to every
+               plain SELECT if AUTOCOMMIT is not on. */
+
+               return(static_cast<my_bool>(false));
+       }
+
+
        if (is_autocommit && trx->n_mysql_tables_in_use == 0) {
                /* We are going to retrieve the query result from the query
                cache. This cannot be a store operation to the query cache
```
[24 Jul 2020 11:28] MySQL Verification Team
Hello Ze Yang,

Thank you for the report and feedback.
Could you please confirm exact version in which you are seeing the reported issue? With our without your patch I'm seeing below:

- 
 ./mtr query_cache_bug
Logging: ./mtr  query_cache_bug
MySQL Version 5.7.31
Checking supported features...
 - SSL connections supported
Collecting tests...
Removing old var directory...
Creating var directory '/export/umesh/server/source/bugs/src_build/fb_builds/100293/5731/mysql-test/var'...
Installing system database...
Using parallel: 1

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
CREATE TABLE t(c1 INT);
INSERT INTO t VALUES(1),(2);
BEGIN;
SELECT * FROM t;
c1
1
2
COMMIT;
flush status;
SHOW STATUS LIKE "QCACHE_HITS";
Variable_name   Value
Qcache_hits     0
SET TRANSACTION_ISOLATION='SERIALIZABLE';
BEGIN;
SELECT * FROM t;
c1
1
2
SHOW STATUS LIKE "QCACHE_HITS";
Variable_name   Value
Qcache_hits     0
[ 50%] main.query_cache_bug                     [ fail ]
        Test ended at 2020-07-24 13:25:08

CURRENT_TEST: main.query_cache_bug
mysqltest: At line 18: query 'INSERT INTO t VALUES(3)' failed: 1205: Lock wait timeout exceeded; try restarting transaction
safe_process[420]: Child process: 421, exit: 1

 - the logfile can be found in '/export/umesh/server/source/bugs/src_build/fb_builds/100293/5731/mysql-test/var/log/main.query_cache_bug/query_cache_bug.log'
[100%] shutdown_report                          [ pass ]
--------------------------------------------------------------------------

regards,
Umesh
[27 Jul 2020 2:33] Ze Yang
Sorry that, I forget to add the query_cache_bug-master.opt file

query_cache_bug-master.opt
```
--query_cache_type=1
```

The query cache is disabled by default. Need to open it for the test.

I both test on 5.7.30 and 5.7.31.
[27 Jul 2020 4:31] MySQL Verification Team
Sorry, I should have guessed and added at first :)
Verified as described.

regards,
Umesh
[27 Jul 2020 4:51] MySQL Verification Team
Please note that in order to submit contributions you must first sign the Oracle Contribution Agreement (OCA). More details are described in "Contributions" tab, please ensure to re-send the patch via that tab. Otherwise we would not be able to accept it.