Bug #84295 The behavior is different when innodb_rollback_on_timeout=on
Submitted: 21 Dec 2016 8:25 Modified: 6 Feb 2017 18:45
Reporter: doller half Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6.11,5.7.12 OS:Any
Assigned to: CPU Architecture:Any

[21 Dec 2016 8:25] doller half
Description:
In MySQL 5.6.11, a transaction will rollback occurs to a deadlock lock wait timeout and start a new transaction when we set innodb_rollback_on_timeout=on and start a transaction with BEGIN or START TRANSACTION statement.

But it is different from the MySQL 5.7.12, when a transaction was rolled back, it won't start a new one.

How to repeat:
MySQL 5.6.11:
1. In my.cnf , add:
innodb-rollback-on-timeout=on

2. create a table
mysql> create table t1( id int not null primary key, age int );
mysql> insert into t1 values(1,1),(2,2);

3. create 2 sessions, A and B :

A : 
mysql> begin;
mysql> select * from t1 where id=1 lock in share mode;

B : 
mysql> begin;
mysql> insert into t1 value(3,3);
session b: db01> select * from t1;
+----+------+
| id | age  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
mysql> select trx_id,trx_state,trx_mysql_thread_id,trx_query from information_schema.innodb_trx\G
*************************** 1. row ***************************
             trx_id: 5388
          trx_state: RUNNING
trx_mysql_thread_id: 2
          trx_query: select trx_id,trx_state,trx_mysql_thread_id,trx_query from information_schema.innodb_trx
*************************** 2. row ***************************
             trx_id: 5387
          trx_state: RUNNING
trx_mysql_thread_id: 1
          trx_query: NULL

mysql> update t1 set age = 11 where id =1 ;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from t1;
+----+------+
| id | age  |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
mysql> select trx_id,trx_state,trx_mysql_thread_id,trx_query from information_schema.innodb_trx\G
*************************** 1. row ***************************
             trx_id: 5401
          trx_state: RUNNING
trx_mysql_thread_id: 2
          trx_query: select trx_id,trx_state,trx_mysql_thread_id,trx_query from information_schema.innodb_trx
*************************** 2. row ***************************
             trx_id: 5387
          trx_state: RUNNING
trx_mysql_thread_id: 1
          trx_query: NULL

We can see the trx_id is different in row 1.
A new transaction will start when an old one was rolled back.

MySQL 5.7.12:
1. In my.cnf , add:
innodb-rollback-on-timeout=on

2. create a table
mysql> create table t1( id int not null primary key, age int );
mysql> insert into t1 values(1,1),(2,2);

3. create 2 sessions, A and B :

A : 
mysql> begin;
mysql> select * from t1 where id=1 lock in share mode;

B : 
mysql> begin;
mysql> insert into t1 value(3,3);
mysql> select * from t1;
+----+------+
| id | age  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
mysql> select trx_id,trx_state,trx_mysql_thread_id,trx_query from information_schema.innodb_trx\G
*************************** 1. row ***************************
             trx_id: 3201284
          trx_state: RUNNING
trx_mysql_thread_id: 2
          trx_query: select trx_id,trx_state,trx_mysql_thread_id,trx_query from information_schema.innodb_trx
*************************** 2. row ***************************
             trx_id: 422128392099664
          trx_state: RUNNING
trx_mysql_thread_id: 3
          trx_query: NULL

mysql> update t1 set age = 11 where id =1 ;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from t1;
+----+------+
| id | age  |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
mysql> select trx_id,trx_state,trx_mysql_thread_id,trx_query from information_schema.innodb_trx\G
*************************** 1. row ***************************
             trx_id: 422128392099664
          trx_state: RUNNING
trx_mysql_thread_id: 3
          trx_query: NULL

In MySQL 5.7.12, it will not start a new transaction when a transaction was rolled back.

Suggested fix:
it can be documented as not perfectly conforming to MySQL 5.6.11 in MySQL 5.7 Reference Manual or fix it and make it's behavior be consistent between MySQL 5.6.11 and MySQL 5.7.12.
[19 Jan 2017 15:45] MySQL Verification Team
Hi !

Thank you for your bug report.

I was not able to find this change in behavior documented, so our manual requires to be updated.
[6 Feb 2017 18:45] Daniel Price
Posted by developer:
 
Enabling innodb_rollback_on_timeout does not cause a new transaction to be started in case of a deadlock timeout.

The behavior in the reported example is described here:

https://dev.mysql.com/doc/refman/5.6/en/innodb-error-handling.html

"When a transaction rollback occurs due to a deadlock or lock wait timeout, it cancels the effect of the statements within the transaction. But if the start-transaction statement was START TRANSACTION or BEGIN statement, rollback does not cancel that statement. Further SQL statements become part of the transaction until the occurrence of COMMIT, ROLLBACK, or some SQL statement that causes an implicit commit."

In the 5.6 example, trx_id 5401 is the I_S query. It is not a "new transaction". Transaction 5387 is the uncanceled transaction that was started with a BEGIN statement.

In the 5.7 example, trx_id 422128392099664 is the uncanceled transaction that was started with a BEGIN statement.

The innodb_rollback_on_timeout description was updated to include the quoted information noted above.
https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_rollback_on_t...

The change should appear online within 24 hours.

Thank you for the bug report.