Oracle ACE

MySQL

MySQL 8.4 LTS 事务避坑指南

2026-02-21 2.4k words 10 min read views

对于金融级应用，事务隔离级别的选择往往决定着系统的生死。今天咱们就详细拆解MySQL的事务控制机制。

01. 事务基础：ACID与InnoDB的实现逻辑

MySQL的事务支持主要由InnoDB存储引擎实现，其核心就是ACID四特性。但在实际生产中，很多开发者对AUTOCOMMIT的理解都存在误区。

AUTOCOMMIT模式的双面性：

1
2
3
4
5
6
7
-- 查看当前会话的提交模式（默认ON）
SELECT @@autocommit;
-- 结果：1（表示每条SQL都是一个独立事务）

-- 关闭自动提交（进入手动事务模式）
SET autocommit = 0;
-- 此时每条DML语句都不会自动提交，必须显式COMMIT或ROLLBACK

当AUTOCOMMIT开启时，START TRANSACTION或BEGIN语句会临时禁用自动提交模式，直到COMMIT或ROLLBACK执行后才恢复。很多开发者误以为START TRANSACTION会永久改变会话的AUTOCOMMIT设置。

1
2
3
4
-- 场景：当前AUTOCOMMIT=ON
START TRANSACTION;  -- 临时关闭AUTOCOMMIT，开启显式事务
-- ... 执行DML语句 ...
ROLLBACK;           -- 事务结束，AUTOCOMMIT自动恢复为ON

隐式提交陷阱：

当AUTOCOMMIT从ON切换到OFF后，之前的语句并不会自动成为可回滚事务的一部分。SET AUTOCOMMIT=off之前的UPDATE和INSERT已经作为独立事务提交了，后续ROLLBACK对它们无效。

1
2
3
4
5
SET AUTOCOMMIT=on;
UPDATE emp SET salary=24000 WHERE id=101;  -- 自动提交，已持久化
INSERT INTO EMP VALUES (102,'John',13000); -- 自动提交，已持久化
SET AUTOCOMMIT=off;                        -- 切换模式
ROLLBACK;                                  -- 对前两行无影响！

02. 隔离级别全景：从脏读到幻读

MySQL 支持四种标准的隔离级别，但不同级别带来的并发问题截然不同。

四种隔离级别对比：

隔离级别 脏读 (Dirty Read) 不可重复读 (Non-Repeatable Read) 幻读 (Phantom Read) 实现机制 (Implementation)
READ UNCOMMITTED (读未提交) 可能 可能 可能 无锁读取 (不加锁直接读取最新数据)
READ COMMITTED (读已提交) 不可能 可能 可能 MVCC (多版本并发控制) + 快照读
REPEATABLE READ (可重复读) 不可能 不可能 可能 MVCC + 间隙锁 (或 Next-Key Lock)
SERIALIZABLE (串行化) 不可能 不可能 不可能 所有 SELECT 语句强制加共享锁 (S锁)
  • REPEATABLE READ (可重复读)

MySQL InnoDB 引擎的默认隔离级别。事务在启动执行第一个 SELECT 时生成一个快照，之后整个事务期间都沿用这个快照（快照读），从而解决不可重复读。针对需要加锁的当前读，通过引入间隙锁（Gap Lock）和记录锁组合而成的 Next-Key Lock，锁定数据行之间的间隙，很大程度上防止了其他事务插入新数据，从而在很大程度上避免了幻读。

  • READ COMMITTED (读已提交)

多数数据库（如 Oracle、SQL Server）的默认隔离级别。引入 MVCC（多版本并发控制）。事务中每次执行 SELECT 语句时都会生成一个新的快照（Read View），只能看到已经提交的事务修改。

案例：

当Session 1使用REPEATABLE READ，Session 2使用READ COMMITTED时，即使Session 2的事务已提交，Session 1的第二次SELECT仍然看不到插入的行，因为REPEATABLE READ保证的是事务开始时的快照一致性。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Session 1（REPEATABLE READ）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

mysql> select * from employees;
+-----+---------+------------+-----------+
| eid | ename   | esalary    | ebonus    |
+-----+---------+------------+-----------+
|   1 | Alice   |  8000.0000 | 2000.0000 |
|   2 | Bob     |  9500.0000 |      NULL |
|   3 | Charlie | 12000.0000 |    0.0000 |
|   4 | David   |  7000.0000 |      NULL |
+-----+---------+------------+-----------+
4 rows in set (0.000 sec)


-- Session 2（READ COMMITTED）在此期间插入新行并提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
insert employees select 5,'Shawn',10000,2000;
commit;

-- Session 1再次查询
SELECT * FROM emp;  -- 仍然只看到4行

注意：这不是因为Session 2的隔离级别，而是因为Session 1的RR级别

03. 锁机制与并发控制：FOR UPDATE的奥秘

锁是事务隔离的底层实现。下面解释行级锁与表级锁在并发场景下的行为差异。

FOR UPDATE的锁范围

当Session 1执行SELECT … FOR UPDATE时，即使AUTOCOMMIT=1，由于FOR UPDATE是锁定读，它会对行加排他锁（X锁）。当WHERE条件没有命中任何行时，不会阻塞Session 2的UPDATE。

1
2
3
4
5
6
-- Session 1（autocommit=1）
SELECT * FROM employees FOR UPDATE;  -- 锁定所有行（如果表有数据）

-- Session 2（并发执行）
UPDATE employees SET ebonus=100 WHERE ename LIKE '%b';
-- 如果Session 1锁住了匹配行，Session 2等待；否则可以执行

LOCK TABLES与元数据锁

LOCK TABLES是表级锁，与InnoDB的行级事务锁不同。当Session 0执行LOCK TABLES t2 WRITE后，其他会话对t2的查询会被阻塞。

1
2
3
4
5
6
7
8
-- Session 0
LOCK TABLES mydb.t1 READ, mydb.t2 WRITE;

-- Session 1（被阻塞）
SELECT * FROM mydb.t2;  -- 等待写锁释放

-- Session 3（可以执行，因为t1是READ锁）
SELECT * FROM mydb.t1;  -- 立即返回

死锁受害者选择策略

MySQL选择死锁牺牲者的标准不是事务ID或CPU时间，而是事务修改的行数最少者（the transaction with the fewest modified rows）。这是一个反直觉的设计，目的是保留工作量更大的事务。

04. 事务控制语句与保存点实战

精细的事务控制需要掌握ROLLBACK、COMMIT以及SAVEPOINT的使用。

保存点的嵌套控制，下面是一段 PHP 代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
try {
    $connect->beginTransaction();
    $connect->exec("INSERT INTO band(song) VALUES('Here comes the shawnyan')");
    
    // 设置保存点
    $connect->exec("SAVEPOINT shawnyan");
    
    // ... 可能出错的操作 ...
    
} catch (PDOException $ex) {
    // 回滚到保存点，而非整个事务
    $connect->rollBack(); 
}

超时与回滚行为，下面是一段 Python 代码：

当innodb_rollback_on_timeout=OFF（默认）时，如果发生Lock wait timeout exceeded，只有导致超时的那条语句被回滚，事务中之前的成功语句仍然保留。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# Python代码示例
conn.autocommit = False
cursor = conn.cursor()

try:
    cursor.execute("INSERT INTO band(song, year) VALUES('From Me to You', 1964)")
    # 成功执行，保留在事务中
    
    cursor.execute("UPDATE band SET year=1969 WHERE song='Come Together'")
    # 如果这里发生Lock wait timeout
    
except mysql.connector.Error as error:
    print(error)  # 1205 (HY000): Lock wait timeout exceeded

conn.commit()  # Only the INSERT statement completes successfully

因为innodb_rollback_on_timeout=OFF，UPDATE被回滚，INSERT保留。

MySQL 8.0.26+版本中，事务超时的处理更加严格。建议显式设置innodb_rollback_on_timeout=ON，确保超时后整个事务回滚，避免数据不一致。

05. 死锁诊断与元数据锁管理

除了行级锁死锁，MySQL还有元数据锁（MDL）和命名锁（Named Locks）机制。

命名锁死锁

使用GET_LOCK()函数可以创建用户级锁。当Session 1持有mylock1并请求mylock2，而Session 2持有mylock2并请求mylock1时，会发生死锁。

1
2
3
4
5
6
7
8
9
10
11
12
-- Session 1
SELECT GET_LOCK('mylock1', 70);  -- 获得锁
-- ... 稍后 ...
SELECT GET_LOCK('mylock2', 70);  -- 等待Session 2释放

-- Session 2
SELECT GET_LOCK('mylock2', 70);  -- 获得锁
-- ... 稍后 ...
SELECT GET_LOCK('mylock1', 70);  -- 请求mylock1，形成循环等待

mysql> SELECT GET_LOCK('mylock1', 70);
ERROR 3058 (HY000): Deadlock found when trying to get user-level lock; try rolling back transaction/releasing locks and restarting lock acquisition.

结果：发生死锁。不同于InnoDB死锁会自动牺牲一方，GET_LOCK死锁直接报错。

DDL操作与事务阻塞

OPTIMIZE TABLE是DDL操作，需要元数据锁。

如果Session 1有活跃事务（哪怕只是START TRANSACTION后的SELECT），Session 2的OPTIMIZE TABLE会等待。

1
2
3
4
5
6
-- Session 1
START TRANSACTION;
SELECT * FROM mydb.orders;  -- 持有MDL读锁

-- Session 2
OPTIMIZE TABLE mydb.orders;  -- 需要MDL写锁，必须等待Session 1提交

当 Session 1 提交后，session 2 返回结果。

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Session 1
mysql> commit;
Query OK, 0 rows affected (0.000 sec)

-- Session 2
mysql> OPTIMIZE TABLE mydb.orders;
+-------------+----------+----------+-------------------------------------------------------------------+
| Table       | Op       | Msg_type | Msg_text                                                          |
+-------------+----------+----------+-------------------------------------------------------------------+
| mydb.orders | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| mydb.orders | optimize | status   | OK                                                                |
+-------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (8.614 sec)

06. AI场景的事务优化

MySQL 8.4 LTS推荐配置：

1
2
3
4
5
6
7
8
9
10
11
# my.cnf
[mysqld]
# 事务隔离级别建议（AI场景通常不需要严格RR）
transaction_isolation = READ-COMMITTED

# 死锁检测间隔优化（高并发AI推理场景）
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = ON  # 确保数据一致性

# 元数据锁超时（防止DDL被长事务阻塞）
lock_wait_timeout = 600

AI数据管道的事务模式：

对于大模型训练的ETL管道，建议使用 READ COMMITTED 级别配合批量提交，减少MVCC版本链长度，提升大批量插入性能。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- AI特征工程事务模板
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

-- 批量插入（利用OCI HeatWave的并行插入优化）
INSERT INTO features (model_id, feature_vector) VALUES 
    (1, '[0.1, 0.2, ...]'),
    (2, '[0.3, 0.4, ...]'),
    ...;

-- 定期检查点，避免事务过大
SAVEPOINT batch_1000;

COMMIT;

总结

好了，本期内容就到这里，简单总结一下。

  1. 隔离级别精准选型：REPEATABLE READ是MySQL默认，但建议使用READ COMMITTED减少锁开销。
  2. AUTOCOMMIT理解透彻：START TRANSACTION只是临时禁用自动提交，不是永久开关，要区分会话级与语句级的提交控制。
  3. 死锁策略主动防御：MySQL选择最少修改行的事务作为牺牲者，应用层应设计重试机制，并配置死锁告警指标。
  4. 超时回滚严格配置：生产环境务必设置innodb_rollback_on_timeout=ON，避免部分提交问题，确保事务原子性。
  5. 锁粒度分层管理：行级锁用于并发控制，命名锁用于应用级协调，元数据锁保护表结构，三层锁机制需要分别监控。

Have a nice day ~ ☕

🌻 近期内容 ▼

👉 这里有得聊

如果对国产基础软件（操作系统、数据库、中间件）、AI、Vibe Coding、OpenClaw 、Hermes Agent 等感兴趣，可以加群一起聊聊。关注微信公众号：(少安事务所)，后台回复[群]，即可看到入口。如果这篇文章为你带来了灵感或启发，请帮忙『点赞、推荐、转发』吧，感谢！ღ( ´･ᴗ･` )~

oracle mysql
Copyright Notice

Author: Shawn Yan

Link: https://shawnyan.cn/2026/mysql/mysql-8-4-lts-transaction-pitfall-avoidance-guide/index.html

License: All articles on this site are original unless otherwise stated. Please indicate the source when reprinting!