免费制作手机app,国内seo做最好的公司,纯流量卡免费申请入口,杭州绿城乐居建设管理有限公司网站本文最初于2016年底发表在我的个人微信公众号里面#xff0c;现略有修订后发布在这里。本文的技术信息针对的是mysql-5.7.x和mariadb-10.1.9。MariaDB和MySQL两者对分布式事务的支持有所不同#xff0c;总的来说MySQL的支持更好#xff0c;是完备的和可靠的(尽管后来也陆续发…本文最初于2016年底发表在我的个人微信公众号里面现略有修订后发布在这里。本文的技术信息针对的是mysql-5.7.x和mariadb-10.1.9。MariaDB和MySQL两者对分布式事务的支持有所不同总的来说MySQL的支持更好是完备的和可靠的(尽管后来也陆续发现了不少bug)而MariaDB的支持还有诸多问题先举例说明。本文测试用例使用MySQL-5.7.16和MariaDB-10.1.9 进行测试。MySQL/MariaDB对分布式事务的支持是根据 X/Open CAE document Distributed Transaction Processing: The XA Specification (http://www.opengroup.org/public/pubs/catalog/c193.htm) 主要包括下面这几个语句xa start gtid;xa end gtid;xa prepare gtid;xa commit gtid;xa rollback gtid;xa recover;外部的分布式事务管理器(transaction manager, TM) 可以使用这套XA命令来操作mysql 数据库按照XA标准的两阶段提交算法(2PC) 完成分布式事务提交。各个语句的意义见官方文档。其中mariadb的问题在于 xa prepare gtid 之后这个事务分支(transaction branch) 可能会丢失。详见下文。事先创建1个简单的表并且插入4行数据create table t1(a int primary key);insert into t1 values(1),(2),(3),(4);一。两者的公共行为(相同点)本节的查询是要说明xa prepare之前(无论是xa end 之前还是之后)xa事务的改动对外部不可见也不持久化退出连接就会丢失修改。xa事务信息本身也会在退出连接后消失重新连接后xa recover不会显示它。这些行为mysql与mariadb相同都是正确的。mysql use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql xa start 124;Query OK, 0 rows affected (0.00 sec)mysql insert into t1 values(5);Query OK, 1 row affected (0.00 sec)mysql insert into t1 values(6);Query OK, 1 row affected (0.00 sec)mysql quit;Byedaviddavid-VirtualBox:~/mysql_installs/mysql-5.7.16$ ./bin/mysql -S ../../mysql-instances/a/mysql.sockWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.7.16-debug-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type help; or \h for help. Type \c to clear the current input statement.mysql xa recover;Empty set (0.00 sec)mysql use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql xa start 124;Query OK, 0 rows affected (0.00 sec)mysql insert into t1 values(5);Query OK, 1 row affected (0.00 sec)mysql insert into t1 values(6);Query OK, 1 row affected (0.00 sec)mysql xa end 124;Query OK, 0 rows affected (0.00 sec)mysql quit;Byedaviddavid-VirtualBox:~/mysql_installs/mysql-5.7.16$ ./bin/mysql -S ../../mysql-instances/a/mysql.sockWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 6Server version: 5.7.16-debug-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type help; or \h for help. Type \c to clear the current input statement.mysql xa recover;Empty set (0.00 sec)mysql select*from t1;ERROR 1046 (3D000): No database selectedmysql use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql select*from t1;------| a |------| 1 || 2 || 3 || 4 |------4 rows in set (0.00 sec)二。MySQL的XA PREPARE 的行为1. 对MySQL来说在一个事务中执行xa prepare之后退出连接xa事务不丢失它的更新也不会丢失。mysql create table t1(a int);Query OK, 0 rows affected (0.05 sec)mysql xa start 123;Query OK, 0 rows affected (0.00 sec)mysql insert into t1 values(1),(2);Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0mysql insert into t1 values(3),(4);Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0mysql xa end 123;Query OK, 0 rows affected (0.00 sec)mysql xa prepare 123;Query OK, 0 rows affected (0.07 sec)mysql quit;Byedaviddavid-VirtualBox:~/mysql_installs/mysql-5.7.16$ ./bin/mysql -S ../../mysql-instances/a/mysql.sockWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.7.16-debug-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type help; or \h for help. Type \c to clear the current input statement.mysql xa recover;--------------------------------------------| formatID | gtrid_length | bqual_length | data |--------------------------------------------| 1 | 3 | 0 | 123 |--------------------------------------------1 row in set (0.00 sec)mysql xa commit 123;Query OK, 0 rows affected (0.01 sec)mysql select*From test.t1;------| a |------| 1 || 2 || 3 || 4 |------4 rows in set (0.00 sec)2. 对mysql来说在一个事务中执行xa prepare后kill掉 mysqld xa 事务的改动及其元数据不丢失mysql binlog系统与innodb做了协调一致的事务恢复非常完美。重启mysqld后客户端连接上去执行xa recover可以看到这个prepared事务执行 xa commit 可以完成该事务的提交。mysql xa start 124;Query OK, 0 rows affected (0.00 sec)mysql insert into t1 values(5);Query OK, 1 row affected (0.00 sec)mysql insert into t1 values(6);Query OK, 1 row affected (0.00 sec)mysql xa end 124;Query OK, 0 rows affected (0.00 sec)mysql xa prepare 124;Query OK, 0 rows affected (0.00 sec)mysql quit;Byedaviddavid-VirtualBox:~/mysql_installs/mysql-5.7.16$ ./bin/mysql -S ../../mysql-instances/a/mysql.sockWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 7Server version: 5.7.16-debug-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type help; or \h for help. Type \c to clear the current input statement.mysql xa recover;--------------------------------------------| formatID | gtrid_length | bqual_length | data |--------------------------------------------| 1 | 3 | 0 | 124 |--------------------------------------------1 row in set (0.00 sec)mysql quitBye[1] Killed ./bin/mysqld_safe --defaults-file../../mysql-instances/a/my.cnfdaviddavid-VirtualBox:~/mysql_installs/mysql-5.7.16$ ./bin/mysqld_safe --defaults-file../../mysql-instances/a/my.cnf [1] 22109daviddavid-VirtualBox:~/mysql_installs/mysql-5.7.16$ 2016-12-02T06:53:49.336023Z mysqld_safe Logging to /home/david/mysql-instances/a/datadir/david-VirtualBox.err.2016-12-02T06:53:49.350561Z mysqld_safe Starting mysqld daemon with databases from /home/david/mysql-instances/a/datadirdaviddavid-VirtualBox:~/mysql_installs/mysql-5.7.16$ ./bin/mysql -S ../../mysql-instances/a/mysql.sockWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.16-debug-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type help; or \h for help. Type \c to clear the current input statement.mysql xa recover;--------------------------------------------| formatID | gtrid_length | bqual_length | data |--------------------------------------------| 1 | 3 | 0 | 124 |--------------------------------------------1 row in set (0.00 sec)mysql select*from t1;ERROR 1046 (3D000): No database selectedmysql use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql select*from t1;------| a |------| 1 || 2 || 3 || 4 |------4 rows in set (0.00 sec)mysql xa commit 124;Query OK, 0 rows affected (0.00 sec)mysql select*from t1;------| a |------| 1 || 2 || 3 || 4 || 5 || 6 |------6 rows in set (0.00 sec)mysql3. mysql之所以能做到上述行为是因为mysql在xa prepare 做了binlog刷盘并且允许其后的xa commit被其他事务的binlog隔开。本例中新启动一个xa事务 T1在xa prepare 之后另启动一个连接在其中执行2个普通本地事务然后再做xa commit T1可以看到这个xa commit 的binlog与xa prepare被那两个新事务的binlog隔开了。对MySQL来说这不是问题。三。MariaDB的XA支持mariadb没有把xa start到xa prepare这一块操作作为一个单独的binlog事务并且在xa prepare时刻做binlog刷盘因此xa prepare之后一旦数据库连接断开或者mysqld重启那么binlog子系统中prepared状态的事务的binlog就消失了但是innodb当中这个prepared事务是存在的也就是说binlog与innodb数据不一致了。1. xa prepare 后连接断开那么这个prepared事务就消失了包括事务修改数据和元数据。binlog上面没有它的binlogxa recover也不能列出这个事务。这就错的更加离谱了可能是连接断开后mariadb把innodb中prepared的事务也回滚了。MariaDB [(none)] xa start 124;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)] insert into t1 values(5);ERROR 1046 (3D000): No database selectedMariaDB [(none)] use test;Database changedMariaDB [test] insert into t1 values(5);Query OK, 1 row affected (0.00 sec)MariaDB [test] insert into t1 values(6);Query OK, 1 row affected (0.00 sec)MariaDB [test] xa end 124;Query OK, 0 rows affected (0.00 sec)MariaDB [test] xa prepare 124;Query OK, 0 rows affected (0.00 sec)MariaDB [test] xa recover;--------------------------------------------| formatID | gtrid_length | bqual_length | data |--------------------------------------------| 1 | 3 | 0 | 124 |--------------------------------------------1 row in set (0.00 sec)MariaDB [test] quit;Bye[tdengineTENCENT64 ~/davie/mysql_installs/tdsql-mariadb-10.1.9-bin-release3/install]$./jmysql.sh 6678cmd: e/data/6678/prod/mysql.sock/data/home/tdengine/davie/mysql_installs/tdsql-mariadb-10.1.9-bin-release3Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 9Server version: 10.1.9-MariaDBV1.0R030D002-20161123-1511 Source distributionCopyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.Type help; or \h for help. Type \c to clear the current input statement.MariaDB [(none)] xa recover;Empty set (0.00 sec)MariaDB [(none)] use test;Database changedMariaDB [test] select*from t1;Empty set (0.00 sec)MariaDB [test]2. xa prepare 后kill掉mysqld那么这个prepared事务在binlog上面不存在不过xa recover能列出这个事务并且它的改动也存在。在innodb当中执行了XA PREPARE之后这个事务已经prepare了但是它的binlog没有写到binlog文件中。mysqld被kill掉重新拉起后innodb做了事务恢复所以可以看到它之前的改动但是binlog上面却没有这个事务。不过做xa commit 却又可以提交这个事务。之后这个事务的改动就可见了。但是innodb中的数据与binlog已经不一致了。备机上面没有事务内容MariaDB [test] xa start 125;Query OK, 0 rows affected (0.00 sec)MariaDB [test] insert into t1 values(9);Query OK, 1 row affected (0.00 sec)MariaDB [test] insert into t1 values(10);Query OK, 1 row affected (0.00 sec)MariaDB [test] xa end 125;Query OK, 0 rows affected (0.00 sec)MariaDB [test] xa prepare 125;Query OK, 0 rows affected (0.00 sec)MariaDB [test] quitBye[tdengineTENCENT64 ~/davie/mysql_installs/tdsql-mariadb-10.1.9-bin-release3/install]$./jmysql.sh 6678cmd: e/data/6678/prod/mysql.sock/data/home/tdengine/davie/mysql_installs/tdsql-mariadb-10.1.9-bin-release3Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 3Server version: 10.1.9-MariaDBV1.0R030D002-20161123-1511 Source distributionCopyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.Type help; or \h for help. Type \c to clear the current input statement.MariaDB [(none)] xa recover;--------------------------------------------| formatID | gtrid_length | bqual_length | data |--------------------------------------------| 1 | 3 | 0 | 125 |--------------------------------------------1 row in set (0.00 sec)MariaDB [(none)] select*from t1;ERROR 1046 (3D000): No database selectedMariaDB [(none)] use test;Database changedMariaDB [test] select*from t1;---| a |---| 5 || 6 || 7 || 8 |---4 rows in set (0.01 sec)MariaDB [test] xa commit 125;Query OK, 0 rows affected (0.00 sec)MariaDB [test] select*from t1;----| a |----| 5 || 6 || 7 || 8 || 9 || 10 |----6 rows in set (0.00 sec)MariaDB [test]从最初的测例开始一直没有任何插入数据行的binlog(write_row)写入。
