MySQL Bugs: #100293: The SERIALIZABLE transactions are not blocked for query cache

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 (OCA)	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

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
```

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

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.

Sorry, I should have guessed and added at first :)
Verified as described.

regards,
Umesh

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.