小心MySQL这个配置可能会让你事务部份提交！！

原创 aisql 2024-04-09

413

1、官方定义

innodb_rollback_on_timeout

InnoDB rolls back only the last statement on a transaction timeout by default. If --innodb-rollback-on-timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction.

官方解释当出现事务等待超时参数innodb_rollback_on_timeout=off的时候，只会回滚最后一条语句，只有当innodb_rollback_on_timeout=on时才会回滚整个事务。

接下来我们来做实验印证这个问题

2、验证

构造测试数据

CREATE TABLE `tt` (
  `id1` int NOT NULL,
  `id2` int NOT NULL,
  UNIQUE KEY `idx` (`id1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `t` (
  `id1` int NOT NULL,
  `id2` int NOT NULL,
  UNIQUE KEY `idx` (`id1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
insert into t values(1,1);
复制

查看参数innodb_rollback_on_timeout = off

先将innodb_lock_wait_timeout 设小点，避免长时间的等待

set innodb_lock_wait_timeout = 10;
复制

tt表为空表

在session1执行以下语句-- 并不提交

begin;
insert into tt values(1,1);
update t set id2 = 2 where id1 = 1;  
复制

在session2执行以下语句 – 并不提交

begin;
set innodb_lock_wait_timeout = 10; 
begin;
insert into tt values(2,2);
update t set id2 = 2 where id1 = 1; --此处会等待session1的锁
复制

等待10s后，出现报错

Error Code: 1205. Lock wait timeout exceeded; try restarting transaction

这个时候先后提交session1与session2

commit;
复制

两个都提交后我们查看tt表数据

可以看到tt表即有session1的插入数据也有session2的插入数据

我们将innodb_rollback_on_timeout改为on再试
这个参数不能动态设置，我们在参数文件加上
innodb_rollback_on_timeout=on
然后重启mysql

参数已为on
清空表数据，重复上面的session1与session2的语句

delete from tt;
复制

重复上面步骤后
表tt只有session1插入的数据，session2因超时已全部回滚

3、总结

innodb_rollback_on_timeout=off 是默认值。如果应用没有很好的catch 错误并显示回滚，很有可能就造成了事务的部份提交，为了安全起见可以将innodb_rollback_on_timeout改为true

4、疑问

很明显当innodb_rollback_on_timeout=off这个默认配置在特殊情况下会造成事务的部份提交，会破坏事务的原子性与一致性。为什么MySQL不把innodb_rollback_on_timeout默认值设为on呢？
难道就像隔离级别默认RR就是为了避免binglog为语句格式的主从重放bug一样。innodb_rollback_on_timeout默认为off也是为了避免某个bug吗？