2.3 处方权与疗程：DCL与事务语句

2.3 处方权与疗程：DCL 与事务语句

本节摘要：GRANT/REVOKE 管的是"谁有处方权"，事务语句管的是"一个疗程必须完整"。本节给出最小权限的授权模板与事务提交回滚的标准动作，为第 5 章并发控制打底。

处方权：按科室授权

-- 只读账号：数据分析、报表人员
CREATE USER 'analyst'@'%' IDENTIFIED BY 'Ana#2026Key';
GRANT SELECT ON clinic.* TO 'analyst'@'%';

-- 值班开发：能改业务表，不能动结构、不能动权限
GRANT SELECT, INSERT, UPDATE, DELETE ON clinic.* TO 'oncall'@'10.0.%';

-- 收回权限立即生效
REVOKE DELETE ON clinic.* FROM 'oncall'@'10.0.%';
​

授权粒度从粗到细：全局、库、表、列。诊断室原则是"默认无权，用到再给"，GRANT ALL ON *.* 这种万能钥匙只留给本地练习环境。

疗程：事务的标准动作

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE user_id = 1001;
UPDATE account SET balance = balance + 100 WHERE user_id = 2002;
-- 中间任何一步异常：
-- ROLLBACK;
COMMIT;
​

转账的两条 UPDATE 必须同生共死，这就是事务的原子性。两个实用补充：

SET autocommit = 0;          -- 关闭自动提交，每条语句都要手动 COMMIT
SAVEPOINT before_discount;   -- 打存档点
ROLLBACK TO SAVEPOINT before_discount;  -- 只回滚到存档，不放弃整个事务
​

⚠️ 常见坑：autocommit 关掉后忘了 COMMIT 就断开连接，长事务悄悄持有锁和旧版本数据，拖垮后面第 5 章要讲的 MVCC。用完即提交，事务里不要塞慢操作。

图：一次事务的生命周期

图：授权粒度金字塔

权限的核对与体检

授权不是发完就完事，定期体检才能发现"权限腐化"——人员调动、项目下线后账号成了无人认领的万能钥匙：

-- 谁的权限最大：列出有全局授权的账号
SELECT user, host FROM mysql.user WHERE super_priv = 'Y';

-- 某个账号到底有哪些权限，逐级查看
SHOW GRANTS FOR 'oncall'@'10.0.1.%';

-- 找出最近 90 天没连接过的账号（需连接数据支撑，或用审计日志）
SELECT user, host FROM mysql.user;
​

账号清理的两条纪律：收回权限与删除账号要区分，REVOKE 留人去权，DROP USER 连人带权一起清；离职流程里"收号"必须排在交接清单第一位，晚一天都是敞着大门过夜。

事务的隔离与锁的初次照面

第 2 章只需要建立两个直观感受，深挖留给第 5 章。感受一，事务的边界天然是连接级的：autocommit 开着时每条语句自成一个事务；关掉后从上一条 COMMIT 到下一次 COMMIT 之间全算一个事务，中间夹着一条 SELECT 也算——这就是"事务悄悄变长"的常见起点。

感受二，写写相遇必有锁。两个会话同时 UPDATE 同一行，后到者要等先到者提交或回滚：

-- 会话A
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE user_id = 1001;
-- 未提交

-- 会话B：卡住，直到 A 提交或超时
UPDATE account SET balance = balance - 50 WHERE user_id = 1001;
-- ERROR 1205: Lock wait timeout exceeded（默认等 50 秒）
​

这个 50 秒来自 innodb_lock_wait_timeout，调小它能让故障快速暴露而不是慢慢拖垮全站——但调参只是止痛，真正要做的是让事务短到不该撞车。这些直觉在 5.3 节的急诊案例里会变成完整的排查流程。

一张 DCL 速查卡

把常用权限动词按危险等级排一下，授权时对号入座：SELECT 只读最安全；INSERT/UPDATE/DELETE 是业务标配，注意 DELETE 配合 WHERE 纪律；INDEX/ALTER 属于变更权限，应用账号不该有；DROP/TRUNCATE/CREATE 属于结构级，只给 DBA；GRANT OPTION 是"转授权限的权限"，给出去等于交出科室钥匙；SUPER/PROCESS/REPLICATION 能绕过常规约束，等闲不给。这张卡不必背，授权前扫一眼，多数越权事故就不会发生。

隐式提交：事务里的暗门

有些语句会悄悄把当前事务提交掉，这扇暗门叫隐式提交。DDL 是最常见的触发者：事务跑到一半执行一条 ALTER，前面的未提交修改会被静默提交，回滚从此无从谈起。LOAD DATA、部分账号管理语句也有同样效果：

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE user_id = 1001;
ALTER TABLE account ADD COLUMN memo VARCHAR(100);  -- 前面那条 UPDATE 已被隐式提交
ROLLBACK;  -- 挽回不了 UPDATE 了
​

处方只有一条：事务里只放 DML。DDL 与事务混编的代码，等于在每个事务里埋了一颗不定时提交雷，排查时表现为"回滚没生效"，日志里毫无异常。

只读事务也有价值

事务不只为了回滚，一致性快照本身就是产品能力。对账任务要读五张表，读的过程中业务还在写入，五张表读到的就不是同一时刻的状态。把它们包进一个事务（或 START TRANSACTION READ ONLY），快照定格，读到的天然自洽：

START TRANSACTION READ ONLY;
SELECT SUM(amount) FROM clinic_order WHERE created_at < '2026-08-01';
SELECT COUNT(*) FROM clinic_user WHERE created_at < '2026-08-01';
-- 两条数字基于同一快照，比值才有意义
COMMIT;
​

这个手法在第 5 章 MVCC 里会得到机理层面的解释，此刻先记住使用场景：多处读取需要一个统一的时间戳时，用事务把时间冻结住。

本节要点回顾

• 最小权限：按角色给 SELECT/DML，拒绝万能钥匙
• 事务四步：开启、执行、提交或回滚，异常路径先想好
• 短事务：事务内不做慢查询，autocommit 关闭要自律