原文:
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表(支持事务)进行测试。
MySQL 8.0
创建一张测试表,并插入一条记录
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启停情况进行测试。
session A | session B |
mysql> begin; mysql> select * from test1 where id=1 for update;
mysql> select * from test1; |
mysql> begin; mysql> select * from test1; mysql> insert into test1 values(2,‘2edft6‘); mysql> delete from test1 where id=1; mysql> select * from test1;
|
隔离级别REPEATABLE-READ & innodb_rollback_on_timeout =OFF (2个参数均为默认值)的情况下,即使事务中有超时回滚报错,超时前的sql不会回滚,依旧执行成功。
session A | session B |
mysql> show global variables like ‘transaction_isolation‘; mysql> use testdb; mysql> select * from test1; mysql> select * from test1 where id =1 for update;
mysql> commit; mysql> select * from test1; |
mysql> begin; mysql> select * from test1; mysql> insert into test1 values(3,‘3eft6‘); mysql> delete from test1 where id=1; mysql> select * from test1;
|
隔离级别为READ-COMMITTED & innodb_rollback_on_timeout =OFF 情况下,即使事务中有超时回滚报错,超时前的sql不会回滚,依旧执行成功,同2者均为默认值的情况。
注: innodb_rollback_on_timeout不能在线修改,需要修改配置文件后重启生效
测试过程:
在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)
session A | session B |
mysql> select * from test1; mysql> select * from test1 where id=1 for update;
mysql> commit; mysql> select * from test1; | mysql> use testdb; mysql> select * from test1; mysql> insert into test1 values(4,‘4rgy7‘); mysql> delete from test1 where id=1; mysql> select * from test1; |
隔离级别REPEATABLE-READ & innodb_rollback_on_timeout =ON 的情况下,事务中有超时回滚报错时,超时前sql也会回滚。
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)
session A | session B |
mysql> use testdb; mysql> select * from test1; mysql> select * from test1 where id =1 for update;
mysql> commit; mysql> select * from test1; |
mysql> use testdb; mysql> insert into test1 values(5,‘5thu8‘); mysql> delete from test1 where id =1; mysql> select * from test1; |
隔离级别为READ-COMMITTED & innodb_rollback_on_timeout =ON的情况下,即使事务中有超时回滚报错,超时前的sql不会回滚,依旧执行成功,同2者均为默认值的情况。
在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