MySQL 只读事务怎样减少误写风险

报表连接最怕的不是查询本身，而是连接池复用、公共 DAO 或临时改动把一条写语句带进了本该只读的事务。MySQL 可以用 START TRANSACTION READ ONLY 明确收紧当前事务的访问模式：对 InnoDB 来说，普通非锁定读取可以继续，写入会被拒绝并返回只读事务错误。

但 READ ONLY 不是账号权限，也不是把整个数据库切成只读。真正稳妥的组合是：事务层阻断误写，账号层只授予 SELECT，连接池层明确设置作用域并在归还连接时复位。

先把要保护的资产和误写路径说清楚

报表场景要保护的资产通常是业务表里的数据完整性，以及读连接对生产数据的低影响边界。一次误写路径可能很短：报表服务从连接池拿到连接，开启事务后调用了复用的保存方法，最后才发现这条连接本来应该只执行查询。

这里有两个不同层次的控制。READ ONLY 是当前事务的访问模式，负责在事务内阻断修改；账号权限是数据库授权边界，负责让连接即使没有正确设置事务模式，也尽量没有修改数据的能力。把两者混成一个开关，正是很多“看起来只读、实际仍能写”的根源。

选择 READ ONLY 的作用域

MySQL 的事务访问模式有不同作用域，先按报表代码的边界选，再决定写法。只保护一组明确的查询时，直接使用 START TRANSACTION READ ONLY 最容易审阅；只希望下一笔事务只读时，可以用不带 SESSION 的 SET TRANSACTION READ ONLY；如果一个专用报表会话后续事务都只读，则使用会话级设置，但必须注意连接池复用。

-- 只把一组报表查询放进显式只读事务，作用域最直观
START TRANSACTION READ ONLY;

-- 这里只放非锁定读取，避免把行锁语义带入报表事务
SELECT order_id, total_amount
FROM report_orders
WHERE report_date = '2026-10-14';

-- 查询集合完成后结束当前事务
COMMIT;

SET TRANSACTION READ ONLY 和 SET SESSION TRANSACTION READ ONLY 不应随意互换：前者设置下一笔事务，后者影响该会话后续事务。连接池中如果使用会话级配置，归还连接时要有明确的状态复位策略，否则普通业务请求可能继承报表连接的访问模式。

用 START TRANSACTION READ ONLY 收紧报表事务

对一组需要保持同一读取视角的报表查询，显式事务能让边界出现在代码附近。只读标记还给 InnoDB 一个明确的访问模式；官方手册说明，已知只读的事务可以避免一部分事务 ID 记录开销。

“只读”要求查询本身也保持非锁定语义。普通 SELECT 属于一致性读取；不要为了“确保数据最新”在同一个事务里随手改成 FOR UPDATE，那已经是锁定读取，不再是这个只读事务的适用场景。

-- 单次报表任务的事务边界：开始、读取、收口必须在同一连接上
START TRANSACTION READ ONLY;

-- 普通 SELECT 用于读取报表数据，不申请行锁
SELECT customer_id, SUM(total_amount) AS amount
FROM report_orders
WHERE report_date >= '2026-10-01'
GROUP BY customer_id;

-- 所有读取完成后提交只读事务，释放其事务状态
COMMIT;

如果报表实际上只有一条非锁定 SELECT，开启自动提交也能让 InnoDB 识别这类自动提交、非锁定、只读事务；不要为了形式上的“事务”把一个单查询请求拉成长事务。多条查询需要共享同一读取边界时，再使用显式只读事务。

哪些语句会被阻断，哪些仍是例外

显式只读事务不是“所有 SQL 都能执行但希望开发者自觉”。对 InnoDB、MyISAM 等表的修改会报 ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.，事务仍保持只读状态。这个失败是保护信号，应用应记录并结束这次报表事务，而不是在同一个连接上继续猜测。

-- 该事务只允许非锁定读取，写入语句会被 MySQL 拒绝
START TRANSACTION READ ONLY;

-- 误调用写路径时会触发只读事务错误
UPDATE report_orders
SET total_amount = total_amount + 1
WHERE order_id = 1001;

-- 失败后显式结束事务，避免把异常状态带回连接池
ROLLBACK;

锁定读取同样不应混入只读报表事务，例如 SELECT ... FOR UPDATE 或 LOCK IN SHARE MODE。另外，MySQL 允许在只读事务中修改会话专用的临时表，因为这些变化不会对其他事务可见。这是有意的例外，不应拿来写入正式业务表。

第二道防线：给报表账号最小权限

如果报表账号拥有写权限，单靠开发者记住设置 READ ONLY 并不够。授权层应从“它确实需要读哪些对象”开始，先给出最小的 SELECT 范围，再把事务访问模式作为运行时保险丝。

-- 报表账号只授予报表库的读取权限，降低配置失误后的影响面
GRANT SELECT ON report_db.* TO 'report_reader'@'%';

-- 授权完成后再由管理员按实际账号策略收紧其他权限
SHOW GRANTS FOR 'report_reader'@'%';

示例中的账号名和主机范围只是说明授权形状，生产环境应按网络边界、代理层和实际对象拆分权限。尤其不要把广泛的通配主机或高权限账号直接塞进应用配置；只读事务只能解决事务内的访问模式，不能修补一个本来就拥有写权限的账号。

连接池和复查清单

连接池让“作用域”变得更重要：数据库会话变量可能跨请求保留，而事务本身也可能因为异常路径没有正常结束。报表请求结束后，应保证事务已经 COMMIT 或 ROLLBACK，并让连接池按驱动能力复位会话状态。更简单的策略是每次报表任务都显式执行 START TRANSACTION READ ONLY，减少对持久会话状态的依赖。

• 报表连接是否使用专用账号，并只拥有必要的 SELECT 权限？
• 只读事务是否覆盖了所有查询，而不是在某个公共方法之后才开启？
• 查询中是否出现 FOR UPDATE、LOCK IN SHARE MODE 或写入语句？
• 异常路径是否总能结束事务，避免脏状态回到连接池？
• 如果使用临时表，是否明确知道它是会话级例外，并没有替代正式表的写入？

常见问题

START TRANSACTION READ ONLY 能否替代数据库只读账号？

不能。它控制当前事务的访问模式，账号权限决定连接整体能做什么；报表系统应同时使用只读事务和最小 SELECT 权限。

SET TRANSACTION READ ONLY 会影响后面所有事务吗？

不带 SESSION 时，重点是下一笔事务；会话级写法才影响该会话后续事务。连接池场景要特别防止状态跨请求遗留。

只读事务能执行 SELECT ... FOR UPDATE 吗？

不应这样设计。锁定读取不是普通非锁定读取，和显式只读事务的语义边界冲突；需要锁时应使用可写事务并重新评估权限。

为什么临时表还能写？

临时表的修改只对当前会话可见，MySQL 将其作为只读事务中的特例。它不意味着正式 InnoDB 表也可以写。