1 概述

本文测试 MySQL InnoDB 引擎的事务中执行失败的语句，是否会回滚该事务。

测试的 MySQL 版本:

  • 8.0.25 MySQL Community Server - GPL
  • 5.7

MySQL 配置:

  • transaction_isolation: ‘REPEATABLE-READ’
  • autocommit: 1

测试的执行失败语句有3种：

  • 导致死锁
  • 等待锁超时
  • 错误的 SQL 语句：SQL 语法错误，语句中的表、字段不存在

测试方法：

使用 mysql 程序执行 MySQL 语句。在一个事务中，依次执行一个成功的 UPDATE 语句（记为 S1）和一个失败的语句（记为 S2），然后提交该事务。然后查询 S1 更新的行，若该行被 S1 更新了，说明 S2 未导致事务回滚，否则说明 S2 导致事务回滚了。

测试表的结构：

  1. CREATE TABLE `t` (
  2.   `id` int NOT NULL AUTO_INCREMENT,
  3.   `a` int DEFAULT NULL,
  4.   PRIMARY KEY (`id`)
  5. ) ENGINE=InnoDB

每次测试前，将表数据设置成 (id, a) = (1, 1), (2, 1)

2 测试死锁是否会回滚事务

表1 是在 MySQL 8.0 上测试死锁是否会回滚事务的过程，在 MySQL 5.7 上的测试过程基本相同，只是 “select from t where id = 2 for share” 语句的语法不同，要改成 “select from t where id = 2 lock in share mode”。下文中等待锁超时和错误的 SQL 语句 2 种情形的测试也是一样的，

表1 在 MySQL 8.0 上测试死锁是否会回滚事务

步骤 会话1 会话2
操作 结果 操作 结果
1 select id, a from t where id = 1; (1, 1)
2 begin; 开始事务T
3 update t set a = a + 1 where id = 1;
4 begin;
5 update t set a = a + 1 where id = 2;
6 select * from t where id = 1 for share; 被阻塞
7 select * from t where id = 2 for share; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 步骤6中会话2的语句执行成功
8 commit;
9 select id, a from t where id = 1; (1, 1)

步骤9说明步骤3的 UPDATE 语句没有生效，说明步骤7的死锁语句导致事务T回滚了。

3 测试等待锁超时是否会回滚事务

表2 在 MySQL 8.0 上测试等待锁超时是否会回滚事务

步骤 会话1 会话2
操作 结果 操作 结果
1 select id, a from t where id = 1; (1, 1)
2 begin; 开始事务T
3 update t set a = a + 1 where id = 1;
4 begin;
5 update t set a = a + 1 where id = 2;
6 select * from t where id = 2 for share; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
7 commit;
8 select id, a from t where id = 1; (1, 2)

步骤8说明步骤3生效了，说明步骤6的等待锁超时并未回滚事务

4 测试错误的SQL语句是否会回滚事务

步骤 操作 结果
1 select id, a from t where id = 1; (1, 1)
2 begin;
3 update t set a = a + 1 where id = 1;
4 select from t where id = 1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘from t where id = 1’ at line 1
5 select b from t where id = 1; ERROR 1054 (42S22): Unknown column ‘b’ in ‘field list’
6 select a from t2 where id = 1; ERROR 1146 (42S02): Table ‘tmp.t2’ doesn’t exist
7 commit;
8 select id, a from t where id = 1; (1, 2)

步骤8说明步骤3生效了，说明步骤4-6 3 种错误的 SQL 语句都没有回滚事务。

5 结论

对于 MySQL 5.7 和 8.0 的 InnoDB 引擎，死锁会回滚事务，等待锁超时和错误的 SQL 语句（SQL 语法错误，语句中的表、字段不存在）不会回滚事务。