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.
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.