14.5.4 事务一致性与强制主库策略

事务一致性与强制主库策略的目标是在读写分离下确保事务内读到最新数据。ProxySQL通过事务识别与规则优先级，把事务内语句强制路由到写库 hostgroup，避免从库延迟导致读到旧数据。

原理草图（事务内强制主库）：

文章图片

关键场景与风险：
- 事务内先写后读：读走从库会读到旧数据。
- 显式锁/一致性读：SELECT ... FOR UPDATE 必须走主库。
- 长事务：从库延迟扩大，读一致性风险提高。
- autocommit=0：隐式事务导致读写误路由。

一、规则设计与配置示例
目标：事务内强制写库；锁定语句、DML、DDL 优先路由写库；普通读走读库。

1) hostgroup 设计（主写、从读）

-- 连接到 ProxySQL 管理端
mysql -u admin -padmin -h 127.0.0.1 -P 6032

-- 配置主库和从库
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight) VALUES
(10, '10.0.0.10', 3306, 1),  -- 写库HG10
(20, '10.0.0.11', 3306, 1);  -- 读库HG20

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

2) 事务与锁语句的高优先级规则
说明：transaction_persistent=1 使事务内绑定写库。

-- 高优先级：显式锁/一致性读 -> 写库
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, priority)
VALUES
(1001, 1, '^SELECT\\s+.*FOR\\s+UPDATE', 10, 1, 1),
(1002, 1, '^SELECT\\s+.*LOCK\\s+IN\\s+SHARE\\s+MODE', 10, 1, 1);

-- 事务开始/结束识别（确保事务内写库）
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, priority, transaction_persistent)
VALUES
(1003, 1, '^(BEGIN|START\\s+TRANSACTION)', 10, 1, 1, 1),
(1004, 1, '^(COMMIT|ROLLBACK)', 10, 1, 1, 0);

-- DML/DDL 统一写库
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, priority)
VALUES
(1005, 1, '^(INSERT|UPDATE|DELETE|REPLACE|ALTER|CREATE|DROP|TRUNCATE)', 10, 1, 2);

-- 普通 SELECT 路由读库（低优先级）
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, priority)
VALUES
(2001, 1, '^SELECT', 20, 1, 100);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

3) 会话级强制主库（特定用户/应用强一致）

-- 将某用户的默认 hostgroup 设为写库
UPDATE mysql_users SET default_hostgroup=10 WHERE username='app_strict';
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

二、验证示例（命令+预期效果）
1) 模拟事务内读写 

-- 业务连接端口6033
mysql -u app -papp -h 127.0.0.1 -P 6033

BEGIN;
UPDATE t_order SET status='PAID' WHERE id=1;
SELECT status FROM t_order WHERE id=1; -- 期望走主库HG10
COMMIT;

2) 在管理端查看路由统计 

-- 观察命中规则与流量分布
SELECT rule_id, hits FROM stats_mysql_query_rules ORDER BY rule_id;

SELECT hostgroup, SUM(ConnUsed) AS used_conn
FROM stats_mysql_connection_pool
GROUP BY hostgroup;

预期：rule_id=1001/1003/1005 命中增加，HG10 连接使用量在事务期间升高。

三、排错清单（常见问题与命令）
1) 事务内读仍走从库
- 排查规则优先级与正则匹配：

SELECT rule_id, match_pattern, destination_hostgroup, priority
FROM mysql_query_rules ORDER BY priority;
  • 若 SELECT 规则优先级过高，降低其优先级（数值更大）。

2) autocommit=0 未触发事务绑定
- 确认是否设置了事务持久：

SELECT variable_name, variable_value
FROM global_variables
WHERE variable_name IN ('mysql-auto_increment_delay_multiplex', 'mysql-set_query_lock_on_hostgroup');
  • 建议通过规则匹配 ^SET\\s+autocommit=0 并路由写库。

3) 误将大量读打到主库
- 排查过宽规则：

SELECT rule_id, match_pattern FROM mysql_query_rules WHERE destination_hostgroup=10;
  • 缩小 match_pattern 范围，或提升读规则优先级（数值更大）。

四、练习题（动手验证）
1) 为 SELECT ... FOR UPDATE 增加规则，确保命中写库；用统计表验证命中次数。
2) 模拟从库延迟（或手动暂停复制），验证事务内读始终走主库。
3) 为业务表 t_account 增加强制主库读规则，仅该表走主库，其它读仍走从库。

总结
通过事务识别、规则优先级与会话级强制主库策略，ProxySQL 能在读写分离下保持关键事务的一致性，同时将非一致性读安全地分流到从库。