Mycat的使用-04.事務(wù)支持

03全局序列號(hào)篇詳細(xì)介紹了分片表, 業(yè)務(wù)在使用分片表時(shí), 很自然的可能會(huì)遇到一個(gè)事務(wù)中操作的數(shù)據(jù)分布在多個(gè)分片節(jié)點(diǎn)上, 即分布式事務(wù). 先來(lái)直觀感受下Mycat處理事務(wù)的過(guò)程.

在網(wǎng)站制作、成都網(wǎng)站制作過(guò)程中，需要針對(duì)客戶的行業(yè)特點(diǎn)、產(chǎn)品特性、目標(biāo)受眾和市場(chǎng)情況進(jìn)行定位分析，以確定網(wǎng)站的風(fēng)格、色彩、版式、交互等方面的設(shè)計(jì)方向。成都創(chuàng)新互聯(lián)公司還需要根據(jù)客戶的需求進(jìn)行功能模塊的開(kāi)發(fā)和設(shè)計(jì)，包括內(nèi)容管理、前臺(tái)展示、用戶權(quán)限管理、數(shù)據(jù)統(tǒng)計(jì)和安全保護(hù)等功能。

登陸tb3表的dnTest2節(jié)點(diǎn)主機(jī), 操作如下.

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, 開(kāi)啟一個(gè)事務(wù), 結(jié)合日志看下該過(guò)程.

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 transaction

mysql> commit;

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

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

關(guān)鍵日志如下.

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]begin

03/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 = 4

03/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 = 59

03/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 [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

03/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]commit

03/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]rollback

03/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目前對(duì)于未分片的節(jié)點(diǎn), 是可以保證事務(wù)的完整性的; 若是多個(gè)分片節(jié)點(diǎn), 在執(zhí)行事務(wù)時(shí), 遇到任何分片出錯(cuò), 也是能保證所有分片回滾的, 即上邊展示的情況. 可是應(yīng)用一旦進(jìn)入commit過(guò)程, 若此時(shí)出現(xiàn)問(wèn)題, 其就無(wú)能為力了, 這也是Mycat稱(chēng)之為弱XA的原因.

上述commit過(guò)程是指: InnoDB prepare, write/sync Binlog, InnoDB commit(其歷經(jīng)5.5至5.7版本的多次迭代優(yōu)化, 這塊內(nèi)容也是精彩紛呈). 雖該階段一般不會(huì)出現(xiàn)問(wèn)題, 這也正暗示了Mycat在特殊情況下還不能保證分布式事務(wù)安全. 那應(yīng)用架構(gòu)中又如何實(shí)現(xiàn)可靠的分布式事務(wù)呢, 這又是另一個(gè)宏大的話題了...

若感興趣可關(guān)注訂閱號(hào)”數(shù)據(jù)庫(kù)最佳實(shí)踐”(DBBestPractice).

網(wǎng)頁(yè)題目：Mycat的使用-04.事務(wù)支持
網(wǎng)頁(yè)路徑：http://muchs.cn/article12/jepegc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供虛擬主機(jī)、網(wǎng)站營(yíng)銷(xiāo)、網(wǎng)站內(nèi)鏈、移動(dòng)網(wǎng)站建設(shè)、企業(yè)建站、品牌網(wǎng)站制作

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)