首页
学习
活动
专区
圈层
工具
发布
社区首页 >专栏 >事务拆分只覆盖写之前的读:autocommit 与 Hint

事务拆分只覆盖写之前的读:autocommit 与 Hint

原创
作者头像
一只特立独行的兔先生
发布于 2026-11-18 15:04:01
发布于 2026-11-18 15:04:01
460
举报

应用已经连上数据库代理地址,只读实例的读权重也调高了,主实例的 CPU 和 Com_select 还是下不来。先别改权重。事务拆分默认关闭,这时事务里的读和写都打到主实例,只读实例接不到这段流量。

默认路由:事务一开始,读也回主库

代理按语句类型路由,不是按「这条 SQL 看起来像读」路由。路由表把事务外的 SELECT 划到只读实例;同一页又写明,非事务读的权重可以分给读写实例和只读实例。主实例读权重不是 0 时,事务外的读也会打到主实例,那是权重分配。下面这些明确发到主实例:INSERT / UPDATE / DELETE、DDL、SELECT FOR UPDATE、临时表相关语句、LOCK 相关语句、存储过程、last_insert_id() 和全部自定义函数、用户变量的查询和更改、用分号拼起来的多语句,以及开启事务后的语句,包括 set autocommit=0。SHOW PROCESSLIST、系统变量的 SET、USE 发到所有实例。

因此 SET autocommit=0 本身,以及它之后、提交之前的每一条 SELECT,在拆分关闭时都不会因为「是读」离开主实例。注意事项写明:没有启用事务拆分,事务请求都会路由到主实例。事务里的读不走读权重。只加只读实例、只改权重,这条路径不会分流。

语句

拆分关闭时的去向

事务外的 SELECT

按非事务读权重分配,读写实例也可以占权重

SET autocommit=0 之后、提交之前的语句

主实例

SELECT ... FOR UPDATE、用户变量、临时表、LOCK

主实例

系统变量 SET、USE

所有实例

框架把请求包进事务之后

很多服务没有手写 BEGIN。进入带事务注解的方法时,驱动会发 SET autocommit=0 或 START TRANSACTION。Spring 的 @Transactional 默认如此,readOnly = true 也一样:事务管理器仍会关掉自动提交。JDBC 的 setReadOnly(true) 不在代理路由表里,代理不会因为它改送到只读实例。Django 打开 ATOMIC_REQUESTS 时,整个请求包在一个事务里,页面上那几次查询同样走主实例。

事务拆分文档点名的就是这种框架:请求被封装进非自动提交的事务,主实例负载过大。拆分默认关闭。

两个客户端行为会让排查走偏。MySQL 命令行不加 -c,注释形式的 Hint 会被客户端丢掉,代理根本看不到。PREPARE 会先发到所有节点,后续 EXECUTE 再按语句类型路由:写语句的执行发到主库,事务外读语句的执行发到只读实例。用 PREPARE 做的探测,不能直接当成普通文本 SELECT 的路由结果。

事务拆分只挪走第一个写之前的读

事务拆分按访问地址配置,默认关闭,代理版本需要 1.3.1 或更高。云盘版如果在同一访问地址上开了网络转发,不能再开事务拆分。控制台在实例页的「数据库代理 > 访问策略」里对目标策略点设置,也可以走「数据库代理 > 概览 > 连接地址」的调整配置。

打开之后,文档承诺的范围只有一句:把事务内第一个写请求之前的读请求转发到只读实例,用来降低主实例负载。它没有说事务里每一条 SELECT 都会离开主实例,也没有说这样仍能保障事务正确性。默认把事务整段留在主实例,给出的理由就是保障事务正确性。拆分是拿这段正确性换主库负载。

扣库存时,边界落在第一个写上:

代码语言:sql
复制
START TRANSACTION;
-- 第一个写之前。拆分开启后,这条可以到只读实例,可能读到复制延迟内的旧库存
SELECT stock FROM sku WHERE id = 42;

UPDATE sku SET stock = stock - 1 WHERE id = 42 AND stock > 0;

-- 已有写请求,不在「第一个写之前」的承诺里。写后读用 Hint 钉主库
SELECT /* FORCE_MASTER */ stock FROM sku WHERE id = 42;
COMMIT;

第一条 SELECT 看到的库存,可以落后于主实例上刚提交的更新。只读实例靠 binlog 复制,延迟窗口内读到旧值是预期情况,不是拆分偶发失效。用这个旧值做条件判断,更新语句本身会到主实例,但判断依据已经过期。库存、余额、订单状态不要放在第一个写之前,也不要指望拆分顺便读到最新值。

写之后的那条 SELECT,文档没有另列规则保证它回主实例。需要读到本事务刚写的行,加上 Hint,不要靠推断。

还有一批语句,拆分文档没有说会改写路由表。用户变量的查询和更改划在主实例:SELECT * FROM sku WHERE id = @sku_id 不要交给拆分,只读实例上没有这个会话的用户变量。临时表相关语句、存储过程、SELECT ... FOR UPDATE、LOCK 相关语句、SELECT LAST_INSERT_ID() 同样留在主实例。LAST_INSERT_ID() 是会话状态,只读实例没有。用分号拼起来的多语句整段发主实例。客户端打开了 multi-statement,或者用一次 -e 把 START TRANSACTION 和 SELECT 粘成一条发出去,拆分对这段流量无效,验证也会失真。

拆开之后一致性靠 Hint,不靠事务

注意事项给出的读一致性办法是两条:把读封装进事务,或者使用 Hint。第一条只在事务拆分关闭时成立,因为这时事务请求都到主实例。拆分一开,事务开头的读就可以到只读实例,再把业务包进事务并不能钉住主库。

Hint 的路由优先级最高,不受一致性、事务约束。强制主实例用 /* to master */ 或 /*FORCE_MASTER*/,强制只读实例用 /* to slave */ 或 /*FORCE_SLAVE*/。代理版本大于等于 1.1.3 时,Hint 也支持 PREPARE。指定具体实例用 /* to server 实例ID */,实例 ID 从实例详情页取。

代码语言:sql
复制
-- 拆分已开启,仍要看到主实例上刚提交的状态
SELECT /* FORCE_MASTER */ id, status
FROM orders
WHERE id = 1001;

-- 明确可以接受延迟的列表
SELECT /* FORCE_SLAVE */ id, title
FROM articles
WHERE status = 1
ORDER BY id DESC
LIMIT 20;

命令行验证必须带 -c,否则注释到不了代理。应用侧确认驱动没有剥掉 /* ... */。Hint 可以绕过你以为还在的事务保护。误把 FORCE_SLAVE 加到更新上,语句会被送到只读实例,只读实例拒绝写入。更常见的错法是读语句加了 FORCE_SLAVE,拿旧值去做后面的更新。只加在评审过的语句上,不要做成全局拦截器。

SHOW PROCESSLIST 经代理会把所有节点的结果合并返回,不能用它判断刚才那条 SQL 落在哪台。系统变量的 SET 发到所有实例,拿 SET time_zone 的行数估计读流量,口径是错的。

按访问地址分开,而不是全局打开

访问地址数量与代理节点数相同,新增地址要求代理版本不低于 1.3.1。事务拆分是访问地址上的开关,不是实例级全局开关。订单、库存、支付走一个关闭拆分的地址,列表和详情走另一个开启拆分的地址。强读的少数语句,在开启拆分的地址上用 FORCE_MASTER。不要为了这几条把拆分整个关掉,也不要为了降负载把拆分开到所有地址。

新加或重启的只读实例,只有新连接的请求才会路由过去。验证前先重建应用连接池。概览或性能监控里如果各代理节点连接数不均衡,文档给出的处理是重新均衡负载,打散之后再看读流量,否则只读实例的 Com_select 不涨,可能只是旧连接还粘在原来的节点上。单个代理节点连接上限是 20000,超过可能造成代理连接不稳定。连接数打满和读有没有拆开是两件事。

用 Com_select 增量验证

计数直连主实例地址和只读实例地址,不要打到代理地址。代理可能把 SHOW GLOBAL STATUS 当成普通读转发,结果不能当路由证据。

代码语言:bash
复制
# 10.0.0.11 主实例,10.0.0.12 只读实例。不要换成代理地址。
mysql -h 10.0.0.11 -P 3306 -u app_ro -p -N -e "SHOW GLOBAL STATUS LIKE 'Com_select';"
mysql -h 10.0.0.12 -P 3306 -u app_ro -p -N -e "SHOW GLOBAL STATUS LIKE 'Com_select';"

记下两边的 Com_select,再从代理地址把下面三段各打 200 次。每条语句单独发送。200 次只是为了让增量盖过背景流量,业务忙时加大次数,或挑低峰做。

代码语言:python
复制
import pymysql

# 10.0.0.10 是代理地址。execute 一次一条,避免多语句被整段送到主实例。
conn = pymysql.connect(
    host="10.0.0.10",
    port=3306,
    user="app_rw",
    password="example-password",
    database="shop",
    autocommit=True,
)
cur = conn.cursor()
for _ in range(200):
    # 段 2:事务内只有读。拆分关闭时应只增加主实例 Com_select。
    cur.execute("START TRANSACTION")
    cur.execute("SELECT id, stock FROM sku WHERE id = 42")
    cur.fetchall()
    cur.execute("COMMIT")
cur.close()
conn.close()

另外两段同样逐条发。段 1 是事务外的 SELECT id FROM sku WHERE id = 42,拆分开关不影响它,按非事务读权重走。段 3 在事务里先 SELECT,再 UPDATE,最后 SELECT /* FORCE_MASTER */ stock FROM sku WHERE id = 42。命令行打 Hint 时加上 -c。

怎么读增量:拆分关闭时,段 2、段 3 的 SELECT 落在主实例,只读实例不该跟着涨。拆分开启、且主实例读权重为 0 时,段 2 的 SELECT 应出现在只读实例增量里。段 3 的 UPDATE 和带 FORCE_MASTER 的 SELECT 应落在主实例。段 1 若主实例也在涨,先看读权重,不要改拆分开关。

只读实例完全不涨,按这个顺序核对:代理版本是否达到 1.3.1,该访问地址的拆分是否打开,是否同时开了网络转发,应用连接是不是还连着旧的只读节点。都满足仍不涨,再看语句有没有撞上用户变量、临时表、FOR UPDATE 或多语句。

上线前核对

  • 代理版本不低于 1.3.1;云盘版该地址没有开网络转发。
  • 拆分按访问地址开:强一致地址关闭,可接受延迟的地址才开。
  • 事务开头的读允许看到复制延迟;写后读,以及余额、库存类判断,使用 FORCE_MASTER。
  • 用户变量、临时表、锁、存储过程、LAST_INSERT_ID() 不指望被拆走。
  • 命令行验证 Hint 带 -c;Com_select 直连实例地址,不走代理,语句逐条发。
  • 改完权重或增减只读实例后,先重建连接,再看增量。

参考文档:自动读写分离介绍、事务拆分功能、Hint 语法使用、数据库代理注意事项。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

目录
  • 默认路由:事务一开始,读也回主库
  • 框架把请求包进事务之后
  • 事务拆分只挪走第一个写之前的读
  • 拆开之后一致性靠 Hint,不靠事务
  • 按访问地址分开,而不是全局打开
  • 用 Com_select 增量验证
  • 上线前核对
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档