Shulou(Shulou.com)06/01 Report--

03 Global sequence number section introduces the sharding table in detail. When using sharding tables, businesses may naturally encounter data operated in a transaction distributed on multiple shard nodes, that is, distributed transactions. First, let's intuitively feel the process of dealing with transactions in Mycat.

Log in to the dnTest2 node host of the tb3 table and do the following.

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)

Log in to Mycat, open a transaction, and take a look at the process in conjunction with the log.

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.01sec)

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 transaction

Mysql > commit

ERROR 1003 (HY000): Transaction error, need to rollback.

Mysql > rollback

Query OK, 0 rows affected (0.00 sec)

The key log is as follows.

03ax 27 12 ServerQueryHandler.java:56 0715 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] begin

03According 27 12 ServerQueryHandler.java:56 16 ServerQueryHandler.java:56 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 = 4

03According 27 12 NonBlockingSession.java:113 16 NonBlockingSession.java:113 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}

03According 27 12 con need syn 16 MySQLConnection.java:459 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]

03According 27 12 ServerQueryHandler.java:56 16 DEBUG 52.795 [$_ 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 = 59

03ax 27 12 NonBlockingSession.java:113 16 NonBlockingSession.java:113 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}

03go 27 12 MySQLConnection.java:459 16 DEBUG 52.797 [$_ 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]

03Plus 27 12V 16R 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 [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] frontend host:192.168.4.184/59858/test_user

03True 27 12 ServerQueryHandler.java:56 1715 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] commit

03True 27 12 ServerQueryHandler.java:56 17 ServerQueryHandler.java:56 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] rollback

03ax 27 12 RollbackNodeHandler.java:79 1715 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]

03US27 12GV 17VERV 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]

At present, Mycat can guarantee the integrity of transactions for unsharded nodes; if multiple sharding nodes encounter any sharding errors during transaction execution, it can also guarantee that all shards are rolled back, as shown above. However, once the application enters the commit process, if there is a problem at this time, it will be powerless, which is why Mycat calls it weak XA.

The above commit process refers to: InnoDB prepare, write/sync Binlog, InnoDB commit (it has undergone several iterative optimizations in versions 5.5 to 5.7, and this piece of content is also brilliant). Although there are generally no problems at this stage, it also implies that Mycat can not guarantee the security of distributed transactions in special cases. Then how to achieve reliable distributed transactions in the application architecture is another grand topic.

If you are interested, please follow Subscription account's Database Best practices (DBBestPractice).

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.