MySQL Bugs: #102831: transaction-isolation level is not honored when changed at session level

Bug #102831	transaction-isolation level is not honored when changed at session level
Submitted:	5 Mar 2021 16:32	Modified:	6 Mar 2021 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

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

Hello Francisco,

Thank you for the bug report. 
Verified as described.

Thanks,
Umesh

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)

proposed fix

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

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

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.