Mycat的使用 – 04.事务支持

03全局序列号篇详细介绍了分片表, 业务在使用分片表时, 很自然的可能会遇到一个事务中操作的数据分布在多个分片节点上, 即分布式事务. 先来直观感受下Mycat处理事务的过程.

登陆tb3表的dnTest2节点主机, 操作如下.mysql> set global innodb_lock_wait_timeout = 5;Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)
mysql> select user_name from tb3 where user_id = 59 for update;+———–+| user_name |+———–+| mnop_f |+———–+1 row in set (0.00 sec)
登陆Mycat, 开启一个事务, 结合日志看下该过程.mysql> start transaction;Query OK, 0 rows affected (0.01 sec)
mysql> update tb3 set user_name = ‘igkl_2f’ where user_id = 4;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0
mysql> update tb3 set user_name = ‘mnop_2f’ where user_id = 59;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> commit;ERROR 1003 (HY000): Transaction error, need to rollback.mysql> rollback;Query OK, 0 rows affected (0.00 sec)
关键日志如下.03/27 12:07:09.189 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=2, schema=testdb, host=192.168.4.184, user=test_user,txIsolation=3, autocommit=true, schema=testdb]begin03/27 12:16:33.019 DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=6, schema=testdb, host=192.168.4.184, user=test_user,txIsolation=3, autocommit=false, schema=testdb]update tb3 set user_name = ‘igkl_2f’ where user_id = 403/27 12:16:33.021 DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:113) -ServerConnection [id=6, schema=testdb, host=192.168.4.184, user=test_user,txIsolation=3, autocommit=false, schema=testdb]update tb3 set user_name = ‘igkl_2f’ where user_id = 4, route={ 1 -> dnTest1{update tb3 set user_name = ‘igkl_2f’ where user_id = 4}03/27 12:16:33.021 DEBUG [$_NIOREACTOR-2-RW] (MySQLConnection.java:459) -con need syn ,total syn cmd 3 commands SET names utf8;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;SET autocommit=0;schema change:false con:MySQLConnection [id=8, lastTime=1522124193021, user=appacc, schema=test1, old shema=test1, borrowed=true, fromSlaveDB=false, threadId=28, charset=utf8, txIsolation=0, autocommit=true, attachment=dnTest1{update tb3 set user_name = ‘igkl_2f’ where user_id = 4}, respHandler=SingleNodeHandler [node=dnTest1{update tb3 set user_name = ‘igkl_2f’ where user_id = 4}, packetId=0], host=192.168.4.235, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]03/27 12:16:52.795 DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=6, schema=testdb, host=192.168.4.184, user=test_user,txIsolation=3, autocommit=false, schema=testdb]update tb3 set user_name = ‘mnop_2f’ where user_id = 5903/27 12:16:52.796 DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:113) -ServerConnection [id=6, schema=testdb, host=192.168.4.184, user=test_user,txIsolation=3, autocommit=false, schema=testdb]update tb3 set user_name = ‘mnop_2f’ where user_id = 59, route={ 1 -> dnTest2{update tb3 set user_name = ‘mnop_2f’ where user_id = 59}03/27 12:16:52.797 DEBUG [$_NIOREACTOR-2-RW] (MySQLConnection.java:459) -con need syn ,total syn cmd 3 commands SET names utf8;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;SET autocommit=0;schema change:false con:MySQLConnection [id=20, lastTime=1522124212797, user=appacc, schema=test2, old shema=test2, borrowed=true, fromSlaveDB=false, threadId=8, charset=utf8, txIsolation=0, autocommit=true, attachment=dnTest2{update tb3 set user_name = ‘mnop_2f’ where user_id = 59}, respHandler=SingleNodeHandler [node=dnTest2{update tb3 set user_name = ‘mnop_2f’ where user_id = 59}, packetId=0], host=192.168.4.151, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]03/27 12:16:58.800 WARN [$_NIOREACTOR-1-RW] (SingleNodeHandler.java:232) -execute sql err : errno:1205 Lock wait timeout exceeded; try restarting transaction con:MySQLConnection [id=20, lastTime=1522124212784, user=appacc, schema=test2, old shema=test2, borrowed=true, fromSlaveDB=false, threadId=8, charset=utf8, txIsolation=3, autocommit=false, attachment=dnTest2{update tb3 set user_name = ‘mnop_2f’ where user_id = 59}, respHandler=SingleNodeHandler [nod开发云主机域名e=dnTest2{update tb3 set user_name = ‘mnop_2f’ where user_id = 59}, packetId=1], host=192.168.4.151, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@52530466, writeQueue=0, modifiedSQLExecuted=true] frontend host:192.168.4.184/59858/test_user03/27 12:17:05.660 DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=6, schema=testdb, host=192.168.4.184, user=test_user,txIsolation=3, autocommit=false, schema=testdb]commit03/27 12:17:08.868 DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=6, schema=testdb, host=192.168.4.184, user=test_user,txIsolation=3, autocommit=false, schema=testdb]rollback03/27 12:17:08.870 DEBUG [$_NIOREACTOR-2-RW] (RollbackNodeHandler.java:79) -rollback job run for MySQLConnection [id=20, lastTime=1522124212784, user=appacc, schema=test2, old shema=test2, borrowed=true, fromSlaveDB=false, threadId=8, charset=utf8, txIsolation=3, autocommit=false, attachment=dnTest2{update tb3 set user_name = ‘mnop_2f’ where user_id = 59}, respHandler=SingleNodeHandler [node=dnTest2{update tb3 set user_name = ‘mnop_2f’ where user_id = 59}, packetId=1], host=192.168.4.151, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@52530466, writeQueue=0, modifiedSQLExecuted=true]03/27 12:17:08.870 DEBUG [$_NIOREACTOR-2-RW] (RollbackNodeHandler.java:79) -rollback job run for MySQLConnection [id=8, lastTime=1522124193010, user=appacc, schema=test1, old shema=test1, borrowed=true, fromSlaveDB=false, threadId=28, charset=utf8, txIsolation=3, autocommit=false, attachment=dnTest1{update tb3 set user_name = ‘igkl_2f’ where user_id = 4}, respHandler=SingleNodeHandler [node=dnTest1{update tb3 set user_name = ‘igkl_2f’ where user_id = 4}, packetId=1], host=192.168.4.235, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
Mycat目前对于未分片的节点, 是可以保证事务的完整性的; 若是多个分片节点, 在执行事务时, 遇到任何分片出错, 也是能保证所有分片回滚的, 即上边展示的情况. 可是应用一旦进入commit过程, 若此时出现问题, 其就无能为力了, 这也是Mycat称之为弱XA的原因.
上述commit过程是指: InnoDB prepare, write/sync Binlog, InnoDB commit(其历经5.5至5.7版本的多次迭代优化, 这块内容也是精彩纷呈). 虽该阶段一般不会出现问题, 这也正暗示了Mycat在特殊情况下还不能保证分布式事务安全. 那应用架构中又如何实现可靠的分布式事务呢, 这又是另一个宏大的话题了…
若感兴趣可关注订阅号”数据库最佳实践”(DBBestPractice).

相关推荐: mysql 存储过程的创建

注：（1）这里需要注意的是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以”;”为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用…