Mycat之日志分析跨分片事务以及存储过程的执行过程

1 针对成功事务:

过程说明:

  1.初始化连接，路由到各个分片

  2.开启非阻塞执行更新，然后执行时候每个节点执行2次

  3.执行提交，各节点返回commit

  4.释放连接，先释放datasource然后才说非阻塞连接

执行代码*

 #开启事务更新(全表更新)

mysql> select * from travelrecord;

+----------+-----------+------------+------+------+

| id       | user_id   | traveldate | fee  | days |

+----------+-----------+------------+------+------+

|     1000 | chinesern | 2017-10-11 |  100 |   10 |

| 11000000 | chinesern | 2017-10-11 |  100 |   10 |

|  5100000 | chinesern | 2017-10-11 |  100 |   10 |

+----------+-----------+------------+------+------+

3 rows in set (0.03 sec)

mysql> begin ;

Query OK, 0 rows affected (0.01 sec)

mysql> update travelrecord set user_id='fish';

Query OK, 3 rows affected (0.11 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;

Query OK, 0 rows affected (0.03 sec)

分析mycat日志

2 针对失败事务:

情景描述:

1.一个节点开启事务SQL执行，不提交

2. mycat 开启一个事务模拟阻塞,超时后,冲突手动回滚,并查看mycat日志

rollback过程:

1.获取连接

2.各个节点执行回滚，释放非阻塞连接，最后物理连接。

执行代码*

#在db1执行事务不提交.

[root@localhost bin]# mysql -h 192.168.2.130 -P3306 -u root -proot123

mysql> use db1;

mysql> begin;

mysql>delete from travelrecord where id =1000;

#在mycat里面执行更新，模拟失败

[root@localhost conf]# mysql -h 192.168.2.130 -P8066 -utest -ptest

mysql> begin;

mysql> update travelrecord set user_id='fail';

ERROR 1105 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> rollback;

分析mycat日志

3 创建存储过程

#MYCAT上执行报错

mysql> DELIMITER $$

mysql> CREATE PROCEDURE `proc_count`()

	-> BEGIN

	-> select count(*) from travelrecord;

	-> END$$

ERROR 1064 (HY000): op table not in schema----PROCEDURE

#在各节点上创建存储

mysql> use db1

mysql> DELIMITER $$

mysql> create procedure proc_select() begin declare i int; select count(*) into i from travelrecord;select i;end$

mysql> use db2

mysql> create procedure proc_select() begin declare i int; select count(*) into i from travelrecord;select i;end$

mysql> use db3

mysql> create procedure proc_select() begin declare i int; select count(*) into i from travelrecord;select i;end$

4 MYCAT上调用pror_select ,路由到特定节点上。**

查询语句

mysql> /*!mycat:sql=select * from travelrecord where ID=1000*/ call proc_select();

+------+

| i    |

+------+

|    1 |

+------+

1 row in set (0.03 sec)

分析mycat日志

5 MYCAT上调用proc_select 路由到所有节点

查询语句

mysql> /*!mycat:sql=select * from travelrecord where ID=1000*/ call proc_select();

+------+

| i    |

+------+

|    1 |

+------+

1 row in set (0.03 sec)

分析MYCAT日志,可以看到路由到了三个节点上,但是没有merge,而是分别查询出来了！

巴特西

Mycat之日志分析跨分片事务以及存储过程的执行过程

1 针对成功事务:

2 针对失败事务:

3 创建存储过程

4 MYCAT上调用pror_select ,路由到特定节点上。**

5 MYCAT上调用proc_select 路由到所有节点

最新文章

热门文章