环境:Server version: 5.6.21-log MySQL Community Server (GPL)
前提提要:
innodb_rollback_on_timeout是啥作用? 答:事务B在锁等待超时后是回滚事务内所有的statement还是最后一条语句; 0表示rollback最后一条语句,默认值;有点坑 1表示回滚事务B内所有的statements; 此参数是只读参数,需在my.cnf中配置,并且重启生效; 注意:回滚statements后不自动commit或rollback事务;坑
表结构:
mysql> show create table t12\G; *************************** 1. row *************************** Table: t12 Create Table: CREATE TABLE `t12` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `b` varchar(766) DEFAULT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`), KEY `b` (`b`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
实验一:
参数配置: mysql> show variables like 'innodb_rollback_on_timeout'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | innodb_rollback_on_timeout | ON | +----------------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'tx_iso%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.00 sec) mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.00 sec)
过程:
实验二:
参数配置: mysql> show variables like 'innodb_rollback_on_timeout'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | innodb_rollback_on_timeout | OFF | +----------------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'tx_iso%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.00 sec) mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.00 sec)
过程:
总结:
1、关闭innodb_rollback_on_timeout后,一旦以begin;start transaction;等语句开启一个事务,当锁等待超时后,该事务请求的锁将不释放,直到事务提交或回滚或会话超时;
所以autocommit参数建议设置成ON,只要程序没有显示开启事务,就可以避免上述锁未释放问题。
2、开启innodb_rollback_on_timeout后,一旦锁等待超时,是事务内sql将全部回滚,且释放之前请求的锁。
3、当autocommit=on,只要不显示开启事务,将不存在上面2个问题,即锁的问题和回滚的问题。