MySQL数据库innodb_rollback_on_timeout参数
- 2020 年 1 月 23 日
- 筆記
在使用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 changed mysql> 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 A |
session 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 transactionmysql> 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 A |
session 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 changedmysql> 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 transactionmysql> 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 A |
session 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 changedmysql> 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 transactionmysql> 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 A |
session B |
|---|---|
|
mysql> use testdb;Database changedmysql> 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 changedmysql> 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 transactionmysql> 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-READ |
OFF |
超时回滚前的SQL不会自动回滚 |
|
READ-COMMITTED |
OFF |
超时回滚前的SQL不会自动回滚 |
|
REPEATABLE-READ |
ON |
超时回滚前的SQL会自动回滚 |
|
READ-COMMITTED |
ON |
超时回滚前的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
精彩推荐：
1. MySQL不停地自动重启怎么办
2. 升级python，就是这么简单
3. MySQL里trx_mysql_thread_id为0 的事务导致大量锁等待超时该咋整
4. mysql8.0新增用户及加密规则修改的那些事
5. Postgresql部署及简单操作
6. 比hive快10倍的大数据查询利器– presto
7. 国产数据库部署初体验
8. 监控利器出鞘：Prometheus+Grafana监控MySQL、Redis数据库
11. MySQL从库生成大量小的relay log案例模拟
