MariaDB與MySQL對比 --- 對分散式事務的支持
本文最初於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 -A
Database 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;Byedavid@david-VirtualBox:~/mysql_installs/mysql-5.7.16$ ./bin/mysql -S ../../mysql-instances/a/mysql.sock
Welcome 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;Byedavid@david-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 its
affiliates. 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 -A
Database 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;Byedavid@david-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;Byedavid@david-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.cnfdavid@david-VirtualBox:~/mysql_installs/mysql-5.7.16$ ./bin/mysqld_safe --defaults-file=../../mysql-instances/a/my.cnf &[1] 22109david@david-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/datadirdavid@david-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)mysql> 3. mysql之所以能做到上述行為，是因為mysql在xa prepare 做了binlog刷盤，並且允許其後的xa commit被其他事務的binlog隔開。本例中，新啟動一個xa事務 T1，在xa prepare 之後，另啟動一個連接在其中執行2個普通本地事務，然後再做xa commit T1，可以看到這個xa commit 的binlog與xa prepare被那兩個新事務的binlog隔開了。對MySQL來說這不是問題。
推薦閱讀：
