MySQL中间件ProxySQL的事务测试

雷雷DBA 2022-06-30

559

MySQL中间件ProxySQL的事务测试

--2022-06-29 春雷

1、前言

为了验证ProxySQL的几个autocommit参数对：set autocommit=0 的方式开事务的影响，做如下测试。

2、基本信息

分类	具体
ProxySQL	2.3.2版本 2.3.2-10-g8cd66cf 端口：5555
MySQL	5.7.22版本
MySQL集群	端口：5555 10.10.10.10 主实例 10.10.10.20 从实例 10.10.10.30 从实例 10.10.10.40 从实例 10.10.10.50 从实例

分类

具体

ProxySQL

2.3.2版本

2.3.2-10-g8cd66cf

端口：5555

MySQL

5.7.22版本

MySQL集群

端口：5555

10.10.10.10 主实例

10.10.10.20 从实例

10.10.10.30 从实例

10.10.10.40 从实例

10.10.10.50 从实例

3、测试

3.1、确认参数

【配置参数：global_variables】：

select * from runtime_global_variables where variable_name='mysql-forward_autocommit' limit 5;
select * from runtime_global_variables where variable_name='mysql-enforce_autocommit_on_reads' limit 5;
select * from runtime_global_variables where variable_name='mysql-autocommit_false_not_reusable' limit 5;
select * from runtime_global_variables where variable_name='mysql-autocommit_false_is_transaction' limit 5;

【账号配置表：mysql_users】：

select * from runtime_mysql_users;

【规则配置表：mysql_query_rules】

select * from runtime_mysql_query_rules;

3.2、访问测试

3.2.1、事务测试1

SQL	连接具体
select * from test limit 1;	连接从实例
begin;	连接主实例
select * from test limit 2;	连接主实例
select * from test limit 3;	连接主实例
commit;	连接主实例
select * from test limit 4;	连接从实例
select * from test limit 5;	连接从实例

【测试详情】：

mysql -utest -ptest123 -h中间件IP -P5555 db_test
mysql> select * from test limit 1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
+----+------+
1 row in set (0.01 sec)


mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test limit 2;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)


mysql> select * from test limit 3;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
+----+------+
3 rows in set (0.01 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test limit 4;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
| 10 | ee   |
+----+------+
4 rows in set (0.00 sec)


mysql> select * from test limit 5;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
| 10 | ee   |
+----+------+
4 rows in set (0.00 sec)


中间件访问日志：
{"client":"10.1.1.1:2222","digest":"0x226CD90D52A2BA0B","duration_us":0,"endtime":"2022-06-29 12:30:40.851065","endtime_timestamp_us":1656477040851065,"event":"COM_QUERY","hostgroup_id":-1,"query":"select @@version_comment limit 1","rows_affected":0,"rows_sent":0,"schemaname":"db_test","starttime":"2022-06-29 12:30:40.851065","starttime_timestamp_us":1656477040851065,"thread_id":1097299,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":238,"endtime":"2022-06-29 12:30:45.697812","endtime_timestamp_us":1656477045697812,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 1","rows_affected":0,"rows_sent":1,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:30:45.697574","starttime_timestamp_us":1656477045697574,"thread_id":1097299,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xA592C94A099E89DC","duration_us":1354,"endtime":"2022-06-29 12:30:48.062811","endtime_timestamp_us":1656477048062811,"event":"COM_QUERY","hostgroup_id":1,"query":"begin","rows_affected":0,"rows_sent":0,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:30:48.061457","starttime_timestamp_us":1656477048061457,"thread_id":1097299,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1531,"endtime":"2022-06-29 12:30:49.970893","endtime_timestamp_us":1656477049970893,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 2","rows_affected":0,"rows_sent":2,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:30:49.969362","starttime_timestamp_us":1656477049969362,"thread_id":1097299,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":14844,"endtime":"2022-06-29 12:30:54.036205","endtime_timestamp_us":1656477054036205,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 3","rows_affected":0,"rows_sent":3,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:30:54.021361","starttime_timestamp_us":1656477054021361,"thread_id":1097299,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xDB3A841EF5443C35","duration_us":1382,"endtime":"2022-06-29 12:30:56.994996","endtime_timestamp_us":1656477056994996,"event":"COM_QUERY","hostgroup_id":1,"query":"commit","rows_affected":0,"rows_sent":0,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:30:56.993614","starttime_timestamp_us":1656477056993614,"thread_id":1097299,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":234,"endtime":"2022-06-29 12:30:59.321199","endtime_timestamp_us":1656477059321199,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 4","rows_affected":0,"rows_sent":4,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:30:59.320965","starttime_timestamp_us":1656477059320965,"thread_id":1097299,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":351,"endtime":"2022-06-29 12:31:00.976829","endtime_timestamp_us":1656477060976829,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 5","rows_affected":0,"rows_sent":4,"schemaname":"db_test","server":"10.10.10.50:5555","starttime":"2022-06-29 12:31:00.976478","starttime_timestamp_us":1656477060976478,"thread_id":1097299,"username":"test"}

3.2.2、事务测试2

SQL	连接具体
select * from test limit 1;	连接从实例
start transaction;	连接主实例
select * from test limit 2;	连接主实例
select * from test limit 3;	连接主实例
commit;	连接主实例
select * from test limit 4;	连接从实例
select * from test limit 5;	连接从实例

【测试详情】：

mysql -utest -ptest123 -h中间件IP -P5555 db_test
mysql> select * from test limit 1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
+----+------+
1 row in set (0.00 sec)


mysql>  start transaction;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test limit 2;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)


mysql> select * from test limit 3;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
+----+------+
3 rows in set (0.01 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test limit 4;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
| 10 | ee   |
+----+------+
4 rows in set (0.00 sec)


mysql> select * from test limit 5;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
| 10 | ee   |
+----+------+
4 rows in set (0.00 sec)


中间件访问日志：
{"client":"10.1.1.1:2222","digest":"0x226CD90D52A2BA0B","duration_us":0,"endtime":"2022-06-29 12:37:15.508211","endtime_timestamp_us":1656477435508211,"event":"COM_QUERY","hostgroup_id":-1,"query":"select @@version_comment limit 1","rows_affected":0,"rows_sent":0,"schemaname":"db_test","starttime":"2022-06-29 12:37:15.508211","starttime_timestamp_us":1656477435508211,"thread_id":1097452,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":215,"endtime":"2022-06-29 12:37:18.111819","endtime_timestamp_us":1656477438111819,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 1","rows_affected":0,"rows_sent":1,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:37:18.111604","starttime_timestamp_us":1656477438111604,"thread_id":1097452,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0x326F4F2B935EC266","duration_us":1409,"endtime":"2022-06-29 12:37:27.160270","endtime_timestamp_us":1656477447160270,"event":"COM_QUERY","hostgroup_id":1,"query":"start transaction","rows_affected":0,"rows_sent":0,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:37:27.158861","starttime_timestamp_us":1656477447158861,"thread_id":1097452,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1569,"endtime":"2022-06-29 12:37:29.936428","endtime_timestamp_us":1656477449936428,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 2","rows_affected":0,"rows_sent":2,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:37:29.934859","starttime_timestamp_us":1656477449934859,"thread_id":1097452,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1546,"endtime":"2022-06-29 12:37:31.208729","endtime_timestamp_us":1656477451208729,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 3","rows_affected":0,"rows_sent":3,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:37:31.207183","starttime_timestamp_us":1656477451207183,"thread_id":1097452,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xDB3A841EF5443C35","duration_us":1404,"endtime":"2022-06-29 12:37:35.154422","endtime_timestamp_us":1656477455154422,"event":"COM_QUERY","hostgroup_id":1,"query":"commit","rows_affected":0,"rows_sent":0,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:37:35.153018","starttime_timestamp_us":1656477455153018,"thread_id":1097452,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":255,"endtime":"2022-06-29 12:37:37.824428","endtime_timestamp_us":1656477457824428,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 4","rows_affected":0,"rows_sent":4,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:37:37.824173","starttime_timestamp_us":1656477457824173,"thread_id":1097452,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":231,"endtime":"2022-06-29 12:37:40.021663","endtime_timestamp_us":1656477460021663,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 5","rows_affected":0,"rows_sent":4,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:37:40.021432","starttime_timestamp_us":1656477460021432,"thread_id":1097452,"username":"test"}

3.2.3、事务测试3

SQL	连接具体
select * from test limit 1;	连接从实例
SET AUTOCOMMIT=0	中间件直接返回，不转发
select * from test limit 2;	连接从实例
commit;	中间件直接返回，不转发
select * from test limit 3;	连接从实例

【测试详情】：

mysql -utest -ptest123 -h中间件IP -P5555 db_test
mysql> select * from test limit 1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
+----+------+
1 row in set (0.00 sec)


mysql> SET AUTOCOMMIT=0
    -> ;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test limit 2;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql>  select * from test limit 3;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
+----+------+
3 rows in set (0.01 sec)


中间件访问日志：
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":263,"endtime":"2022-06-29 12:01:44.094820","endtime_timestamp_us":1656475304094820,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 1","rows_affected":0,"rows_sent":1,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:01:44.094557","starttime_timestamp_us":1656475304094557,"thread_id":1096635,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0x6447D76C73F798EB","duration_us":0,"endtime":"2022-06-29 12:02:12.237275","endtime_timestamp_us":1656475332237275,"event":"COM_QUERY","hostgroup_id":-1,"query":"SET AUTOCOMMIT=0","rows_affected":0,"rows_sent":0,"schemaname":"db_test","starttime":"2022-06-29 12:02:12.237275","starttime_timestamp_us":1656475332237275,"thread_id":1096635,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":282,"endtime":"2022-06-29 12:02:22.701424","endtime_timestamp_us":1656475342701424,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 2","rows_affected":0,"rows_sent":2,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:02:22.701142","starttime_timestamp_us":1656475342701142,"thread_id":1096635,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xDB3A841EF5443C35","duration_us":0,"endtime":"2022-06-29 12:02:33.428068","endtime_timestamp_us":1656475353428068,"event":"COM_QUERY","hostgroup_id":-1,"query":"commit","rows_affected":0,"rows_sent":0,"schemaname":"db_test","starttime":"2022-06-29 12:02:33.428068","starttime_timestamp_us":1656475353428068,"thread_id":1096635,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":312,"endtime":"2022-06-29 12:02:47.178428","endtime_timestamp_us":1656475367178428,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 3","rows_affected":0,"rows_sent":3,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:02:47.178116","starttime_timestamp_us":1656475367178116,"thread_id":1096635,"username":"test"}

3.2.4、事务测试4

窗口1 SQL	连接具体	窗口2 SQL
select * from test limit 1;	连接从实例
SET AUTOCOMMIT=0	中间件直接返回，不转发
select * from test limit 2;	连接从实例
insert into test values (10,'ee');	连接主实例
		select * from test; 看不到插入的数据
select * from test limit 10;	连接主实例
commit;	连接主实例
		select * from test; 可以看到插入的数据
select * from test limit 11;	连接主实例

【测试详情】：

窗口1：
mysql -utest -ptest123 -h中间件IP -P5555 db_test
mysql> select * from test limit 1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
+----+------+
1 row in set (0.00 sec)


mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test limit 2;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)


mysql> insert into test values (10,'ee');
Query OK, 1 row affected (0.01 sec)


mysql> select * from test limit 10;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
| 10 | ee   |
+----+------+
4 rows in set (0.01 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test limit 11;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
| 10 | ee   |
+----+------+
4 rows in set (0.00 sec)


窗口2：连接MySQL主实例查询
(test:15038)@[(none)]>use db_test
Database changed
(test:15038)@[db_test]>select * from test;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
+----+------+
3 rows in set (0.01 sec)


窗口1提交后，再次查询可以看到


(test:15038)@[db_test]>select * from test;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
| 10 | ee   |
+----+------+
4 rows in set (0.00 sec)




中间件访问日志：
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":249,"endtime":"2022-06-29 12:03:35.890621","endtime_timestamp_us":1656475415890621,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 1","rows_affected":0,"rows_sent":1,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:03:35.890372","starttime_timestamp_us":1656475415890372,"thread_id":1096678,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0x6447D76C73F798EB","duration_us":0,"endtime":"2022-06-29 12:03:47.043537","endtime_timestamp_us":1656475427043537,"event":"COM_QUERY","hostgroup_id":-1,"query":"SET AUTOCOMMIT=0","rows_affected":0,"rows_sent":0,"schemaname":"db_test","starttime":"2022-06-29 12:03:47.043537","starttime_timestamp_us":1656475427043537,"thread_id":1096678,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":308,"endtime":"2022-06-29 12:03:52.331911","endtime_timestamp_us":1656475432331911,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 2","rows_affected":0,"rows_sent":2,"schemaname":"db_test","server":"10.10.10.50:5555","starttime":"2022-06-29 12:03:52.331603","starttime_timestamp_us":1656475432331603,"thread_id":1096678,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0x6D19DF2B6E1C4673","duration_us":2949,"endtime":"2022-06-29 12:04:10.740577","endtime_timestamp_us":1656475450740577,"event":"COM_QUERY","hostgroup_id":1,"query":"insert into test values (10,'ee')","rows_affected":1,"rows_sent":0,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:04:10.737628","starttime_timestamp_us":1656475450737628,"thread_id":1096678,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1573,"endtime":"2022-06-29 12:04:52.526755","endtime_timestamp_us":1656475492526755,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 10","rows_affected":0,"rows_sent":4,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:04:52.525182","starttime_timestamp_us":1656475492525182,"thread_id":1096678,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xDB3A841EF5443C35","duration_us":1535,"endtime":"2022-06-29 12:04:59.325673","endtime_timestamp_us":1656475499325673,"event":"COM_QUERY","hostgroup_id":1,"query":"commit","rows_affected":0,"rows_sent":0,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:04:59.324138","starttime_timestamp_us":1656475499324138,"thread_id":1096678,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1553,"endtime":"2022-06-29 12:23:29.899417","endtime_timestamp_us":1656476609899417,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 11","rows_affected":0,"rows_sent":4,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:23:29.897864","starttime_timestamp_us":1656476609897864,"thread_id":1096678,"username":"test"}

4、测试2

4.1、更改配置

update global_variables set variable_value='false' where variable_name='mysql-enforce_autocommit_on_reads';
update global_variables set variable_value='false' where variable_name='mysql-autocommit_false_not_reusable';
update global_variables set variable_value='false' where variable_name='mysql-autocommit_false_is_transaction';
load mysql variables to runtime;
save mysql variables to disk;

确认参数：

4.2、测试

4.2.1、事务测试1

SQL	连接具体
select * from test limit 1;	连接从实例
begin;	连接主实例
select * from test limit 2;	连接主实例
select * from test limit 3;	连接主实例
commit;	连接主实例
select * from test limit 4;	连接从实例
select * from test limit 5;	连接从实例

【测试详情】：

mysql -utest -ptest123 -h中间件IP -P5555 db_test
mysql> select * from test limit 1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
+----+------+
1 row in set (0.00 sec)


mysql> begin;
Query OK, 0 rows affected (0.01 sec)


mysql> select * from test limit 2;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)


mysql> select * from test limit 3;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
+----+------+
3 rows in set (0.01 sec)


mysql> commit;
Query OK, 0 rows affected (0.01 sec)


mysql> select * from test limit 4;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
| 10 | ee   |
+----+------+
4 rows in set (0.00 sec)


mysql> select * from test limit 5;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
| 10 | ee   |
+----+------+
4 rows in set (0.00 sec)


中间件访问日志：
{"client":"10.1.1.1:2222","digest":"0x226CD90D52A2BA0B","duration_us":0,"endtime":"2022-06-29 12:54:17.157391","endtime_timestamp_us":1656478457157391,"event":"COM_QUERY","hostgroup_id":-1,"query":"select @@version_comment limit 1","rows_affected":0,"rows_sent":0,"schemaname":"db_test","starttime":"2022-06-29 12:54:17.157391","starttime_timestamp_us":1656478457157391,"thread_id":1097841,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":218,"endtime":"2022-06-29 12:54:19.628804","endtime_timestamp_us":1656478459628804,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 1","rows_affected":0,"rows_sent":1,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:54:19.628586","starttime_timestamp_us":1656478459628586,"thread_id":1097841,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xA592C94A099E89DC","duration_us":1364,"endtime":"2022-06-29 12:54:26.730899","endtime_timestamp_us":1656478466730899,"event":"COM_QUERY","hostgroup_id":1,"query":"begin","rows_affected":0,"rows_sent":0,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:54:26.729535","starttime_timestamp_us":1656478466729535,"thread_id":1097841,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1602,"endtime":"2022-06-29 12:54:32.606209","endtime_timestamp_us":1656478472606209,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 2","rows_affected":0,"rows_sent":2,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:54:32.604607","starttime_timestamp_us":1656478472604607,"thread_id":1097841,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1535,"endtime":"2022-06-29 12:54:33.842144","endtime_timestamp_us":1656478473842144,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 3","rows_affected":0,"rows_sent":3,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:54:33.840609","starttime_timestamp_us":1656478473840609,"thread_id":1097841,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xDB3A841EF5443C35","duration_us":1441,"endtime":"2022-06-29 12:54:36.311918","endtime_timestamp_us":1656478476311918,"event":"COM_QUERY","hostgroup_id":1,"query":"commit","rows_affected":0,"rows_sent":0,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:54:36.310477","starttime_timestamp_us":1656478476310477,"thread_id":1097841,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":284,"endtime":"2022-06-29 12:54:37.782841","endtime_timestamp_us":1656478477782841,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 4","rows_affected":0,"rows_sent":4,"schemaname":"db_test","server":"10.10.10.50:5555","starttime":"2022-06-29 12:54:37.782557","starttime_timestamp_us":1656478477782557,"thread_id":1097841,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":243,"endtime":"2022-06-29 12:54:39.138714","endtime_timestamp_us":1656478479138714,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 5","rows_affected":0,"rows_sent":4,"schemaname":"db_test","server":"10.10.10.50:5555","starttime":"2022-06-29 12:54:39.138471","starttime_timestamp_us":1656478479138471,"thread_id":1097841,"username":"test"}

4.2.2、事务测试2

SQL	连接具体
select * from test limit 1;	连接从实例
start transaction;	连接主实例
select * from test limit 2;	连接主实例
select * from test limit 3;	连接主实例
commit;	连接主实例
select * from test limit 4;	连接从实例
select * from test limit 5;	连接从实例

【测试详情】：

mysql -utest -ptest123 -h中间件IP -P5555 db_test
mysql> select * from test limit 1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
+----+------+
1 row in set (0.01 sec)


mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)


mysql> select * from test limit 2;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)


mysql> select * from test limit 3;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
+----+------+
3 rows in set (0.00 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test limit 4;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
| 10 | ee   |
+----+------+
4 rows in set (0.00 sec)


mysql> select * from test limit 5;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
| 10 | ee   |
| 20 | ff   |
+----+------+
5 rows in set (0.00 sec)


中间件访问日志：
{"client":"10.1.1.1:2222","digest":"0x226CD90D52A2BA0B","duration_us":0,"endtime":"2022-06-29 13:06:05.368931","endtime_timestamp_us":1656479165368931,"event":"COM_QUERY","hostgroup_id":-1,"query":"select @@version_comment limit 1","rows_affected":0,"rows_sent":0,"schemaname":"db_test","starttime":"2022-06-29 13:06:05.368931","starttime_timestamp_us":1656479165368931,"thread_id":1098117,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":271,"endtime":"2022-06-29 13:06:23.665297","endtime_timestamp_us":1656479183665297,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 1","rows_affected":0,"rows_sent":1,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 13:06:23.665026","starttime_timestamp_us":1656479183665026,"thread_id":1098117,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0x326F4F2B935EC266","duration_us":1425,"endtime":"2022-06-29 13:06:29.046246","endtime_timestamp_us":1656479189046246,"event":"COM_QUERY","hostgroup_id":1,"query":"start transaction","rows_affected":0,"rows_sent":0,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 13:06:29.044821","starttime_timestamp_us":1656479189044821,"thread_id":1098117,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1577,"endtime":"2022-06-29 13:06:34.582828","endtime_timestamp_us":1656479194582828,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 2","rows_affected":0,"rows_sent":2,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 13:06:34.581251","starttime_timestamp_us":1656479194581251,"thread_id":1098117,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1488,"endtime":"2022-06-29 13:06:35.597908","endtime_timestamp_us":1656479195597908,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 3","rows_affected":0,"rows_sent":3,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 13:06:35.596420","starttime_timestamp_us":1656479195596420,"thread_id":1098117,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xDB3A841EF5443C35","duration_us":1419,"endtime":"2022-06-29 13:06:39.290401","endtime_timestamp_us":1656479199290401,"event":"COM_QUERY","hostgroup_id":1,"query":"commit","rows_affected":0,"rows_sent":0,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 13:06:39.288982","starttime_timestamp_us":1656479199288982,"thread_id":1098117,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":250,"endtime":"2022-06-29 13:06:41.400123","endtime_timestamp_us":1656479201400123,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 4","rows_affected":0,"rows_sent":4,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 13:06:41.399873","starttime_timestamp_us":1656479201399873,"thread_id":1098117,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":262,"endtime":"2022-06-29 13:06:42.667775","endtime_timestamp_us":1656479202667775,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 5","rows_affected":0,"rows_sent":5,"schemaname":"db_test","server":"10.10.10.50:5555","starttime":"2022-06-29 13:06:42.667513","starttime_timestamp_us":1656479202667513,"thread_id":1098117,"username":"test"}

4.2.3、事务测试3

SQL	连接具体
select * from test limit 1;	连接从实例
SET AUTOCOMMIT=0	中间件直接返回，不转发
select * from test limit 2;	连接从实例
commit;	中间件直接返回，不转发
select * from test limit 3;	连接从实例

【测试详情】：

mysql -utest -ptest123 -h中间件IP -P5555 db_test
mysql> select * from test limit 1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
+----+------+
1 row in set (0.01 sec)


mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test limit 2;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test limit 3;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
+----+------+
3 rows in set (0.00 sec)


中间件访问日志：
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":231,"endtime":"2022-06-29 12:55:56.231303","endtime_timestamp_us":1656478556231303,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 1","rows_affected":0,"rows_sent":1,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:55:56.231072","starttime_timestamp_us":1656478556231072,"thread_id":1097872,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0x6447D76C73F798EB","duration_us":0,"endtime":"2022-06-29 12:56:09.079890","endtime_timestamp_us":1656478569079890,"event":"COM_QUERY","hostgroup_id":-1,"query":"SET AUTOCOMMIT=0","rows_affected":0,"rows_sent":0,"schemaname":"db_test","starttime":"2022-06-29 12:56:09.079890","starttime_timestamp_us":1656478569079890,"thread_id":1097872,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":321,"endtime":"2022-06-29 12:56:11.078580","endtime_timestamp_us":1656478571078580,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 2","rows_affected":0,"rows_sent":2,"schemaname":"db_test","server":"10.10.10.50:5555","starttime":"2022-06-29 12:56:11.078259","starttime_timestamp_us":1656478571078259,"thread_id":1097872,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xDB3A841EF5443C35","duration_us":0,"endtime":"2022-06-29 12:56:22.560071","endtime_timestamp_us":1656478582560071,"event":"COM_QUERY","hostgroup_id":-1,"query":"commit","rows_affected":0,"rows_sent":0,"schemaname":"db_test","starttime":"2022-06-29 12:56:22.560071","starttime_timestamp_us":1656478582560071,"thread_id":1097872,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":316,"endtime":"2022-06-29 12:56:25.574068","endtime_timestamp_us":1656478585574068,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 3","rows_affected":0,"rows_sent":3,"schemaname":"db_test","server":"10.10.10.50:5555","starttime":"2022-06-29 12:56:25.573752","starttime_timestamp_us":1656478585573752,"thread_id":1097872,"username":"test"}

4.2.4、事务测试4

窗口1 SQL	连接具体	窗口2 SQL
select * from test limit 1;	连接从实例
SET AUTOCOMMIT=0	中间件直接返回，不转发
select * from test limit 2;	连接从实例
insert into test values (11,'ff');	连接主实例
		select * from test; 看不到插入的数据
select * from test limit 10;	连接主实例
commit;	连接主实例
		select * from test; 可以看到插入的数据
select * from test limit 11;	连接主实例

【测试详情】：

窗口1：
mysql -utest -ptest123 -h中间件IP -P5555 db_test
mysql> select * from test limit 1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
+----+------+
1 row in set (0.00 sec)


mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test limit 2;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)


mysql>  insert into test values (20,'ff');
Query OK, 1 row affected (0.01 sec)


mysql> select * from test limit 10;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
| 10 | ee   |
| 20 | ff   |
+----+------+
5 rows in set (0.01 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test limit 11;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
| 10 | ee   |
| 20 | ff   |
+----+------+
5 rows in set (0.00 sec)




窗口2：连接MySQL主实例查询


(test:15038)@[db_test]>select * from test ;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
| 10 | ee   |
+----+------+
4 rows in set (0.00 sec)


(test:15038)@[db_test]>select * from test ;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
| 10 | ee   |
| 20 | ff   |
+----+------+
5 rows in set (0.00 sec)


中间件访问日志：
{"client":"10.1.1.1:2222","digest":"0x226CD90D52A2BA0B","duration_us":0,"endtime":"2022-06-29 12:58:00.910345","endtime_timestamp_us":1656478680910345,"event":"COM_QUERY","hostgroup_id":-1,"query":"select @@version_comment limit 1","rows_affected":0,"rows_sent":0,"schemaname":"db_test","starttime":"2022-06-29 12:58:00.910345","starttime_timestamp_us":1656478680910345,"thread_id":1097933,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":245,"endtime":"2022-06-29 12:58:05.203742","endtime_timestamp_us":1656478685203742,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 1","rows_affected":0,"rows_sent":1,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:58:05.203497","starttime_timestamp_us":1656478685203497,"thread_id":1097933,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0x6447D76C73F798EB","duration_us":0,"endtime":"2022-06-29 12:58:07.786478","endtime_timestamp_us":1656478687786478,"event":"COM_QUERY","hostgroup_id":-1,"query":"SET AUTOCOMMIT=0","rows_affected":0,"rows_sent":0,"schemaname":"db_test","starttime":"2022-06-29 12:58:07.786478","starttime_timestamp_us":1656478687786478,"thread_id":1097933,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":304,"endtime":"2022-06-29 12:58:10.838779","endtime_timestamp_us":1656478690838779,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 2","rows_affected":0,"rows_sent":2,"schemaname":"db_test","server":"10.10.10.50:5555","starttime":"2022-06-29 12:58:10.838475","starttime_timestamp_us":1656478690838475,"thread_id":1097933,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0x6D19DF2B6E1C4673","duration_us":2958,"endtime":"2022-06-29 12:58:24.516120","endtime_timestamp_us":1656478704516120,"event":"COM_QUERY","hostgroup_id":1,"query":"insert into test values (20,'ff')","rows_affected":1,"rows_sent":0,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:58:24.513162","starttime_timestamp_us":1656478704513162,"thread_id":1097933,"username":test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1579,"endtime":"2022-06-29 12:58:37.976418","endtime_timestamp_us":1656478717976418,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 10","rows_affected":0,"rows_sent":5,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:58:37.974839","starttime_timestamp_us":1656478717974839,"thread_id":1097933,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xDB3A841EF5443C35","duration_us":1522,"endtime":"2022-06-29 12:58:44.071517","endtime_timestamp_us":1656478724071517,"event":"COM_QUERY","hostgroup_id":1,"query":"commit","rows_affected":0,"rows_sent":0,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:58:44.069995","starttime_timestamp_us":1656478724069995,"thread_id":1097933,"username":"test"}
{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":295,"endtime":"2022-06-29 12:58:55.660222","endtime_timestamp_us":1656478735660222,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 11","rows_affected":0,"rows_sent":5,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:58:55.659927","starttime_timestamp_us":1656478735659927,"thread_id":1097933,"username":"test"}

5、总结

begin 或 start transaction 均可以强制发送到主实例
set auocommit=0 不可以被转发，在中间件层会直接返回
事务内的SQL会统一发到相同的实例
set auocommit=0 会保证事务，未提交的变更，其他事务看不到
set auocommit=0 方式连接，随后查询，会路由到从实例，根据此数据进行后续更新等，可能带来一定的数据准确性问题。
不管是否开启事务，insert 类等变更SQL是会转发到主实例上的。
参数：

mysql-autocommit_false_not_reusable 为true

mysql-autocommit_false_is_transaction 为true
与：

mysql-autocommit_false_not_reusable 为false

mysql-autocommit_false_is_transaction 为false

表现一样

综上：还是推荐大家使用begin 或 start transaction 方式开启事务。

代码里面实现，大致是：

1、创建连接

2、执行SQL：begin
3、执行SQL：查询、写入等
4、支持SQL：commit

5、释放连接(或者不释放也可以)
这样就能转发到主实例了

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

MySQL中间件ProxySQL的事务测试

2、基本信息

3、测试

3.1、确认参数

3.2、访问测试

3.2.1、事务测试1

3.2.2、事务测试2

3.2.3、事务测试3

3.2.4、事务测试4

4、测试2

4.1、更改配置

4.2、测试

4.2.1、事务测试1

4.2.2、事务测试2

4.2.3、事务测试3

4.2.4、事务测试4

5、总结

评论