MySQL Bugs: #79493: rollback to savepoint inside a read only transaction will change trx to rw mode

Bug #79493	rollback to savepoint inside a read only transaction will change trx to rw mode
Submitted:	2 Dec 2015 11:42	Modified:	11 Dec 2015 5:22
Reporter:	zhai weixiang (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7.9, 5.7.10	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
While executing `ROLLBACK TO SAVEPOINT` statement inside a read only transaction , the transaction will be set to READ-WRITE MODE

How to repeat:

mysql> create table t1 (c1 int, c2 int) engine=innodb;                                                                                                                                                                                                                    Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1,2),(2,3);                                                                                                                                                                                                                                 Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;                                                                                                                                                                                                                                                  +------+------+
| c1   | c2   |
+------+------+
|    1 |    2 |
|    2 |    3 |
+------+------+
2 rows in set (0.00 sec)

mysql> select trx_id from information_schema.innodb_trx;
+-----------------+
| trx_id          |
+-----------------+
| 329362948712272 |      
+-----------------+
1 row in set (0.00 sec)

-----> this is not a real trx_id

mysql> savepoint sb1;
Query OK, 0 rows affected (0.00 sec)

mysql> select trx_id from information_schema.innodb_trx;
+-----------------+
| trx_id          |
+-----------------+
| 329362948712272 |
+-----------------+
1 row in set (0.00 sec)

mysql> rollback to savepoint sb1;
Query OK, 0 rows affected (0.00 sec)

mysql> select trx_id from information_schema.innodb_trx;
+----------+
| trx_id   |
+----------+
| 13077432 |
+----------+
1 row in set (0.00 sec)

----> allocated a new trx id, and the current transaction turns into read-write transaction.

Suggested fix:
This is because when rollback to a savepoint,  the following code path (trx_rollback_to_savepoint_for_mysql --> trx_rollback_to_savepoint_for_mysql_low --> trx_rollback_to_savepoint --> trx_start_if_not_started_xa(trx, true)) is called. Note that the second parameter of  trx_start_if_not_started_xa is true,  and that means the transaction will be set to rw mode.

Hello zhai weixiang,

Thank you for the report and test case.

Thanks,
Umesh

// 5.7.10 build

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, 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 '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql> create table t1 (c1 int, c2 int) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1,2),(2,3);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    2 |
|    2 |    3 |
+------+------+
2 rows in set (0.00 sec)

mysql> select trx_id from information_schema.innodb_trx;
+-----------------+
| trx_id          |
+-----------------+
| 421567426910032 |
+-----------------+
1 row in set (0.00 sec)

mysql>  savepoint sb1;
Query OK, 0 rows affected (0.00 sec)

mysql> select trx_id from information_schema.innodb_trx;
+-----------------+
| trx_id          |
+-----------------+
| 421567426910032 |
+-----------------+
1 row in set (0.00 sec)

mysql> rollback to savepoint sb1;
Query OK, 0 rows affected (0.00 sec)

mysql>  select trx_id from information_schema.innodb_trx;
+--------+
| trx_id |
+--------+
| 1292   |
+--------+
1 row in set (0.00 sec)