MySQL START TRANSACTION READ ONLY 怎样限制事务写入

如果一段报表查询只应该读取数据，可以在事务开始时明确写出 START TRANSACTION READ ONLY。它限制的是当前事务对永久表的访问模式：普通非锁定查询可以执行，尝试写入永久表时会返回只读事务错误，而不是悄悄把语句当成成功。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/innodb-performance-ro-txn.html

要验证“怎样限制事务写入”，关键是把 READ ONLY 放在事务入口，并用一条故意写入永久表的测试语句确认错误分支；最后无论查询是否报错，都要显式结束事务。

先用 READ ONLY 固定本次事务的访问模式

MySQL 默认事务是可读写的。下面的写法把访问模式绑定到当前事务，不会把服务器或整个连接池永久变成只读：

-- 只读事务从入口声明，便于让错误尽早暴露
START TRANSACTION READ ONLY;

-- 报表查询可以读取永久表
SELECT id, total_amount
FROM orders
WHERE created_at >= '2026-11-01';

-- 读完后显式结束本次事务
COMMIT;

对 InnoDB 来说，明确的只读事务还可以避免为不会写入的事务创建部分事务 ID 记账。它适合把多条相互关联的查询放在同一个一致性视图中，但不是“查询越多越快”的保证，索引、锁等待和结果集大小仍要单独处理。

用查询和写入分别确认限制效果

测试时不要只执行一条 SELECT 就认为限制生效。可以在测试表上依次验证读取和写入，预期写入永久表会失败，常见错误是 ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.：

-- 先准备一个最小测试表，测试完成后可回滚本次事务
START TRANSACTION READ ONLY;

-- 普通非锁定 SELECT 允许执行
SELECT id, status FROM orders WHERE id = 1001;

-- 这条 INSERT 应被拒绝，调用方应记录并处理错误
INSERT INTO orders(id, status) VALUES (1002, 'pending');

-- 错误后仍要结束事务，避免连接复用脏状态
ROLLBACK;

UPDATE、DELETE 以及对永久表的 DDL 都不应在这个事务里安排。另一个容易误判的情况是锁定读：SELECT ... FOR UPDATE 的意图本身就是为后续修改取得锁，不应当把它当成普通只读报表查询。

区分永久表、临时表和 DDL 边界

READ ONLY 不是实例级开关，也不等于 --innodb-read-only。它只描述当前事务的访问模式。MySQL 文档允许在只读事务中修改当前会话创建的临时表，因为临时表内容不会被其他事务看到：

-- 临时表只属于当前会话，可用于整理报表中间结果
START TRANSACTION READ ONLY;
CREATE TEMPORARY TABLE report_stage (id INT, amount DECIMAL(12, 2));

-- 临时表的写入不等同于修改永久业务表
INSERT INTO report_stage VALUES (1, 19.90);
SELECT * FROM report_stage;

-- 结束事务，同时按需清理临时表
ROLLBACK;
DROP TEMPORARY TABLE report_stage;

因此，若目标是阻止某个账号或实例的所有结构变更，应该使用权限、只读副本或实例级配置来设计，不能只靠 START TRANSACTION READ ONLY。在应用层，它更适合做“这次工作单元只允许读”的防误写护栏。

把 COMMIT、ROLLBACK 和连接复用写进代码

连接池场景要把事务生命周期和异常处理放在同一个函数里。伪代码可以表达清晰的边界：开启只读事务，查询失败就回滚，全部成功才提交；不要把已经开启的事务交给下一次请求。

func loadReport(ctx context.Context, db *sql.DB) ([]Row, error) {
    // TxOptions 只影响本次事务，避免污染连接池的后续请求
    tx, err := db.BeginTx(ctx, &sql.TxOptions{ReadOnly: true})
    if err != nil {
        return nil, err
    }
    // 默认按失败路径回滚；提交成功后回滚只会返回已结束提示，可忽略
    committed := false
    defer func() {
        if !committed {
            _ = tx.Rollback() // 错误、取消或提前返回时释放事务
        }
    }()

    rows, err := queryRows(ctx, tx)
    if err != nil {
        return nil, err
    }
    if err := tx.Commit(); err != nil {
        return nil, err
    }
    committed = true
    return rows, nil
}

不同驱动可能把 ReadOnly: true 翻译成对应的事务属性，也可能在驱动侧有差异；如果必须保证 SQL 入口一致，可在连接上显式执行 START TRANSACTION READ ONLY，并为错误码建立测试。重点是事务结束动作必须成对出现，尤其是连接池复用时。

常见问题

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

不应把它当成普通只读查询。锁定读要求为后续写入保留锁，和 READ ONLY 的访问意图冲突；报告查询应使用非锁定 SELECT。

READ ONLY 会阻止 CREATE TEMPORARY TABLE 吗？

当前会话的临时表属于例外范围，可以创建并修改临时表；永久表的数据和结构变更仍应被拒绝。

执行 INSERT 报错后还能继续用同一个事务吗？

应用应把它视为失败分支，记录错误并回滚结束事务。不要把错误后的连接直接交还给连接池，避免后续请求继承未清理的事务状态。

小结

MySQL START TRANSACTION READ ONLY 的限制点是“当前事务对永久表不允许写入”，不是全局只读。把声明放在入口，用一条写入测试确认约束，区分临时表例外，并在应用代码中保证提交、回滚和连接复用边界，才能让只读事务真正成为可靠的防误写方案。