MyCat 学习笔记 第十二篇.数据分片 之 分片事务处理
1 环境说明
VM 模拟3台MYSQL 5.6 服务器
VM1 192.168.31.187：3307
VM2 192.168.31.212：3307
VM3 192.168.31.150: 3307
MYCAT 1.5 服务部署在宿主机上
MYCAT 192.168.31.207 :8806【SQL执行端口】 / 9066【管理端口】
2 应用场景
2.0 MYCAT配置
schema.xml
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="t_demo_travel_record" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
<table name="t_demo_travel_record_child" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
</schema>
<dataNode name="dn1" dataHost="vm1" database="test" />
<dataNode name="dn2" dataHost="vm2" database="test" />
<dataNode name="dn3" dataHost="vm3" database="test" />
<dataHost name="vm1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="vm1M1" url="192.168.31.187:3307" user="root" password="root123"></writeHost>
</dataHost>
<dataHost name="vm2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="vm2M1" url="192.168.31.212:3307" user="root" password="root123"></writeHost>
</dataHost>
<dataHost name="vm3" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="vm3M1" url="192.168.31.150:3307" user="root" password="root123"></writeHost>
</dataHost>
rule.xml
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<function name="rang-long"
class="org.opencloudb.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
<property name="defaultNode">0</property>
</function>
autopartition-long.txt
# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2
2.1 手动关闭事，跨数据库进行更新操作。
用一套简单点的JAVA来做这次数据验证
DataSource ds = jdbc.getDataSource();
conn = ds.getConnection();
conn.setAutoCommit(false);
pstm1 = conn.prepareStatement("insert into t_demo_travel_record (id,context) values (?,?)");
pstm1.setInt(1, 5000);
pstm1.setString(2, "这条记录应该进行DN1");
pstm1.addBatch();
pstm1.setInt(1, 5999999);
pstm1.setString(2, "这条记录应该进行DN2");
pstm1.addBatch();
pstm1.setInt(1, 14991499);
pstm1.setString(2, "这条记录应该进行DN3");
pstm1.addBatch();
pstm1.executeBatch();
conn.commit();
MYCAT 192.168.31.207: 8806 查询已插入的记录
mysql> select * from t_demo_travel_record where id in (5000,5999999,14991499);
+----------+-------------+
| id | context |
+----------+-------------+
| 5000 | ????????DN1 |
| 14991499 | ????????DN3 |
| 5999999 | ????????DN2 |
+----------+-------------+
3 rows in set (0.01 sec)
VM1 192.168.31.187：3307 物理库
mysql> select * from t_demo_travel_record where id in (5000,5999999,14991499);
+------+-------------+
| id | context |
+------+-------------+
| 5000 | ????????DN1 |
+------+-------------+
1 row in set (0.01 sec)
VM2 192.168.31.212：3307
mysql> select * from t_demo_travel_record where id in (5000,5999999,14991499);
+---------+-------------+
| id | context |
+---------+-------------+
| 5999999 | ????????DN2 |
+---------+-------------+
1 row in set (0.00 sec)
VM3 192.168.31.150: 3307
mysql> select * from t_demo_travel_record where id in (5000,5999999,14991499);
+----------+-------------+
| id | context |
+----------+-------------+
| 14991499 | ????????DN3 |
+----------+-------------+
1 row in set (0.00 sec)
2.2 异常操作，验证下数据是否回滚
模拟数据如下，最后一条主键重复。
pstm1.setInt(1, 5001);
pstm1.setString(2, "这条记录应该进行DN1");
pstm1.addBatch();
pstm1.setInt(1, 5999998);
pstm1.setString(2, "这条记录应该进行DN2");
pstm1.addBatch();
pstm1.setInt(1, 14991499);
pstm1.setString(2, "这条记录应该进行DN3");
pstm1.addBatch();
程序执行报错：
2016-02-03 00:36:10,229 INFO : [] com.alibaba.druid.pool.DruidDataSource [DruidDataSource.java:669] - {dataSource-1} inited
java.sql.BatchUpdateException: Duplicate entry '14991499' for key 'PRIMARY'
...
再看下数据库里并没有执行前2句SQL语句，mycat 在数据分片插入不同的数据库时已给我们做了一定的数据库事务控制。
mysql> select * from t_demo_travel_record where id in (5001,5999998,14991499);
+----------+-------------+
| id | context |
+----------+-------------+
| 14991499 | ????????DN3 |
+----------+-------------+
1 row in set (0.01 sec)
再来看下MYCAT日志，根据分片原则，分析将SQL语句发向不同的数据结点
02/03 00:36:10.279 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=31, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=false, schema=TESTDB]insert into t_demo_travel_record (id,context) values (5001,'这条记录应该进行DN1')
02/03 00:36:10.279 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:113) -ServerConnection [id=31, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=false, schema=TESTDB]insert into t_demo_travel_record (id,context) values (5001,'这条记录应该进行DN1'), route={
1 -> dn1{insert into t_demo_travel_record (id,context) values (5001,'这条记录应该进行DN1')}
} rrs
02/03 00:36:10.281 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=31, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=false, schema=TESTDB]insert into t_demo_travel_record (id,context) values (5999998,'这条记录应该进行DN2')
02/03 00:36:10.281 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:113) -ServerConnection [id=31, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=false, schema=TESTDB]insert into t_demo_travel_record (id,context) values (5999998,'这条记录应该进行DN2'), route={
1 -> dn2{insert into t_demo_travel_record (id,context) values (5999998,'这条记录应该进行DN2')}
} rrs
02/03 00:36:10.281 DEBUG [$_NIOREACTOR-1-RW] (MySQLConnection.java:445) -con need syn ,total syn cmd 1 commands SET autocommit=0;schema change:false con:MySQLConnection [id=28, lastTime=1454430970281, user=root, schema=test, old shema=test, borrowed=true, fromSlaveDB=false, threadId=30, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2{insert into t_demo_travel_record (id,context) values (5999998,'这条记录应该进行DN2')}, respHandler=SingleNodeHandler [node=dn2{insert into t_demo_travel_record (id,context) values (5999998,'这条记录应该进行DN2')}, packetId=0], host=192.168.31.212, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/03 00:36:10.284 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=31, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=false, schema=TESTDB]insert into t_demo_travel_record (id,context) values (14991499,'这条记录应该进行DN3')
02/03 00:36:10.284 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:113) -ServerConnection [id=31, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=false, schema=TESTDB]insert into t_demo_travel_record (id,context) values (14991499,'这条记录应该进行DN3'), route={
1 -> dn3{insert into t_demo_travel_record (id,context) values (14991499,'这条记录应该进行DN3')}
} rrs
准备同步数据库操作，发现SQL语句在DN3上编译失败
02/03 00:36:10.285 DEBUG [$_NIOREACTOR-1-RW] (MySQLConnection.java:445) -con need syn ,total syn cmd 1 commands SET autocommit=0;schema change:false con:MySQLConnection [id=8, lastTime=1454430970285, user=root, schema=test, old shema=test, borrowed=true, fromSlaveDB=false, threadId=24, charset=utf8, txIsolation=3, autocommit=true, attachment=dn3{insert into t_demo_travel_record (id,context) values (14991499,'这条记录应该进行DN3')}, respHandler=SingleNodeHandler [node=dn3{insert into t_demo_travel_record (id,context) values (14991499,'这条记录应该进行DN3')}, packetId=0], host=192.168.31.150, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/03 00:36:10.286 WARN [$_NIOREACTOR-0-RW] (SingleNodeHandler.java:222) -execute sql err : errno:1062 Duplicate entry '14991499' for key 'PRIMARY' con:MySQLConnection [id=8, lastTime=1454430970269, user=root, schema=test, old shema=test, borrowed=true, fromSlaveDB=false, threadId=24, charset=utf8, txIsolation=3, autocommit=false, attachment=dn3{insert into t_demo_travel_record (id,context) values (14991499,'这条记录应该进行DN3')}, respHandler=SingleNodeHandler [node=dn3{insert into t_demo_travel_record (id,context) values (14991499,'这条记录应该进行DN3')}, packetId=1], host=192.168.31.150, port=3307, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@48537945, writeQueue=0, modifiedSQLExecuted=true] frontend host:127.0.0.1/50644/test
再下来开始回滚所有数据结点上的更新操作。
02/03 00:36:10.305 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=31, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=false, schema=TESTDB]rollback
02/03 00:36:10.305 DEBUG [$_NIOREACTOR-1-RW] (RollbackNodeHandler.java:71) -rollback job run for MySQLConnection [id=8, lastTime=1454430970269, user=root, schema=test, old shema=test, borrowed=true, fromSlaveDB=false, threadId=24, charset=utf8, txIsolation=3, autocommit=false, attachment=dn3{insert into t_demo_travel_record (id,context) values (14991499,'这条记录应该进行DN3')}, respHandler=SingleNodeHandler [node=dn3{insert into t_demo_travel_record (id,context) values (14991499,'这条记录应该进行DN3')}, packetId=1], host=192.168.31.150, port=3307, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@48537945, writeQueue=0, modifiedSQLExecuted=true]
02/03 00:36:10.305 DEBUG [$_NIOREACTOR-1-RW] (RollbackNodeHandler.java:71) -rollback job run for MySQLConnection [id=16, lastTime=1454430970269, user=root, schema=test, old shema=test, borrowed=true, fromSlaveDB=false, threadId=33, charset=utf8, txIsolation=3, autocommit=false, attachment=dn1{insert into t_demo_travel_record (id,context) values (5001,'这条记录应该进行DN1')}, respHandler=SingleNodeHandler [node=dn1{insert into t_demo_travel_record (id,context) values (5001,'这条记录应该进行DN1')}, packetId=1], host=192.168.31.187, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/03 00:36:10.306 DEBUG [$_NIOREACTOR-1-RW] (RollbackNodeHandler.java:71) -rollback job run for MySQLConnection [id=28, lastTime=1454430970269, user=root, schema=test, old shema=test, borrowed=true, fromSlaveDB=false, threadId=30, charset=utf8, txIsolation=3, autocommit=false, attachment=dn2{insert into t_demo_travel_record (id,context) values (5999998,'这条记录应该进行DN2')}, respHandler=SingleNodeHandler [node=dn2{insert into t_demo_travel_record (id,context) values (5999998,'这条记录应该进行DN2')}, packetId=1], host=192.168.31.212, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/03 00:36:10.312 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:361) -clear session resources org.opencloudb.server.NonBlockingSession@603ec61b
释放数据库链接
02/03 00:36:10.312 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=8, lastTime=1454430970269, user=root, schema=test, old shema=test, borrowed=true, fromSlaveDB=false, threadId=24, charset=utf8, txIsolation=3, autocommit=false, attachment=dn3{insert into t_demo_travel_record (id,context) values (14991499,'这条记录应该进行DN3')}, respHandler=org.opencloudb.mysql.nio.handler.RollbackNodeHandler@394047fa, host=192.168.31.150, port=3307, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@48537945, writeQueue=0, modifiedSQLExecuted=true]
02/03 00:36:10.312 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=16, lastTime=1454430970269, user=root, schema=test, old shema=test, borrowed=true, fromSlaveDB=false, threadId=33, charset=utf8, txIsolation=3, autocommit=false, attachment=dn1{insert into t_demo_travel_record (id,context) values (5001,'这条记录应该进行DN1')}, respHandler=org.opencloudb.mysql.nio.handler.RollbackNodeHandler@394047fa, host=192.168.31.187, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/03 00:36:10.312 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=28, lastTime=1454430970269, user=root, schema=test, old shema=test, borrowed=true, fromSlaveDB=false, threadId=30, charset=utf8, txIsolation=3, autocommit=false, attachment=dn2{insert into t_demo_travel_record (id,context) values (5999998,'这条记录应该进行DN2')}, respHandler=org.opencloudb.mysql.nio.handler.RollbackNodeHandler@394047fa, host=192.168.31.212, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/03 00:36:10.313 DEBUG [$_NIOREACTOR-0-RW] (RollbackReleaseHandler.java:58) -autocomit is false,but no commit or rollback ,so mycat rollbacked backend conn MySQLConnection [id=8, lastTime=1454430970269, user=root, schema=test, old shema=test, borrowed=true, fromSlaveDB=false, threadId=24, charset=utf8, txIsolation=3, autocommit=false, attachment=null, respHandler=org.opencloudb.mysql.nio.handler.RollbackReleaseHandler@2b358b73, host=192.168.31.150, port=3307, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@48537945, writeQueue=0, modifiedSQLExecuted=true]
02/03 00:36:10.313 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=8, lastTime=1454430970269, user=root, schema=test, old shema=test, borrowed=true, fromSlaveDB=false, threadId=24, charset=utf8, txIsolation=3, autocommit=false, attachment=null, respHandler=null, host=192.168.31.150, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/03 00:36:10.313 DEBUG [$_NIOREACTOR-0-RW] (RollbackReleaseHandler.java:58) -autocomit is false,but no commit or rollback ,so mycat rollbacked backend conn MySQLConnection [id=16, lastTime=1454430970269, user=root, schema=test, old shema=test, borrowed=true, fromSlaveDB=false, threadId=33, charset=utf8, txIsolation=3, autocommit=false, attachment=null, respHandler=org.opencloudb.mysql.nio.handler.RollbackReleaseHandler@4856412e, host=192.168.31.187, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/03 00:36:10.313 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=16, lastTime=1454430970269, user=root, schema=test, old shema=test, borrowed=true, fromSlaveDB=false, threadId=33, charset=utf8, txIsolation=3, autocommit=false, attachment=null, respHandler=null, host=192.168.31.187, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/03 00:36:10.313 DEBUG [$_NIOREACTOR-0-RW] (RollbackReleaseHandler.java:58) -autocomit is false,but no commit or rollback ,so mycat rollbacked backend conn MySQLConnection [id=28, lastTime=1454430970269, user=root, schema=test, old shema=test, borrowed=true, fromSlaveDB=false, threadId=30, charset=utf8, txIsolation=3, autocommit=false, attachment=null, respHandler=org.opencloudb.mysql.nio.handler.RollbackReleaseHandler@16d1581c, host=192.168.31.212, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/03 00:36:10.313 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=28, lastTime=1454430970269, user=root, schema=test, old shema=test, borrowed=true, fromSlaveDB=false, threadId=30, charset=utf8, txIsolation=3, autocommit=false, attachment=null, respHandler=null, host=192.168.31.212, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02
OK，通过2个场景验证了MYCAT的事务一致性。
再补一句，大家可以通过MYSQL权威指南文档了解到其实MYCAT实现一种弱XA的事务控制，怎么理解这个弱XA事务控制。
举例有3个结点，MYCAT根据分片规则依次在不同物理库中预执行，如果当中任何一个发现问题则回滚所有。
若预执行成功，3个结点分别进行commit操作时发生异常，则mycat无法回滚已提交的事务。当然，这种机率很小很小~~~
另外一个问题在于，如果发锁升级导致单结点无法操作时，会对整体应用产生比较大的影响。
本篇完
