线上突然接到业务告警订单状态批量更新失败率超过 50%。登录 MySQL 看了半天Group ReplicationMGR三节点全部 ONLINECPU、内存、磁盘全部正常复制距离也没异常但业务日志里到处都是死锁、锁等待超时客户端持续报更新失败。如果你也遇到过这种“集群看着没毛病、业务却写不进去”的诡异故障那这篇故障复盘应该能帮你省下不少排查时间。我这次要聊的是一个基于 MySQL 8.0 搭建的三节点 MGR 多主模式集群承载订单状态流转和库存扣减场景。整个故障窗口大概持续四十分钟最终定位到的是多主模式下非常典型的写写冲突与认证失败问题。文章会按“现象还原 - 排查路径 - 根因机制 - 应急与根治 - 监控预防”的顺序展开既有 SQL 也有参数尽量给到可以直接参考的排查思路。1. 故障现象三节点 ONLINE业务却写不进去1.1 集群拓扑与现场信息先说下背景。这套环境是某公司核心交易库的一部分一共三个节点跑的是 MySQL 8.0MGR 采用多主模式也就是每个节点都可以接受写入。应用侧通过中间件做了读写分离正常情况下写请求会被分发到不同节点比如订单创建走 node-a订单状态更新走 node-b库存扣减走 node-c这样做的初衷是分散写入压力提高吞吐。故障发生在某天下午的业务高峰期持续约四十分钟。业务方反馈两个核心接口开始出现大量报错一个是订单状态更新接口报错内容是“Lock wait timeout exceeded”另一个是库存扣减接口报错内容直接是“Deadlock found when trying to get lock; try restarting transaction”。两个接口都集中在 UPDATE 和 DELETE 操作上INSERT 反而很少有问题。当时我第一反应是数据库负载被打满了或者某个节点出现了磁盘/内存瓶颈。但从监控平台看三个节点的 CPU、内存、磁盘 IO、网络流量都处于正常水位InnoDB 的 buffer pool 命中率也稳定在 99% 左右。这就有点反常了——如果是资源瓶颈监控多少会露出点迹象但这次表面完全是“风平浪静”。1.2 客户端实际看到的报错是什么样为了还原问题我让业务方把客户端日志打出来发现报错并不是单一类型主要集中在下面几种报错信息错误码出现频率Lock wait timeout exceeded; try restarting transactionERROR 1205高Deadlock found when trying to get lock; try restarting transactionERROR 1213中The transaction has been rolled back because it conflicted with another transactionERROR 3098低Cant execute the given command because you have active locked tables or transactionsERROR 1123偶发这几种报错混合出现很多 DBA 看到 1205 和 1213 会先往 InnoDB 锁竞争上排查比如长事务、大事务、行锁太多。这也是这次故障迷惑性的来源你往锁方向查能查到一些行锁等待记录但根因根本不是普通锁竞争而是 MGR 多主认证冲突把事务直接终止了客户端感知到的“锁”其实是冲突检测机制的副作用。1.3 为什么先排除硬资源问题排查初期我做了几件比较常规的事先确认不是基础设施问题看系统负载三个节点uptime的负载都远低于 CPU 核数没有拍到打满的迹象。看磁盘与 IOiostat里 %util 没有长期超过 60%redo log 写入没有明显排队。看网络节点之间的 RPC 延迟和丢包率正常没有出现网络分区。看慢日志确实出现了一批慢 SQL但单条 SQL 执行时间不超过两秒和“大面积超时”对不上。这些检查的价值在于快速划定边界。如果网络有抖动或者磁盘有延迟MGR 的选主和同步机制也会表现异常但表现方式会和这次完全不同——通常会出现成员状态异常、节点退出集群等。而这次所有成员都稳定 ONLINE说明基础层和 MGR 通信层大概率没有硬故障。那问题就只能出现在事务执行或认证层面。2. 初步排查MGR 监控指标里藏着答案2.1 成员状态全部 ONLINE不代表集群健康很多人排查 MGR 第一步是看performance_schema.replication_group_members看到三个节点都是 ONLINE 就觉得集群没问题。这个认知需要修正一下。replication_group_members反映的只是组成员和通信状态ONLINE 只能说明这个节点还活着、还在参与组通信但完全不能反映这个节点的复制线程是否健康、认证是否在失败、流控是否在生效。我这次也是先查了这个视图SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members;结果三个节点全部 ONLINE两个 PRIMARY一个 SECONDARY多主模式下角色其实都是 PRIMARY这里显示 PRIMARY 表示可写。单看这个结果很多人就会得出结论“数据库没问题是应用的问题”。但业务真实报错摆在面前不能就这样交差。2.2 真正的线索在 replication_group_member_stats接着我查了另一个容易被忽略的表performance_schema.replication_group_member_stats。这张表记录了每个成员的事务统计信息是定位 MGR 写冲突的核心视图。SELECT MEMBER_ID, COUNT_TRANSACTIONS_IN_QUEUE, COUNT_TRANSACTIONS_CONFLICTS, COUNT_TRANSACTIONS_LOCAL_PROPOSED, COUNT_TRANSACTIONS_LOCAL_ROLLBACK FROM performance_schema.replication_group_member_stats;查询结果让我一下子锁定了方向节点事务队列认证冲突数本地提议事务数本地回滚事务数node-a013215800130node-b011814900116node-c014517100143正常健康的多主集群COUNT_TRANSACTIONS_CONFLICTS应该长期保持在一个极低水平可能一天也就几十个。而现在一个小时内积累了上百个冲突且每个本地回滚事务数都对应着一次冲突。这说明集群内部正在频繁发生事务互相“打架”的情况。这里要说明一下COUNT_TRANSACTIONS_CONFLICTS是累计值所以排查时不能只看当前大小要连续采样两次计算差值看单位时间内的增长速率。我当时隔了 30 秒查了两次确认冲突数仍在持续快速上涨这就确定了问题还在进行中而不是历史遗留的统计值。2.3 错误日志和 InnoDB 状态的双重佐证接下来我去翻了三个节点的 MySQL error log找到了不少组复制层面的报错类型大致是[ERROR] [MY-011712] Plugin group_replication reported: Transaction ... failed certification with conflict这类日志直接证实了认证失败的存在。然后我又在其中一个节点执行了SHOW ENGINE INNODB STATUS\G看了 LATEST DETECTED DEADLOCK 部分发现两个事务确实是围绕同一行订单记录互相持锁等待看起来像是典型的行锁死锁但结合组复制日志其实背后的逻辑是事务 A 在 node-a 提交并广播写集合事务 B 在 node-b 提交并广播写集合两个事务修改了同一行数据MGR 的认证器判定冲突后提交的那个事务被回滚。也就是说InnoDB 层看到的“死锁”是表现组复制层的“认证冲突”才是根因。如果只盯着 InnoDB 死锁信息去优化索引、拆分事务问题大概率还会二进宫。3. 根因分析多主模式的写写冲突与认证机制3.1 MGR 认证机制到底做了什么要理解这次故障必须先把 MGR 的认证机制讲透。MGR 和传统主从复制最大的区别在于事务不仅要写本地还要在组内达成共识。具体流程大致是应用在某个节点上执行事务事务先在本地 InnoDB 中写入。事务提交时节点会把这个事务的二进制日志内容连同“写集合”一起广播到组内。组内所有节点对该事务执行认证certification判断它和其他已经提交或正在提交的事务是否修改了同一行数据。如果认证通过事务可以提交如果认证失败事务直接被回滚。这里的“写集合”是 MGR 用来判断冲突的核心。它通过主键把事务涉及的行映射成一组哈希值。两个事务只要写集合有交集就判定为冲突。这也是为什么 MGR 强制要求表必须有主键——没有主键就没法稳定计算行哈希认证阶段直接无法工作。用个生活化的类比多主模式就像三个人同时编辑同一份在线文档你改了第 3 段的文字另一个人也改了第 3 段后保存的那个人会收到提示“文档已被修改”他的更改会被丢弃。MGR 的认证器就是那个文档协作系统“你不能更新了”本质上是系统在保护数据一致性防止两个节点对同一行数据做出互相覆盖的更改。3.2 哪些场景最容易触发认证冲突多主模式不是所有操作都会冲突实际上 INSERT 的冲突率远低于 UPDATE 和 DELETE。容易触发认证冲突的场景往往具有以下特征热点行并发更新比如同一张订单记录被多个节点同时修改库存表同一行被多个服务扣减这种是最高频的冲突源。业务逻辑是“先查再改”应用先 SELECT 出订单状态然后在业务代码里判断状态再执行 UPDATE。两次操作之间如果另一个节点也改了同一行认证冲突概率极高。更新条件不是主键如果 UPDATE 语句的 WHERE 条件走的是普通二级索引或者没索引MGR 认证时需要扫描更多行来判断写集合交集冲突检测的粒度更粗容易被误伤。长事务和大事务事务持续时间越长和别的事务产生写集合交集的机会就越大。批量 UPDATE 扫过大量行也会放大写集合范围。表没有主键这会直接导致认证失败或复制报错严格说是“不能进入认证环节”而不是冲突问题但表现同样是数据不能更新。3.3 本次故障的具体触发链路回到这次案例。最终定位到的根因有三层逐层递进第一层业务侧存在“读改写”的并发模式。两个应用实例通过中间件把同一批订单的更新请求分发到了不同 MGR 节点。比如订单号 10001 的状态更新请求一部分发到 node-a一部分发到 node-b。两个节点同时读取订单状态、判断是否允许流转、然后执行 UPDATE天然形成了写集合交集。第二层某些表的 WHERE 条件走的是非主键索引。订单状态更新用了订单状态扫描条件一条 UPDATE 可能触碰几十行写集合被放大。原本只改一行可能还不至于频繁冲突一改几十行冲突概率呈几何级增长。第三层也是最隐蔽的一层集群里有一张历史遗留的明细表没有主键只有二级索引。这张表平时写入量不大但 MGR 对无主键表的处理非常严格一旦有事务包含对这张表的修改认证器就无法生成稳定的行哈希事务在组内广播时直接被拒绝。业务更新订单时联表操作会顺带修改这张明细表导致整个事务不可用。三层原因叠加最终表现就是“多主数据不能更新”。第一层和第二层是诱因第三层是放大器把偶发冲突变成了大面积故障。4. 处理过程与最终修复4.1 应急处理先把业务恢复起来故障发生时首先要做的不是分析根因而是恢复业务可用性。我当时的应急动作按顺序执行通知应用团队开启事务重试机制死锁和 1205 错误属于可重试错误应用层设置最多重试三次间隔 200 毫秒。这一步立竿见影很多偶发写入失败被自动消化掉了。将批量订单更新任务从高峰期摘除改到业务低峰期执行。批量任务是大写集合的主要生产者摘掉后冲突数立刻下降。对那几张热点表临时启用“同一行数据路由到固定节点”的策略。中间件层面配置写路由规则比如订单状态更新统一只发到 node-a库存扣减统一只发到 node-b错开写集合交集。这里要特别提醒一个容易做错的动作不要因为害怕冲突就随便重启节点。有些 DBA 遇到 MGR 异常会本能地执行STOP GROUP_REPLICATION再START GROUP_REPLICATION希望通过重新加入集群来触发状态重置。但在冲突密集的窗口内重启节点反而可能因为恢复同步时积压大量事务而加剧流控甚至导致该节点一直卡在 RECOVERING 状态。重启操作只适用于节点完全卡死或成员状态异常的场合不要作为常规应急手段。加急操作大约十五分钟后业务报错量明显回落从 50% 降到 5% 以下。业务侧算是缓过来了接下来才是治本。4.2 业务代码层改造消除热点行冲突应急只是把问题压下去了不改造业务模式冲突迟早还会爆发。业务侧的根治措施我总结成三条引入乐观锁。在订单表增加版本号字段UPDATE 语句改为UPDATE orders SET status 已完成, version version 1 WHERE id ? AND version ?。这样即使两个节点同时尝试更新同一行只有一个事务的版本号能匹配成功另一个直接更新 0 行不会进入认证冲突。拆分“读改写”事务。尽量把查询状态和更新状态拆成两步独立操作缩短事务持有写集合的时间。如果必须保持原子性把事务体量压缩到最小不在一个事务里同时更新订单主表和明细表。按业务做写路由。多主模式下不同业务模块的写入尽量只在固定节点执行比如订单模块写入只走 node-a库存模块写入只走 node-b。这不会影响 MGR 的多主能力但能大幅降低跨节点写同一行的概率。这次踩坑后我把“多主模式必须有冲突防控设计”固化成了团队开发规范。多主不是问题问题是用多主的方式去写单行热点数据这在架构上就是拧巴的。4.3 参数与配置层调整代码改造完成后我又对数据库参数做了一轮针对性调整主要围绕三个方面调整流控参数。MGR 自带流控机制flow control当某个节点的事务应用队列积压时会反过来限制所有成员的事务写入避免落后节点积压过大。但流控生效时写操作会出现延迟甚至超时容易被误判为“不能更新”。我这次把group_replication_flow_control_mode从默认的 QUOTA 调整成了 DISABLED先消除了流控带来的额外延迟。这里必须说明生产环境长跑不建议一直 DISABLED它会让落后节点失去保护。正确做法是保持流控开启但把group_replication_flow_control_min_quota和group_replication_flow_control_max_quota调大给瞬时写入高峰留出缓冲空间。调整锁等待超时。innodb_lock_wait_timeout默认是 50 秒在 MGR 冲突场景下锁等待超时很容易被当成普通锁问题。我把这个参数调低到 10 秒让应用更快地感知到事务失败并重试而不是长时间挂起占住连接。调整事务大小限制。group_replication_transaction_size_limit是 MGR 对单个事务大小的硬限制超过限制的事务会直接失败。我检查后发现默认配置能覆盖日常业务暂时不用调整但确认了批量任务拆分到事务内的行数避免单事务过大。参数调整不是万能的它更多是给业务改造争取时间。真正解决问题的永远是业务侧对写集合的控制。4.4 给历史表补主键这次故障的根源修复这次故障的最终引爆点是那张无主键的明细表。处理完业务代码后我专门花了一个周末把集群里所有无主键表全部梳理出来逐一补主键。补主键本身不复杂但在线执行有坑。MySQL 8.0 支持ALTER TABLE ... ADD PRIMARY KEY用的算法是 INPLACE但大表执行时依然会产生大量 redo 和内部临时文件。我当时的操作步骤是先在备节点上执行ALTER TABLE观察执行时间和产生的临时文件大小。确认无异常后选择业务低峰期在主节点执行并在执行前调整lock_wait_timeout避免 DDL 语句被事务阻塞。优先选用业务自然键或自增列作为主键如果表本身有逻辑唯一字段就直接用没有业务主键的加一个自增列并用ALTER TABLE ... AUTO_INCREMENT初始值避免后续主键冲突。执行完成后检查performance_schema.replication_group_member_stats确认认证冲突数不再上涨。这里也提醒一句MGR 环境里给大表加主键前最好先确认group_replication_consistency的配置必要时在低峰期临时调整为EVENTUAL减少 DDL 对组内其他节点的影响。我这次是在维护窗口内操作的没有遇到异常但提前做足检查总是对的。5. 监控与预防别等报错再上线排查5.1 上线前必须过一遍的检查项经历过这次故障后我把 MGR 多主集群的上线前检查清单重新整理了一遍每一条都是拿教训换来的全库表必须都有主键或非空的唯一键这是硬性要求没有加主键的表不允许接入 MGR。写集合冲突压测要做真实并发只测单节点写入看不出问题至少模拟两个节点同时写同一批热点行的场景。业务规范里明确写清“读改写”事务的边界禁止在同一个事务里做无关行的批量更新。确认performance_schema开启replication_group_member_stats和replication_group_members这些视图才能正常采集监控数据才不会缺失。提前把group_replication_flow_control_mode、group_replication_transaction_size_limit、innodb_lock_wait_timeout这些参数按业务峰值压测结果固化到配置模板里。这套检查清单现在直接挂在我们部门的数据库上线流程里新业务接入 MGR 前必须逐项确认确认不了就用单主模式。5.2 监控指标与告警设置建议MGR 的监控不能只看节点状态和主从延迟更要看认证冲突和流控指标。我目前保留的监控项和阈值如下监控项数据来源告警阈值成员状态replication_group_members非 ONLINE 立即告警认证冲突数增长率replication_group_member_stats30 秒内增长超过 20 次告警事务队列积压数replication_group_member_stats持续超过 30 秒大于 0 告警节点应用线程异常replication_applier_status线程停止即告警流控触发次数错误日志关键字5 分钟内触发 3 次以上告警特别提一下认证冲突数这个指标日常多主集群也可能会有一两次冲突但 30 秒增长 20 次已经是明显的异常信号足以触发人工介入。设置这个告警之前很多 MGR 写冲突类问题都是业务先发现DBA 才被动介入有了告警后我们基本能在业务感知前就收到通知。5.3 多主模式的适用性反思这次故障之后我一直在想MGR 多主模式到底适合什么场景后来得出的结论很明确多主模式更适合“多写少冲”的业务模型比如不同业务模块写不同的库表或者同一个业务的数据天然按维度拆分比如按租户、按地域分片。如果业务存在明显的单行热点写入比如订单状态频繁更新、库存扣减、账户余额变动那么多主模式会频繁触发认证冲突不仅没享受到多写带来的吞吐提升反而额外背负冲突回滚和重试的开销。这种情况下单主模式加读写分离反而更合适。MGR 同样支持单主模式写入全部走主节点不存在跨节点写单行的问题冲突数为零架构还更简单。当初这套系统选多主是因为业务团队希望充分利用三台服务器多主似乎更“高级”但实际运行下来多主带来的额外复杂度远大于收益。当然如果业务层已经做好了写路由不同数据域固定走不同节点多主模式也能跑得很稳定。关键是架构选型必须匹配业务特征而不是为了技术而技术。最后再分享一个小技巧MGR 排查写冲突类故障时别急着看业务代码先查replication_group_member_stats里的COUNT_TRANSACTIONS_CONFLICTS增长速率再结合 error log 里的 certification 相关日志基本五分钟内就能确定是不是认证冲突。方向对了剩下的就是业务改造和参数调优方向错了半夜起来查锁等待只会越查越乱。这套排查路径我验证过多次越用越顺手。
