![9389a78f9d8a6f9edb23927c5725f19e.png](https://img-blog.csdnimg.cn/img_convert/9389a78f9d8a6f9edb23927c5725f19e.png)
在使用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) 测试过程:
![7d3ae3d2115fbcb7faf50564a237286f.png](https://img-blog.csdnimg.cn/img_convert/7d3ae3d2115fbcb7faf50564a237286f.png)
![4a393720170223c9fa9d9bdf9aa93790.png](https://img-blog.csdnimg.cn/img_convert/4a393720170223c9fa9d9bdf9aa93790.png)
b) 测试结果:
隔离级别REPEATABLE-READ & innodb_rollback_on_timeout =OFF (2个参数均为默认值)的情况下,即使事务中有超时回滚报错,超时前的sql不会回滚,依旧执行成功。
2.2 隔离级别为READ-COMMITTED & innodb_rollback_on_timeout =OFF
a) 测试过程
![42503b12f78febdba8d1ebc5e03e5b8a.png](https://img-blog.csdnimg.cn/img_convert/42503b12f78febdba8d1ebc5e03e5b8a.png)
![cf9847e1ce7c746ce4f6151ffbf253a8.png](https://img-blog.csdnimg.cn/img_convert/cf9847e1ce7c746ce4f6151ffbf253a8.png)
![535c843ff992e57f3710a5f21505a5de.png](https://img-blog.csdnimg.cn/img_convert/535c843ff992e57f3710a5f21505a5de.png)
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) 事务测试过程
![6a803bc2b6dbe5ce589a379bb2bef21e.png](https://img-blog.csdnimg.cn/img_convert/6a803bc2b6dbe5ce589a379bb2bef21e.png)
![4f9cca0e1b8cccf807fb1375f6ce3a5c.png](https://img-blog.csdnimg.cn/img_convert/4f9cca0e1b8cccf807fb1375f6ce3a5c.png)
![fdf1fb00aaf6d126b0ba33960c8de49c.png](https://img-blog.csdnimg.cn/img_convert/fdf1fb00aaf6d126b0ba33960c8de49c.png)
c) 测试结果:
隔离级别REPEATABLE-READ & innodb_rollback_on_timeout =ON 的情况下,事务中有超时回滚报错时,超时前sql也会回滚。
2.4 隔离级别为READ-COMMITTED & innodb_rollback_on_timeout =ON
a) 参数调整
![98e9e7481f77c35790fd0cafd2e8bc17.png](https://img-blog.csdnimg.cn/img_convert/98e9e7481f77c35790fd0cafd2e8bc17.png)
b) 测试过程
![e6f62c9522943c10ead70449b33ce258.png](https://img-blog.csdnimg.cn/img_convert/e6f62c9522943c10ead70449b33ce258.png)
![f0264f713fa395932c2d04702d25726d.png](https://img-blog.csdnimg.cn/img_convert/f0264f713fa395932c2d04702d25726d.png)
![0b03b4643b850094503d8190749287fc.png](https://img-blog.csdnimg.cn/img_convert/0b03b4643b850094503d8190749287fc.png)
c) 测试结果
隔离级别为READ-COMMITTED & innodb_rollback_on_timeout =ON的情况下,即使事务中有超时回滚报错,超时前的sql不会回滚,依旧执行成功,同2者均为默认值的情况。
3. 小结
在MySQL8.0 中,仅有在隔离级别为READ-COMMITTED & innodb_rollback_on_timeout =ON情况下,事务中有超时回滚报错时,超时前sql也会回滚。
![cbacd64c23af8e95b4c96e298969adda.png](https://img-blog.csdnimg.cn/img_convert/cbacd64c23af8e95b4c96e298969adda.png)
TIPS:
1) 测试过程中可以查看information_schema.innodb_trx表观察事务情况,在不同的版本中事务情况不一样.例如,隔离级别REPEATABLE-READ & innodb_rollback_on_timeout=on的情况下,MySQL5.6 中整个事务回滚后会自动创建一个事务,而MySQL5.7则不会再自动创建事务。
2) 在生产环境使用中,建议将innodb_rollback_on_timeout 设置为ON。应用程序一定要做好事务控制,在一个事务出现异常时必须进行显式rollback
微信公众号: 数据库干货铺