Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-7593

transaction-isolation level is not honored when changed at session level

Details

    • Bug
    • Status: Done
    • Medium
    • Resolution: Fixed
    • 5.7.31-34, 8.0.22-13, 5.7.33-36 (Q1 2021)
    • 5.7.34-37 (Q2 2021)
    • None

    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:

      [[email protected] ~]# mysql -A
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      Your MySQL connection id is 5
      Server version: 5.7.31-34-log Percona Server (GPL), Release 34, Revision 2e68637
      Copyright (c) 2009-2020 Percona LLC and/or its affiliates
      Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
      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 ‘\cto 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:

      [[email protected] ~]# mysql -A
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      Your MySQL connection id is 5
      Server version: 5.7.31-34-log Percona Server (GPL), Release 34, Revision 2e68637
      Copyright (c) 2009-2020 Percona LLC and/or its affiliates
      Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
      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 ‘\cto 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.

      Attachments

        Activity

          People

            kamil.holubicki Kamil Holubicki
            francisco.bordenave Francisco Bordenave
            Votes:
            3 Vote for this issue
            Watchers:
            9 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Time Tracking

                Estimated:
                Original Estimate - 49 minutes Original Estimate - 49 minutes
                49m
                Remaining:
                Time Spent - 2 days, 4 hours, 46 minutes Remaining Estimate - 31 minutes
                31m
                Logged:
                Time Spent - 2 days, 4 hours, 46 minutes Remaining Estimate - 31 minutes
                2d 4h 46m

                Smart Checklist