事务控制（TCL 实战）
以歌姬应援值转账为主线讲 BEGIN/COMMIT/ROLLBACK/SAVEPOINT：原子性演示、CHECK 拦截与回滚的分工、DDL 隐式提交铁律、双会话隔离实验
知识点地图
- 知识类别：SQL TCL / 事务控制语句（ACID 的理论在事务四特性，隔离级别深水区在隔离级别——本篇只管”四个语句怎么用对”）。
- 解决什么问题：转账要”扣钱与到账同生共死”、批量导入要”错一条回一批”、复杂流程要”只回滚一半”——这些是 BEGIN/COMMIT/ROLLBACK/SAVEPOINT 四件套的岗位。
- 什么时候用到：任何”多条写语句要么全成要么全不算”的场景；写迁移脚本；排查”我明明 ROLLBACK 了为什么没滚回去”。
- 练习环境：vocaloid 曲库（歌姬表带
points应援值字段，CHECK (points >= 0)）。
心智模型：事务是”草稿模式”
text
没有事务：每条语句一个独立事务（autocommit），写完立即生效——一步做错无法整体撤销
开启事务：BEGIN 之后进入草稿模式，改动能看能改，外人看不见
├─ COMMIT：草稿转正，永久生效
└─ ROLLBACK：草稿作废，一切如初三个默认值要先背下来：
- autocommit 默认 ON：每条语句自成事务。手动
BEGIN才能攒一批； - 事务开启后必须以 COMMIT 或 ROLLBACK 收尾——忘收尾的连接挂着事务，锁不放、快照不推进（事故形态见 PG 连接池篇的 idle in transaction）；
- 事务里不要放 DDL（原因见下文铁律一节）。
场景一：应援值转账（原子性现场）
需求：把初音未来的 200 点应援值转给洛天依。两条 UPDATE 必须同生共死——扣了没到账就是事故。
sql
-- 准备
ALTER TABLE vsinger ADD COLUMN points INT NOT NULL DEFAULT 1000
, ADD CONSTRAINT chk_points CHECK (points >= 0);
UPDATE vsinger SET points = 1000 WHERE name IN ('初音未来', '洛天依');
-- 转账
START TRANSACTION;
UPDATE vsinger SET points = points - 200 WHERE name = '初音未来';
UPDATE vsinger SET points = points + 200 WHERE name = '洛天依';
-- 中间态检查（只有本会话看得见）
SELECT name, points FROM vsinger WHERE name IN ('初音未来', '洛天依');
-- 初音未来 800 / 洛天依 1200 —— 草稿状态
COMMIT; -- 或 ROLLBACK：两条一起生效 / 一起消失逐段讲解：
START TRANSACTION（MySQL）或BEGIN（PG/标准）：从这条起 autocommit 暂停，语句进草稿；- 两条 UPDATE 之间没有先后生效：第二条失败时第一条也必须不算——这正是”转账不能拆成两个 autocommit”的原因。拆开会发生什么：第一条提交后进程崩溃，200 点凭空蒸发；
- 中间态 SELECT 是自检手段：提交前核对两边数字，养成”COMMIT 前看一眼”的习惯。
CHECK 拦截与回滚的分工——把转账金额改成 5000 试试：
sql
START TRANSACTION;
UPDATE vsinger SET points = points - 5000 WHERE name = '初音未来';
-- ERROR 3819: Check constraint 'chk_points' is violated.
-- 语句级拦截：这条 UPDATE 根本没执行，事务还开着
ROLLBACK; -- 习惯性收尾（其实这里没有可回滚的东西）分工口诀：CHECK 负责把非法语句挡在门外（语句级），ROLLBACK 负责把合法但业务上错误的改动撤销（事务级）。负余额轮不到 ROLLBACK 出场——约束先动手。反过来，“扣了 200 但转错了人”是合法数据，只有 ROLLBACK 能救。
场景二：SAVEPOINT（只回滚一半）
需求：批量导入 10 位歌姬，中途发现第 7 条数据有错，前 6 条要保留。
sql
START TRANSACTION;
INSERT INTO vsinger (name, debut_year, company) VALUES ('姬A', 2020, '社A');
INSERT INTO vsinger (name, debut_year, company) VALUES ('姬B', 2021, '社B');
SAVEPOINT after_good_batch; -- 里程碑：到此为止的都是好的
INSERT INTO vsinger (name, debut_year, company) VALUES ('坏数据', NULL, NULL);
-- 发现 debut_year 不该为空
ROLLBACK TO SAVEPOINT after_good_batch; -- 撤销坏数据，保留前两条
RELEASE SAVEPOINT after_good_batch; -- 用完删除里程碑（可选）
INSERT INTO vsinger (name, debut_year, company) VALUES ('姬C', 2022, '社C');
COMMIT; -- 姬A、姬B、姬C 入库，坏数据无痕要点：
- SAVEPOINT 是事务内的里程碑，回滚到它不结束事务——与 ROLLBACK（整个作废）是两回事；
- 里程碑可嵌套多个、可重复回滚；
RELEASE只是删除命名，不提交任何东西； - 别滥用：十几层 SAVEPOINT 的脚本说明流程设计有问题，拆成多个事务更清晰。
场景三：双会话隔离实验（亲眼看见隔离级别）
一个人也能做并发实验：开两个命令行窗口当两个用户。先确认会话都在 READ COMMITTED（或各自的默认档）：
sql
-- 会话 A（先跑，不提交）
START TRANSACTION;
SELECT points FROM vsinger WHERE name = '初音未来'; -- 读到 1000
-- 会话 B（趁 A 未提交）
UPDATE vsinger SET points = 666 WHERE name = '初音未来'; -- 立即提交生效
-- 会话 A 再读
SELECT points FROM vsinger WHERE name = '初音未来';
-- READ COMMITTED：读到 666（不可重复读发生了）
-- REPEATABLE READ（MySQL 默认）：仍读到 1000（快照读）这个实验是理解隔离级别的最快路径：同一事务里两次相同查询结果不同，就是不可重复读。换 REPEATABLE READ 再跑一遍，A 会一直读 1000 直到 COMMIT 后。实验失败排查：会话 A 忘了 BEGIN（autocommit 下每次 SELECT 都是独立事务，永远读到最新值）——九成的”我怎么复现不出来”是这个。
铁律：事务里放 DDL = 隐式提交
sql
START TRANSACTION;
UPDATE vsinger SET points = 99 WHERE name = '初音未来'; -- 打算回滚的改动
CREATE TABLE ddl_probe (id INT); -- 手滑放了 DDL
ROLLBACK;
SELECT points FROM vsinger WHERE name = '初音未来'; -- 99！没回滚原因：MySQL 的 DDL 执行前会隐式提交当前事务（DDL 自身也立即生效）。ROLLBACK 只能回滚”最后一个隐式提交之后”的语句——CREATE TABLE 之后的——UPDATE 已经转正。PG 的多数 DDL 可回滚（事务性 DDL 是它的优点），但跨方言脚本的纪律按最弱者执行：任何库的事务里都别放 DDL，混合脚本把 DDL 拆到事务外。
完整可复现实验与”改了再改回来”的写法见表结构变更的动手环节。
常见困惑
“COMMIT 之后还能反悔吗？“——不能。TCL 的撤销只到 COMMIT 为止；反悔已提交的数据是另一个话题：备份恢复（见逻辑备份与 PITR）、或补偿事务（再写一条反向 UPDATE）。
“嵌套事务是什么？“——SQL 标准没有真嵌套。SAVEPOINT 是官方的”伪嵌套”；有些客户端框架在应用层模拟嵌套（最外层才真提交）。看到框架文档说”支持嵌套事务”，先问实现是哪种。
“autocommit 关掉好不好？“——连接级 SET autocommit = 0 让所有语句进草稿直到显式 COMMIT。批处理脚本方便，但忘收尾 = 长事务 = 锁与快照灾难。宁显式 BEGIN，不长关 autocommit。
动手实践：把四个语句全部演一遍
任务：
- 复现场景一的转账，分别以 COMMIT 和 ROLLBACK 收尾，核对两次结果差异；
- 制造”扣 5000”的 CHECK 拦截，确认事务状态仍可继续使用（再执行别的语句试试）；
- 用 SAVEPOINT 搭一个”导入 5 行、第 3 行错、重导第 3 行”的流程；
- 复现 DDL 隐式提交实验，再用”DDL 放事务外”的写法修正它；
- 双会话实验：READ COMMITTED 下复现不可重复读；切 REPEATABLE READ 再复现一次对比。
参考实现（先自己写再展开）
sql
-- 1
START TRANSACTION;
UPDATE vsinger SET points = points - 200 WHERE name = '初音未来';
UPDATE vsinger SET points = points + 200 WHERE name = '洛天依';
COMMIT; -- 查两边 800/1200
-- 重置后重来一次改用 ROLLBACK，两边都回到 1000
-- 2
START TRANSACTION;
UPDATE vsinger SET points = points - 5000 WHERE name = '初音未来'; -- ERROR 3819
UPDATE vsinger SET points = points + 1 WHERE name = '洛天依'; -- 正常，事务没死
COMMIT; -- 只有 +1 生效
-- 3
START TRANSACTION;
INSERT INTO vsinger (name, debut_year, company) VALUES ('T1', 2020, 'S');
INSERT INTO vsinger (name, debut_year, company) VALUES ('T2', 2021, 'S');
SAVEPOINT sp1;
INSERT INTO vsinger (name, debut_year, company) VALUES ('T3bad', NULL, 'S');
ROLLBACK TO SAVEPOINT sp1;
INSERT INTO vsinger (name, debut_year, company) VALUES ('T3', 2022, 'S');
COMMIT;
-- 5（会话隔离实验正文原样执行；切级别）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;判读要点：任务 2 说明语句报错不自动回滚整个事务（MySQL 默认），事务继续可用——这与某些框架”抛异常即回滚”的应用层行为不同，别混淆。任务 5 的两档差异要能一句话说出：读到的值变没变。
检验清单
- 能说出 autocommit 默认值与”忘收尾事务”的两种后果；
- 能用转账场景讲清原子性，并解释”拆成两条 autocommit 哪里会坏”；
- 能区分 CHECK 拦截（语句级）与 ROLLBACK（事务级）的分工；
- 会用 SAVEPOINT 做局部回滚，并知道它与 ROLLBACK 的区别；
- 能复现 DDL 隐式提交实验并给出规避写法；
- 能搭双会话实验复现不可重复读。
下一步
- 事务 ACID 四特性：本篇操作背后的理论保证；
- 隔离级别：双会话实验的完整版（三类读异常）；
- 锁机制：事务之间互相挡住的细节。
参考与致谢
- MySQL 8.0 官方文档 Chapter 15.7 Transaction Statements（GPLv2 文档许可）：https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html
- PostgreSQL 官方文档 Chapter 9.19 Transaction（PostgreSQL Licence）：https://www.postgresql.org/docs/current/tutorial-transactions.html
- 应援值转账场景取自仓库扫描素材（vocaloid 笔记 05-TCL）并重写扩充。
提交的保证
COMMIT 成功后，数据库保证：
- 变更已写入重做日志（redo log / WAL）
- 即使系统崩溃，变更也不会丢失
- 其他事务可以看到这些变更
链式提交
sql
-- PostgreSQL：COMMIT AND CHAIN 自动开始新事务
COMMIT AND CHAIN;
-- 等价于 COMMIT; BEGIN;隐式回滚
sql
-- 以下情况事务自动回滚：
-- 1. 连接断开
-- 2. 语句执行错误（部分数据库）
-- 3. 死锁被选中牺牲
-- PostgreSQL：错误后事务进入 aborted 状态
BEGIN;
INSERT INTO orders VALUES (1, 99.99);
INSERT INTO orders VALUES ('invalid', 99.99); -- 类型错误
-- 事务进入 aborted 状态，后续语句都被忽略
INSERT INTO orders VALUES (2, 49.99); -- 被忽略
ROLLBACK; -- 必须显式回滚多级保存点
sql
BEGIN;
SAVEPOINT level1;
INSERT INTO table_a VALUES (1);
SAVEPOINT level2;
INSERT INTO table_b VALUES (2);
SAVEPOINT level3;
INSERT INTO table_c VALUES (3);
-- 回滚到 level2，level3 的变更被撤销
ROLLBACK TO SAVEPOINT level2;
-- level1 和 level2 的变更仍然保留
COMMIT;SQL Server 嵌套事务
sql
-- SQL Server 支持嵌套 BEGIN TRANSACTION
BEGIN TRANSACTION outer;
INSERT INTO table_a VALUES (1);
BEGIN TRANSACTION inner;
INSERT INTO table_b VALUES (2);
COMMIT TRANSACTION inner; -- 减少嵌套计数
COMMIT TRANSACTION outer; -- 真正提交