ProxySQL中间件事务表现

雷雷DBA 2022-06-21

968

ProxySQL中间件事务表现

--2022-06-21

1、前言

目前公司已经使用了200个左右的ProxySQL实例，已经使用了2年多，基本的需求为读写分离，但是为了满足强制读主的需求，设置查询规则为：

不开事务的读，默认发送到从实例上
开事务的读，默认发送到主实例上

但是有些开发童鞋，比较迷惑，到底如何开启事务，才能让读SQL 强制到主实例上执行。

下面进行测试

2、测试具体

2.1、ProxySQL配置

mysql_query_rules表：

match_digest的配置^SELECT destination_hostgroup 为2

match_digest的配置^SELECT.*FOR UPDATE$ destination_hostgroup 为1

即：

匹配到以select开头的SQL会转发到从实例

匹配到select 开头，for update结尾的转发到主实例

2.2、强制开事务方式：[begin，start transaction]

begin，start transaction 这种强制开事务，是可以直接转发到后端MySQL的主实例的，例如：

begin 或 start transaction;转发到后端MySQL主实例

select * from aaa ;转发到后端MySQL主实例

insert into aaa values (1,'aa'); 转发到后端MySQL主实例，且其他事务不可见

select * from aaa ; 转发到后端MySQL主实例

commit; 转发到后端MySQL主实例,且其他事务可见

2.3、通过设置自动提交的方式来开事务：[set autocommit=0]

set autocommit=0这种，是直接在中间件ProxySQL层就返回，不会转发到后端MySQL主实例,

【尝试设置规则表，让其转发到主实例】：

在规则表mysql_query_rules设置：匹配set autocommit=0 就转发到主实例，这种也是不生效的，测试如下：

match_digest: ^SET AUTOCOMMIT=0

destination_hostgroup: 1

查看日志："hostgroup_id":-1 即不转发到后端的主实例，直接返回

【SQL测试示例1】：

set autocommit=0; 中间件层直接返回

select * from aaa ;转发到后端MySQL从实例

insert into aaa values (1,'aa'); 转发到后端MySQL主实例，且其他事务不可见

select * from aaa ; 转发到后端MySQL主实例

commit; 转发到后端MySQL主实例,且其他事务可见

【SQL测试示例2】：

set autocommit=1

select * from aaa ;转发到后端MySQL从实例

insert into aaa values (1,'aa'); 转发到后端MySQL主实例，且其他事务可见

select * from aaa ; 转发到后端MySQL从实例

3、总结

set autocommit=0 虽然没有转发到后端主实例，但是可以保证不自动提交，但是如果先查询，就会查询到从实例，再以查询的结果执行更新等写动作，就会有数据延后或者不对的情况。
如果想强制读主库，需要明确开启事务，使用begin 或 start transaction，同时为了保证后续SQL也会在相同的实例执行，需要配置mysql_users表的transaction_persistent为1，解释如下：
当某用户开始一个事务后，该事务内的请求在基于查询规则进行路由时，可能会路由到不同的主机组中。为了避免这种情况，需要启用transaction_persistent来保证事务持久。(只有在事务结束后，该用户的请求才允许路由到其它主机组)

proxysql mysql读写分离 mysql事务 mysql中间件 mysql

文章转载自雷雷DBA，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论