事务与安全

这一课你将能做到

1. 用 START TRANSACTION / COMMIT / ROLLBACK / SAVEPOINT 精确控制事务边界，完成转账式原子操作
2. 背出四个隔离级别与三种并发现象（脏读、不可重复读、幻读）的矩阵，并亲手复现 RR 与 RC 的差异
3. 按最小权限原则创建应用账号、用角色批量授权，说出生产账号分层的规范

为什么要学

转账扣款一半成功、库存超卖、并发下单重复发货——所有「钱和数量」类的事故都指向事务没做对。事务是后端面试的必考区且区分度极高：背出 ACID 四个词只能及格，能讲清 InnoDB 靠 undo/redo/MVCC 分别实现哪一项、RR 级别下幻读到底防没防，才是真正的分水岭。

人话：事务就是「要么全做、要么全不做」的一组操作。转账 = 扣 A + 加 B，两步必须同生共死；隔离级别决定「别人做到一半的操作，你能不能看见」。

先建立图像

转账事务的正确性要求：

  终端 A（你）                          数据库
  ─────────────────                    ─────────
  START TRANSACTION;
  UPDATE wallet 扣 lin 200   ──写入──► 改动仅自己可见（未提交）
  UPDATE wallet 加 zhao 200  ──写入──► 同上
  COMMIT;                    ──落盘──► 全世界可见 + 持久
  ─ 或 ─
  ROLLBACK;                  ──回滚──► 两步全部撤销，如同没发生

并发三现象：
  脏读       读到了别人未提交的数据          （最严重）
  不可重复读 同一事务内两次读同一行，值变了
  幻读       同一事务内两次范围查询，行数变了

跟着做

步骤 0：数据准备

-- 若第 9 课的演示表还在，先清理（它们带着指向 cf_user 的外键）
DROP TABLE IF EXISTS cf_comment, cf_feedback;

USE sql_practice;
TRUNCATE TABLE cf_submission;
TRUNCATE TABLE cf_practice_record;
TRUNCATE TABLE cf_user;
TRUNCATE TABLE cf_problem;
-- 执行第 1 课步骤 4 的四段 INSERT 恢复初始数据

CREATE TABLE IF NOT EXISTS cf_wallet (
  user_id    BIGINT UNSIGNED PRIMARY KEY,
  points     INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '积分余额',
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

TRUNCATE TABLE cf_wallet;
INSERT INTO cf_wallet VALUES (1, 1000, DEFAULT), (2, 500, DEFAULT);

lin 有 1000 积分，zhao 有 500。下文所有预期都基于此。

步骤 1：认识 autocommit

SELECT @@autocommit;   -- 1：每条语句独立成事务，执行完自动提交
UPDATE cf_wallet SET points = points + 10 WHERE user_id = 1;
-- 语句一结束，改动立刻持久，另一个会话马上可见

单条语句天然原子；多条语句想绑成一个单元，必须显式开事务。

步骤 2：终端 A 发起转账（先不提交）

打开第一个 mysql 客户端窗口（终端 A）：

START TRANSACTION;

UPDATE cf_wallet SET points = points - 200 WHERE user_id = 1;
UPDATE cf_wallet SET points = points + 200 WHERE user_id = 2;

SELECT user_id, points FROM cf_wallet;   -- 终端 A 自己看：800 / 700

步骤 3：终端 B 观察隔离性

打开第二个 mysql 客户端窗口（终端 B）：

SELECT user_id, points FROM cf_wallet;   -- 结果：1000 / 500

终端 A 明明已经改了，终端 B 读到的还是旧值——InnoDB 默认隔离级别 REPEATABLE READ 下的快照读，未提交数据对任何人不可见（杜绝脏读）。

步骤 4：两种结局

-- 终端 A 结局一：回滚
ROLLBACK;
-- 终端 B 再查：仍是 1000 / 500，两次 UPDATE 彻底消失

-- 终端 A 结局二：重新转账后提交
START TRANSACTION;
UPDATE cf_wallet SET points = points - 200 WHERE user_id = 1;
UPDATE cf_wallet SET points = points + 200 WHERE user_id = 2;
COMMIT;
-- 终端 B 再查：800 / 700，落定且持久（D）

步骤 5：SAVEPOINT——事务内的后悔药

START TRANSACTION;
UPDATE cf_wallet SET points = points - 50 WHERE user_id = 1;  -- 扣手续费
SAVEPOINT after_fee;
UPDATE cf_wallet SET points = points + 50 WHERE user_id = 2;  -- 这步写错了
ROLLBACK TO after_fee;    -- 只撤销 SAVEPOINT 之后的语句
COMMIT;                   -- 手续费的扣减保留生效

步骤 6：复现不可重复读（RC vs RR）

终端 B（切到 READ COMMITTED）：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@transaction_isolation;      -- READ-COMMITTED
START TRANSACTION;
SELECT points FROM cf_wallet WHERE user_id = 1;   -- 第一次读：800

终端 A 修改并提交：

UPDATE cf_wallet SET points = points + 100 WHERE user_id = 1;
COMMIT;

终端 B 再读：

SELECT points FROM cf_wallet WHERE user_id = 1;   -- RC 下：900（两次读不一致！）
COMMIT;

-- 把隔离级别改回默认 RR 重复一遍实验：
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT points FROM cf_wallet WHERE user_id = 1;   -- 900
-- 终端 A 再改 +100 并 COMMIT
SELECT points FROM cf_wallet WHERE user_id = 1;   -- 仍是 900（可重复读）
COMMIT;                                            -- 事务结束后再读才是 1000

RR 的事务在第一次快照读时「拍照」，之后整个事务都看这张照片；RC 每次快照读都重新拍照。

步骤 7：当前读——SELECT ... FOR UPDATE

-- 终端 A：锁定读取，打算马上扣减
START TRANSACTION;
SELECT points FROM cf_wallet WHERE user_id = 1 FOR UPDATE;  -- 1000，行锁持有中

-- 终端 B：同一条 FOR UPDATE 会阻塞等待，直到 A 提交或回滚
-- SELECT points FROM cf_wallet WHERE user_id = 1 FOR UPDATE;   ← 卡住

-- 终端 A：
UPDATE cf_wallet SET points = points - 100 WHERE user_id = 1;
COMMIT;
-- 终端 B 解除阻塞，读到 900

FOR UPDATE 是当前读（读最新已提交值并加行锁），与普通 SELECT 的快照读是两套机制——「查了再改」的竞态场景必须用它（或把判断放进一条 UPDATE ... WHERE points >= 100 里）。

步骤 8：账号与权限——最小权限原则

-- 只读报表角色与账号
CREATE ROLE IF NOT EXISTS 'r_report';
GRANT SELECT ON sql_practice.* TO 'r_report';

CREATE USER 'cf_ro'@'localhost' IDENTIFIED BY 'Cf_ro#2025pwd';
GRANT 'r_report' TO 'cf_ro'@'localhost';
SET DEFAULT ROLE 'r_report' TO 'cf_ro'@'localhost';
SHOW GRANTS FOR 'cf_ro'@'localhost';

-- 收权
REVOKE SELECT ON sql_practice.* FROM 'r_report';

生产账号分层的标准答案：应用账号只给 DML（SELECT/INSERT/UPDATE/DELETE），DDL 归 migration 账号，GRANT/SUPER 归 DBA；绝不用 root 连应用。

讲清楚要点

1. ACID 与 InnoDB 的实现对应关系（面试背熟）：原子性靠 undo log（回滚段）；持久性靠 redo log（WAL，先写日志后落数据页）；隔离性靠锁 + MVCC 多版本快照；一致性是前三者共同达成的业务结果。

2. autocommit 默认开启：单条语句自动提交；多步业务必须 START TRANSACTION。Java 里由 Spring @Transactional 帮你开，但事务边界要靠你标注在正确的层。

3. 隔离级别矩阵（能默写）：

   级别	脏读	不可重复读	幻读
   READ UNCOMMITTED	可能	可能	可能
   READ COMMITTED	不会	可能	可能
   REPEATABLE READ（InnoDB 默认）	不会	不会	基本不会*
   SERIALIZABLE	不会	不会	不会

   *InnoDB 的 RR 下，快照读靠 MVCC 不见幻影，当前读（FOR UPDATE）靠间隙锁/next-key lock 挡住插入——所以答「基本不会」并说明两套机制是高分答案。

4. 长事务是生产大敌：undo 无法清理导致版本链膨胀、持有锁引发阻塞与死锁、连接池被占满。监控 information_schema.INNODB_TRX 中 trx_started 很久的事务，该杀就杀。

5. 显式锁要精确：FOR UPDATE（排他）/ FOR SHARE（共享，8.0 新名）必须带索引条件，否则锁范围扩大；能用「带条件的 UPDATE + 影响行数判断」解决的，别先 SELECT 再改。

6. 权限按角色授、按层分：8.0 的 CREATE ROLE 让「报表只读」「应用读写」「迁移 DDL」三类权限成为可复用的帽子；账号跟着角色走，审计和收权都变简单。

你可能会懵的地方

• MySQL 为什么默认 RR 而不是业界常用的 RC？ 历史原因：早期 statement 格式的主从复制在 RC 下有数据不一致风险，RR + 间隙锁能让复制更安全。如今 row 格式复制普及，很多公司线上会改成 RC（锁更少、并发更好），面试答「默认 RR，但知道 RC 的取舍」即可。

• COMMIT 之后还能 ROLLBACK 吗？ 不能。提交即持久（redo 落盘），后悔只能靠逆向业务操作（补偿）或备份恢复。所以 COMMIT 前的检查（SELECT 确认）才是真正的后悔药。

• 事务里能跑 DDL / TRUNCATE 吗？ 语法能跑，但 DDL 与 TRUNCATE 会隐式提交当前事务——之前的 DML 全部落定，后续错误想 ROLLBACK 也回不去了。事务里禁止混 DDL。

• ERROR 1213 Deadlock 是谁的错？ InnoDB 检测到死锁环后会自动回滚代价较小的那个事务并报 1213，另一个正常执行。这不是数据库故障，是应用加锁顺序不一致——两个事务按不同顺序更新同样的行就会死锁。

• 读未提交（RU）在 InnoDB 里还有脏读吗？ 有。RU 唯一的作用是「允许读未提交」，脏读照常发生。生产几乎不用，知道即可；四个级别里只有 SERIALIZABLE 慢（读也加锁），RU 与 SERIALIZABLE 都属于「知道存在、避免使用」。

错法对照

错法	问题	正解
事务里调 HTTP/RPC	网络耗时放大事务，锁与连接被长期占用	先收集外部结果，事务内只做纯 DB 操作
事务中混 DDL/TRUNCATE	隐式提交，回滚失效	DDL 移出事务，变更走 migration 流程
FOR UPDATE 不带 WHERE	锁表级范围，全站阻塞	精确到主键/索引条件
应用直连 root	权限失控，误操作无底线	应用账号最小权限 + 角色管理
「先 SELECT 判断再 UPDATE」	并发下判断失效（快照读旧值）	原子条件更新 UPDATE ... WHERE points >= 100 或 FOR UPDATE

卡点急救

1. ERROR 1213 Deadlock found：应用层捕获后重试（这是标准协议，死锁方会被回滚）；根治靠统一加锁顺序（如都按 user_id 从小到大更新），并执行 SHOW ENGINE INNODB STATUS\G 看 LATEST DETECTED DEADLOCK 定位冲突 SQL。
2. ERROR 1205 Lock wait timeout：某事务持锁太久。SELECT * FROM information_schema.INNODB_TRX ORDER BY trx_started; 找到长事务，确认后 KILL <trx_mysql_thread_id>;，再回头修业务代码。
3. ERROR 1819 密码不满足策略：validate_password 组件要求长度/复杂度。给测试账号设带大小写、数字、符号的密码，或临时调低策略（仅本地开发）。

面试常问

• 四个隔离级别分别解决什么？InnoDB 默认哪个？ RU 防什么都不防、RC 防脏读、RR 防脏读+不可重复读（InnoDB 默认）、SERIALIZABLE 全防。InnoDB 的 RR 额外用 MVCC + next-key lock 让幻读在快照读与当前读下都基本不可见。

• 什么是 MVCC？ 多版本并发控制：每行带隐藏的事务版本信息，读操作按 ReadView 访问历史版本（undo 链），实现「读不加锁、读写不冲突」。RR 复用事务开始时的 ReadView，RC 每次读生成新 ReadView——这正是两者行为差异的根源。

• 什么是死锁？怎么避免？ 两个事务互相持有对方需要的锁形成环。避免：统一加锁顺序、缩小事务粒度、给热点行用原子 UPDATE、控制索引（避免锁范围意外扩大）。发生时 InnoDB 自动回滚代价小的一方，应用重试即可。

自测三问

1. ACID 四项分别由 InnoDB 的什么机制实现？
2. 用「拍照」的比喻说明 RC 与 RR 的差异，并写出切换隔离级别的语句。
3. 为什么「先 SELECT 再 UPDATE」在并发下不安全？两种正确写法是什么？

小结一句话

事务用原子性换业务正确性、隔离级别定并发可见性、权限用最小化守住安全——ACID 的实现机制（undo/redo/锁+MVCC）必须能对着术语讲出来。

延伸

• MySQL 8.0 Reference Manual — Transaction Isolation Levels
• MySQL 8.0 Reference Manual — GRANT Statement
• 下一课：性能优化——EXPLAIN、索引失效与慢 SQL 排查套路，把查询从「能跑」练到「飞快」。