Bug #102831 transaction-isolation level is not honored when changed at session level
Submitted: 5 Mar 16:32 Modified: 6 Mar 9:12
Reporter: Francisco Bordenave Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.7.31, 5.7.33 OS:Any (CentOS, Ubuntu)
Assigned to: CPU Architecture:Any
Tags: read-commited, regression, REPEATABLE-READ, tx-isolation

[5 Mar 16:32] Francisco Bordenave
Description:
If you want to change the tx-isolation on a session that already executed a transaction the change is not honored so it may cause a failure in the service. 

Example: 

[root@data1 yum.repos.d]# mysql -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.33-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use percona
Database changed

mysql> show create table checksums\G
*************************** 1. row ***************************
       Table: checksums
Create Table: CREATE TABLE `checksums` (
  `db` char(64) COLLATE utf8_unicode_ci NOT NULL,
  `tbl` char(64) COLLATE utf8_unicode_ci NOT NULL,
  `chunk` int(11) NOT NULL,
  `chunk_time` float DEFAULT NULL,
  `chunk_index` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lower_boundary` blob,
  `upper_boundary` blob,
  `this_crc` char(40) COLLATE utf8_unicode_ci NOT NULL,
  `this_cnt` int(11) NOT NULL,
  `master_crc` char(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `master_cnt` int(11) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`db`,`tbl`,`chunk`),
  KEY `ts_db_tbl` (`ts`,`db`,`tbl`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

mysql> use db_test_tx_iso
Database changed

mysql> show create table test_tx_iso\G
*************************** 1. row ***************************
       Table: test_tx_iso
Create Table: CREATE TABLE `test_tx_iso` (
  `key` varbinary(742) NOT NULL,
  `version` bigint(20) NOT NULL,
  `value` mediumblob,
  `pelock` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`key`,`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
1 row in set (0.00 sec)

mysql> show global variables like 'binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show global variables like 'tx_isolatio%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.01 sec)

mysql> show variables like 'tx_isolatio%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.10 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc)  SELECT 'db_test_tx_iso', 'test_tx_iso', 1, null, null, null, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', CRC32(`key`), `version`, CRC32(`value`), CRC32(`pelock`), CONCAT(ISNULL(`value`), ISNULL(`pelock`)))) AS UNSIGNED)), 10, 16)), 0) AS crc  FROM `db_test_tx_iso`.`test_tx_iso`;
Query OK, 2 rows affected (0.24 sec)
Records: 1  Duplicates: 1  Warnings: 0

*works ok, tx-isolation is read-commited and binlog-format=row

*now within same session:
mysql> set session tx_isolation="REPEATABLE-READ"; set session binlog_format=STATEMENT;  REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc)  SELECT 'db_test_tx_iso', 'test_tx_iso', 1, null, null, null, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', CRC32(`key`), `version`, CRC32(`value`), CRC32(`pelock`), CONCAT(ISNULL(`value`), ISNULL(`pelock`)))) AS UNSIGNED)), 10, 16)), 0) AS crc  FROM `db_test_tx_iso`.`test_tx_iso`;
Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging.
mysql> show variables like 'tx_isolatio%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

*Query fails even if session changed tx-isolation to REPEATABLE-READ

Now if I start a new session:

[root@data1 yum.repos.d]# mysql -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.33-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'tx_isolatio%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

mysql> set session tx_isolation="REPEATABLE-READ"; set session binlog_format=STATEMENT;  REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc)  SELECT 'db_test_tx_iso', 'test_tx_iso', 1, null, null, null, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', CRC32(`key`), `version`, CRC32(`value`), CRC32(`pelock`), CONCAT(ISNULL(`value`), ISNULL(`pelock`)))) AS UNSIGNED)), 10, 16)), 0) AS crc  FROM `db_test_tx_iso`.`test_tx_iso`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected, 1 warning (0.05 sec)
Records: 1  Duplicates: 1  Warnings: 1

mysql> REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc)  SELECT 'db_test_tx_iso', 'test_tx_iso', 1, null, null, null, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', CRC32(`key`), `version`, CRC32(`value`), CRC32(`pelock`), CONCAT(ISNULL(`value`), ISNULL(`pelock`)))) AS UNSIGNED)), 10, 16)), 0) AS crc  FROM `db_test_tx_iso`.`test_tx_iso`;
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 1  Warnings: 1

Queries are executed in the expected way

How to repeat:
1- Start server with binlog_format=ROW and transaction_isolation=READ-COMMITTED
2- create 2 tables, no need to add data or specific table format. 
3- Run a REPLACE INTO table1 SELECT FROM table2; command
4- Change tx-isolation to REPEATABLE READ and binlog_format to STATEMENT (pretty much what pt-table-checksum needs to do) 
5- Repeat query from step 3, it should fail. 

Suggested fix:
This looks like a regression as is not reproducible in 5.7.29, nor in 5.6 versions. 
Expected behavior is that if change of tx-isolation is done on a running session the transaction honor the change and behave accordingly as it was working up to 5.7.29
[6 Mar 9:12] MySQL Verification Team
Hello Francisco,

Thank you for the bug report. 
Verified as described.

Thanks,
Umesh
[16 Apr 10:05] Kamil Holubicki
Hi,
I investigated the issue and here are my finding:

1. When the table is created and then accessed, it is cached in thd's
table cache. It contains, inter alia, handler::cached_table_flags which
is calculated upon transaction_isolation value.
2. After transaction_isolation and binlog_format change
open_tables_for_query() and then sql_base.cc::open_table() finds
table in Table_cache
2. As the table was cached with READ-COMMITTED it has
HA_BINLOG_STMT_CAPABLE flag not set (handler::cached_table_flags)
3. Sql_cmd_insert_select::execute() and then handle_query() calls
select->prepare(thd) BEFORE call to lock_tables()
4. Commit e8cc22afb14914e56e535cb3e61a2b4310033d0e added call to
thd->decide_logging_format(). Decision is made upon cached value of
HA_BINLOG_STMT_CAPABLE flag (not set). This leads to immediate error
as the combination of READ-COMMITED and binlog_format STATEMENT is
not allowed.
5. lock_tables is called which would cause HA_BINLOG_STMT_CAPABLE flag
refresh (handler::cached_table_flags), but it is too late.

Solution:
Force recalculation of handler::cached_table_flags if they are used
before locking the table (call introduced in above mentioned regression
commit)
[16 Apr 10:06] Kamil Holubicki
proposed fix

Attachment: fix.patch (text/x-patch), 8.88 KiB.

[16 Apr 11:27] MySQL Verification Team
Thank you, Kamil Holubicki for the contribution.
Please ensure to re-send the patch via "Contribution" tab. Otherwise we would not be able to accept it. Thank you.

regards,
Umesh
[19 Apr 6:39] Kamil Holubicki
proposed fix

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: fix.patch (text/x-patch), 8.88 KiB.