mysql的数据库中的on,MySQL数据库innodb_rollback_on_timeout参数

在使用MySQL数据库时，有时会出现ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 这样的报错。而在一个事务中，如果其中一条sql执行时出现此报错，对本事务的其他脚本是否有影响呢，后面如果执行commit操作，报错之前语句的结果是否成功呢？这个结果与隔离级别以及innodb_rollback_on_timeout参数设置有关。

注：

MySQL默认隔离级别为 REPEATABLE-READ，innodb_rollback_on_timeout为OFF，本文基于innodb表(支持事务)进行测试。

1. 准备工作

1.1  测试环境

MySQL 8.0

1.2 创建测试表及预备数据

创建一张测试表，并插入一条记录

mysql> use testdb;Database changedmysql> create table test1(id int primary key,name varchar(20));Query OK, 0 rows affected (0.01 sec)mysql> insert into test1 values(1,'1wdrt5');Query OK, 1 row affected (0.00 sec)mysql> select  * from  test1;+----+--------+| id | name   |+----+--------|  1 | 1wdrt5 |+----+--------+1 row in set (0.00 sec)

下面将根据不同的隔离级别及innodb_rollback_on_timeout启停情况进行测试。

2. 测试过程

2.1 隔离级别REPEATABLE-READ & innodb_rollback_on_timeout =OFF

a) 测试过程：session Asession B

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1 where id=1 for update;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

+----+--------+

1 row in set (0.00 sec)

mysql> select * from test1;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

|  2 | 2edft6 |

+----+--------+

2 rows in set (0.00 sec)mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select  * from test1;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

+----+--------+

1 row in set (0.00 sec)

mysql> insert into test1 values(2,'2edft6');

Query OK, 1 row affected (0.00 sec)

mysql> delete from  test1 where id=1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> commit;

Query OK, 0 rows affected (0.02 sec)

mysql> select * from test1;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

|  2 | 2edft6 |

+----+--------+

2 rows in set (0.00 sec)

b) 测试结果：

隔离级别REPEATABLE-READ &  innodb_rollback_on_timeout =OFF (2个参数均为默认值)的情况下，即使事务中有超时回滚报错，超时前的sql不会回滚，依旧执行成功。

2.2   隔离级别为READ-COMMITTED  &  innodb_rollback_on_timeout =OFF

a) 测试过程session Asession B

mysql> show global variables like 'transaction_isolation';

+-----------------------+----------------+

| Variable_name         | Value          |

+-----------------------+----------------+

| transaction_isolation | READ-COMMITTED |

+-----------------------+----------------+

1 row in set (0.01 sec)

mysql> use testdb;

Database changed

mysql> begin ;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

|  2 | 2edft6 |

+----+--------+

2 rows in set (0.00 sec)

mysql> select * from test1 where id =1 for update;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

+----+--------+

1 row in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

|  2 | 2edft6 |

|  3 | 3eft6  |

+----+--------+

3 rows in set (0.00 sec)mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

|  2 | 2edft6 |

+----+--------+

2 rows in set (0.00 sec)

mysql> insert into test1 values(3,'3eft6');

Query OK, 1 row affected (0.00 sec)

mysql> delete from test1 where id=1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

|  2 | 2edft6 |

|  3 | 3eft6  |

+----+--------+

3 rows in set (0.00 sec)

b)测试结果：

隔离级别为READ-COMMITTED  &  innodb_rollback_on_timeout =OFF 情况下，即使事务中有超时回滚报错，超时前的sql不会回滚，依旧执行成功，同2者均为默认值的情况。

2.3  隔离级别REPEATABLE-READ &  innodb_rollback_on_timeout =ON

注：innodb_rollback_on_timeout不能在线修改，需要修改配置文件后重启生效

测试过程：

a) 修改配置文件，重启数据库

在my.cnf文件里添加innodb_rollback_on_timeout=on  再重启数据库即可生效

mysql> show global variables like 'transaction_isolation';+-----------------------+-----------------+| Variable_name         | Value           |+-----------------------+-----------------+| transaction_isolation | REPEATABLE-READ |+-----------------------+-----------------+1 row in set (0.00 sec)mysql> show global variables like 'innodb_rollback_on_timeout';+----------------------------+-------+| Variable_name              | Value |+----------------------------+-------+| innodb_rollback_on_timeout | ON    |+----------------------------+-------+1 row in set (0.00 sec)

b) 事务测试过程session Asession B

mysql> select * from test1;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

|  2 | 2edft6 |

|  3 | 3eft6  |

+----+--------+

3 rows in set (0.00 sec)

mysql> select * from test1 where id=1 for update;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

+----+--------+

1 row in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

|  2 | 2edft6 |

|  3 | 3eft6  |

+----+--------+

3 rows in set (0.00 sec)mysql> use testdb;

Database changed

mysql>begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

|  2 | 2edft6 |

|  3 | 3eft6  |

+----+--------+

3 rows in set (0.00 sec)

mysql> insert into test1 values(4,'4rgy7');

Query OK, 1 row affected (0.00 sec)

mysql> delete from test1 where id=1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

|  2 | 2edft6 |

|  3 | 3eft6  |

+----+--------+

3 rows in set (0.00 sec)

c) 测试结果：

隔离级别REPEATABLE-READ &  innodb_rollback_on_timeout =ON 的情况下，事务中有超时回滚报错时，超时前sql也会回滚。

2.4   隔离级别为READ-COMMITTED  &  innodb_rollback_on_timeout =ON

a) 参数调整

mysql> set global  transaction_isolation='READ-COMMITTED';mysql> exit#  重新登录mysql> show global variables like 'transaction_isolation';+-----------------------+----------------+| Variable_name         | Value          |+-----------------------+----------------+| transaction_isolation | READ-COMMITTED |+-----------------------+----------------+1 row in set (0.00 sec)mysql> show global variables like 'innodb_rollback_on_timeout';+----------------------------+-------+| Variable_name              | Value |+----------------------------+-------+| innodb_rollback_on_timeout | ON    |+----------------------------+-------+1 row in set (0.00 sec)

b) 测试过程session Asession B

mysql> use testdb;

Database changed

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select  * from  test1;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

|  2 | 2edft6 |

|  3 | 3eft6  |

+----+--------+

3 rows in set (0.00 sec)

mysql> select * from test1 where id =1 for  update;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

+----+--------+

1 row in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select  * from  test1;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

|  2 | 2edft6 |

|  3 | 3eft6  |

|  5 | 5thu8  |

+----+--------+

4 rows in set (0.00 sec)mysql> use testdb;

Database changed

mysql> select  * from test1;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

|  2 | 2edft6 |

|  3 | 3eft6  |

+----+--------+

3 rows in set (0.00 sec)

mysql> insert into test1 values(5,'5thu8');

Query OK, 1 row affected (0.01 sec)

mysql> delete from test1 where id =1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select  * from test1;

+----+--------+

| id | name   |

+----+--------+

|  1 | 1wdrt5 |

|  2 | 2edft6 |

|  3 | 3eft6  |

|  5 | 5thu8  |

+----+--------+

4 rows in set (0.00 sec)

c)  测试结果

隔离级别为READ-COMMITTED  &  innodb_rollback_on_timeout =ON的情况下，即使事务中有超时回滚报错，超时前的sql不会回滚，依旧执行成功，同2者均为默认值的情况。

3. 小结

在MySQL8.0 中,仅有在隔离级别为READ-COMMITTED  &  innodb_rollback_on_timeout =ON情况下，事务中有超时回滚报错时，超时前sql也会回滚。隔离级别innodb_rollback_on_timeout结果

REPEATABLE-READOFF超时回滚前的SQL不会自动回滚

READ-COMMITTEDOFF超时回滚前的SQL不会自动回滚

REPEATABLE-READON超时回滚前的SQL会自动回滚

READ-COMMITTEDON超时回滚前的SQL不会自动回滚

TIPS：

1)  测试过程中可以查看information_schema.innodb_trx表观察事务情况，在不同的版本中事务情况不一样.例如，隔离级别REPEATABLE-READ& innodb_rollback_on_timeout=on的情况下，MySQL5.6 中整个事务回滚后会自动创建一个事务，而MySQL5.7则不会再自动创建事务。

2)  在生产环境使用中,建议将innodb_rollback_on_timeout 设置为ON。应用程序一定要做好事务控制，在一个事务出现异常时必须进行显式rollback