MySQL数据库innodb_rollback_on_timeout详解

运维那点事| 阅读:766 发表时间:2018-02-22 18:28:16 mysql
摘要:在MySQL 5.6&5.7中默认值为OFF,当InnoDB默认情况下仅回滚事务超时的最后一条语句。如果innodb_rollback_on_timeout值为ON,则事务超时后将导致InnoDB中止并回滚整个事务。
一、innodb_rollback_on_timeout变量

有时侯会发生事务超时的情况,MySQL会返回类似这样的错误:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.
那事务超时后会发生什么呢?此时就需要注意到innodb_rollback_on_timeout了。

这是官方文档对innodb_rollback_on_timeout的解释:
在MySQL 5.6&5.7中默认值为OFF,当InnoDB默认情况下仅回滚事务超时的最后一条语句。如果innodb_rollback_on_timeout值为ON,则事务超时后将导致InnoDB中止并回滚整个事务。

二、验证innodb_rollback_on_timeout=off的情况
Session A
开启一个事务,使用读锁锁住一行数据。
session a: db01> start transaction;
session a: db01> select * from t1 where id = 1 lock in share mode ;
+----+------+
| id | age  |
+----+------+
|  1 |    1 |
+----+------+

Session B
显示开启事务,插入数据后查询到事务ID是4891。
session b:db01> start transaction;
session b:db01> insert into t1 value(3,3);
session b:db01> select trx_id,trx_state,trx_mysql_thread_id,trx_query from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 4891
                 trx_state: RUNNING
       trx_mysql_thread_id: 4
                 trx_query: select * from information_schema.innodb_trx
*************************** 2. row ***************************
                    trx_id: 4888
                 trx_state: RUNNING
       trx_mysql_thread_id: 5
                 trx_query: NULL
再在此事务中执行会造成锁等待的语句,超时后查询发现,数据(3,3)顺利插入,但是理应更新的数据(1,11)没有了,说明发生了文档所说的回滚最新的一条语句。 事务并不会自动结束,不然就会破坏事务的原子性。

session b:db01> update t1 set age = 11 where id = 1 ;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
session b:db01> select * from t1;
+----+------+
| id | age  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)
查询事务,发现4891事务还存在。

session b:db01> select trx_id,trx_state,trx_mysql_thread_id,trx_query from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 4891
                 trx_state: RUNNING
       trx_mysql_thread_id: 4
                 trx_query: select * from information_schema.innodb_trx
*************************** 2. row ***************************
                    trx_id: 4888
                 trx_state: RUNNING
       trx_mysql_thread_id: 5
                 trx_query: NULL
如果此时Session B执行回滚事务,新插入的数据(3,3)被回滚了。那么Session A也就自然看不到曾经被更改的数据了(不针对读未提交隔离级别)。

如果Session B执行提交,在Session A可以看到数据(3,3) 。

session b:db01> commit ;

Session A
session a:db01>select * from t1 ;
+----+------+
| id | age  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
innodb_rollback_on_timeout=off的情况下,显示开启事务,造成锁等待超时时,会回滚造成超时的那条语句,但是事务不会结束。

三、验证innodb_rollback_on_timeout=on的情况
注意:
1. innodb_rollback_on_timeout不支持动态修改,修改需要停服务。
2. innodb_rollback_on_timeout=on的情况下,5.6版本和5.7版本的行为会不一样,为了避免麻烦,一并在下表做对比。

Session A(5.6&5.7)
session a: db01> select * from t1;
+----+------+
| id | age  |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
session a: db01> start transaction;
session a: db01> select * from t1 where id = 1 lock in share mode ;
+----+------+
| id | age  |
+----+------+
|  1 |    1 |
+----+------+

Session B(5.6)
session b: db01> start transaction ;
session b: db01> insert into t1 value(3,3);
session b: db01> select * from t1;
+----+------+
| id | age  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
session b: db01> 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

Session C(5.7)
session c: db01> start transaction ;
session c: db01> insert into t1 value(3,3);
session c: db01> select * from t1;
+----+------+
| id | age  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
session c: db01> 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
Session B(5.6)和Session C(5.7)分别开启事务,插入数据(3,3)。查询到新增的事务分别是5388和3201284。

然后都执行会造成锁等待的语句:

Session B(5.6)
session b: db01> update t1 set age = 11 where id =1 ;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
session b: db01> select * from t1;
+----+------+
| id | age  |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
session b: db01> 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

Session C(5.7)
session c: db01> update t1 set age = 11 where id = 1 ;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
session c: db01> select * from t1;
+----+------+
| id | age  |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
session c: db01> 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
超时后再查询发现:

1. 新插入的数据都回滚了。
2. Session B(5.6)原先的事务5388已经不见了,新增加了一个事务5401。说明5.6版本情况下,innodb_rollback_on_timeout=on,锁超时后,接收下一句语句时会立即开启一个新事务。
3. Session C(5.7)原先的事务3201284已经不见了。

再验证不显示开始事务的情况,发现此时Session B(5.6)没有自动开启事务。

session b: db01> rollback ;
session b: db01> update t1 set age = 11 where id =1 ;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
session b: db01> select trx_id,trx_state,trx_mysql_thread_id,trx_query from information_schema.innodb_trx\G
*************************** 1. row ***************************
             trx_id: 5387
          trx_state: RUNNING
trx_mysql_thread_id: 1
          trx_query: NULL

session c: db01> update t1 set age = 11 where id = 1 ;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
session c: db01> 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

innodb_rollback_on_timeout=on时,在5.6的版本下,由上文加粗加红的字体可以看到事务的ID的发祥变化,说明开启了一个新的事务。由此明白事务会整体回滚,然后新开一个事务接收下一次查询。但是在5.7版本下,回滚时候之后就不会再新开启一个事务了。

四、总结
如果使用MySQL 5.6:
innodb_rollback_on_timeout=off的情况下,会回滚最后的造成锁等待的语句,事务没有自动结束.但是这样会造成数据的不一致,破坏了事务的原子性。
innodb_rollback_on_timeout=on的情况下,整个事务回滚后会自动创建一个事务。

如果使用MySQL 5.7:
innodb_rollback_on_timeout=off的情况下和5.6版本是一样的。
innodb_rollback_on_timeout=on的情况下,整个事务已经自动回滚,不会再自动创建事务。

所以不管是5.6的版本还是5.7的版本,innodb_rollback_on_timeout最好设置成ON,这样可以避免破坏事务原子性,保证数据一致性。唯一的区别是在5.7版本下需要自己手动开启一个事务。

问题:innodb_rollback_on_timeout为OFF,事务的原子性被破坏了吗?
答:NO,从示例中可以看到,事务只是回退上一条语句的状态,而整个事务实际上没有完成(提交或者回滚),而作为应用程序在检测这个错误时,应该选择是提交或者回滚事务。如果严格要求事务的原子性,当然是执行ROLLBACK,回滚事务。

摘自:http://www.ywnds.com/?p=9560
如果您觉得好,可以打赏作者:
如果您觉得累了,是否想看点美女养养眼:猛戳>>若兰网
如果您觉得皮了,是否想来点神吐槽:猛戳>>iPhone查询中

已有2条评论

昵称:
邮箱:

  • 最新评论

godaddy2018-03-15 10:54:10
朋友 交换链接吗
2018-03-15 16:17:29 (管理员回复)
交换的
小弟2018-02-23 14:58:42
很好的讲解~
iPhone查询中 - bbs.ipcxz.com 若兰网 - www.rolan.wang iPhone查询中 - bbs.ipcxz.com
反馈
微信订阅号