不同版本MySQL分布式事务-云栖社区-阿里云
2019年05月10日
原文
背景
MySQL内部分布式事务是通过二阶段提交和XA实现,中间件实现分布式事务有多种方式,比如参照MySQL的二阶段提交,即使用一个协调者实现,或者使用XA,即记录状态信息在table中。本文是验证MySQL内部分布式事务的现象,以及对比MySQL5.
背景
MySQL内部分布式事务是通过二阶段提交和XA实现,中间件实现分布式事务有多种方式,比如参照MySQL的二阶段提交,即使用一个协调者实现,或者使用XA,即记录状态信息在table中。本文是验证MySQL内部分布式事务的现象,以及对比MySQL5.6、MySQL5.7、MGR的分布式事务差异
测试环境
- CentOS 7.4
操作步骤(单机版MySQL5.6)
情况1
root@localhost : (none) 05:14:28> use test Database changed root@localhost : test 05:14:29> desc t3; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c1 | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) root@localhost : test 05:14:30> xa start 'my1'; Query OK, 0 rows affected (0.00 sec) root@localhost : test 05:14:35> insert into t3 values(1); Query OK, 1 row affected (0.00 sec) root@localhost : test 05:14:47> xa end 'my1'; Query OK, 0 rows affected (0.00 sec) root@localhost : test 05:14:55> exit
重新登录到MySQL
[root@my6-master /]# my Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 12 Server version: 5.6.43-log MySQL Community Server (GPL) ... root@localhost : (none) 05:15:17> xa recover; Empty set (0.00 sec)
binlog
[root@my6-master archive]# mysqlbinlog -vv mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #190314 17:14:13 server id 3306102 end_log_pos 120 CRC32 0xbc168bcd Start: binlog v 4, server v 5.6.43-log created 190314 17:14:13 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' '/*!*/; # at 120 #190314 17:14:13 server id 3306102 end_log_pos 151 CRC32 0xbcd00440 Previous-GTIDs # [empty] DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
情况2
root@localhost : (none) 05:15:22> xa start 'my2'; Query OK, 0 rows affected (0.00 sec) root@localhost : (none) 05:15:29> use test Database changed root@localhost : test 05:15:32> insert into t3 values(2); Query OK, 1 row affected (0.00 sec) root@localhost : test 05:15:33> xa end 'my2'; Query OK, 0 rows affected (0.00 sec) root@localhost : test 05:15:38> xa prepare 'my2'; Query OK, 0 rows affected (0.59 sec) root@localhost : test 05:15:45> exit
重新登录到MySQL
[root@my6-master /]# my Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 14 Server version: 5.6.43-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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 respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. root@localhost : (none) 05:15:55> xa recover; Empty set (0.00 sec)
binlog
[root@my6-master archive]# mysqlbinlog -vv mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #190314 17:14:13 server id 3306102 end_log_pos 120 CRC32 0xbc168bcd Start: binlog v 4, server v 5.6.43-log created 190314 17:14:13 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; # at 120 #190314 17:14:13 server id 3306102 end_log_pos 151 CRC32 0xbcd00440 Previous-GTIDs # [empty] DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
情况3:
root@localhost : (none) 05:15:59> xa start 'my3'; Query OK, 0 rows affected (0.00 sec) root@localhost : (none) 05:27:23> use test Database changed root@localhost : test 05:27:25> insert into t3 values(3); Query OK, 1 row affected (0.00 sec) root@localhost : test 05:27:27> xa end 'my3'; Query OK, 0 rows affected (0.00 sec) root@localhost : test 05:27:37> xa prepare 'my3'; Query OK, 0 rows affected (0.38 sec) root@localhost : test 05:27:45> xa commit 'my3'; Query OK, 0 rows affected (0.11 sec)
binlog
[root@my6-master archive]# mysqlbinlog -vv mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #190314 17:14:13 server id 3306102 end_log_pos 120 CRC32 0xbc168bcd Start: binlog v 4, server v 5.6.43-log created 190314 17:14:13 at startup # at 120 #190314 17:14:13 server id 3306102 end_log_pos 151 CRC32 0xbcd00440 Previous-GTIDs # [empty] # at 151 #190314 17:27:50 server id 3306102 end_log_pos 199 CRC32 0x4d06f9ac GTID [commit=yes] SET @@SESSION.GTID_NEXT= '269a748c-3521-11e9-bd4d-0242ac110005:1'/*!*/; # at 199 #190314 17:27:27 server id 3306102 end_log_pos 276 CRC32 0xe3a7d664 Query thread_id=14 exec_time=0 error_code=0 SET TIMESTAMP=1552555647/*!*/; SET @@session.pseudo_thread_id=14/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1073741824/*!*/; SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=1/*!*/; /*!C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=83/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 276 #190314 17:27:27 server id 3306102 end_log_pos 324 CRC32 0x15130f72 Rows_query # insert into t3 values(3) # at 324 #190314 17:27:27 server id 3306102 end_log_pos 369 CRC32 0x36536fac Table_map: `test`.`t3` mapped to number 70 # at 369 #190314 17:27:27 server id 3306102 end_log_pos 409 CRC32 0x61db4371 Write_rows: table id 70 flags: STMT_END_F BINLOG ' ### INSERT INTO `test`.`t3` ### SET ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ # at 409 #190314 17:27:50 server id 3306102 end_log_pos 487 CRC32 0x39bc761d Query thread_id=14 exec_time=0 error_code=0 SET TIMESTAMP=1552555670/*!*/; COMMIT /*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog *//*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
小结:
- MySQL 5.6 XA 事务分别在xa end和xa prepare后会话退出/崩溃,xa事务会回滚掉,xa prepare之前所有操作无法在binlog中记录
- xa recovery无法记录没有commit的事务
- MySQL 5.6 XA 事务和普通事务记录binlog的形式是一样的,没有体现 xa prepare
操作步骤(单机版MySQL5.7)
情况1
root@localhost : test 04:57:25> xa start 'my1'; Query OK, 0 rows affected (0.00 sec) root@localhost : test 04:57:31> insert into t3 values(1); Query OK, 1 row affected (0.00 sec) root@localhost : test 04:57:52> xa end 'my1'; Query OK, 0 rows affected (0.00 sec) root@localhost : test 04:57:59> exit
重新登录到MySQL
[root@master /]# my Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 7 Server version: 5.7.25-log MySQL Community Server (GPL) ... root@localhost : (none) 04:58:14> xa recover; Empty set (0.00 sec) root@localhost : test 04:58:16> select * from t3; Empty set (0.00 sec)
binlog
[root@master archive]# mysqlbinlog -vv mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #190314 16:57:13 server id 3306102 end_log_pos 123 CRC32 0x2035b77c Start: binlog v 4, server v 5.7.25-log created 190314 16:57:13 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' # at 123 #190314 16:57:13 server id 3306102 end_log_pos 154 CRC32 0x31517bb7 Previous-GTIDs # [empty] SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
情况2
root@localhost : (none) 04:58:33> xa start 'my2'; Query OK, 0 rows affected (0.00 sec) root@localhost : (none) 04:58:47> use test Database changed root@localhost : test 04:58:50> insert into t3 values(2); Query OK, 1 row affected (0.00 sec) root@localhost : test 04:58:51> xa end 'my2'; Query OK, 0 rows affected (0.00 sec) root@localhost : test 04:58:57> xa prepare 'my2'; Query OK, 0 rows affected (0.12 sec) root@localhost : test 04:59:06> exit; Bye
binlog
[root@master archive]# mysqlbinlog -vv mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #190314 16:57:13 server id 3306102 end_log_pos 123 CRC32 0x2035b77c Start: binlog v 4, server v 5.7.25-log created 190314 16:57:13 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; # at 123 #190314 16:57:13 server id 3306102 end_log_pos 154 CRC32 0x31517bb7 Previous-GTIDs # [empty] # at 154 #190314 16:59:05 server id 3306102 end_log_pos 219 CRC32 0x8aa31af9 GTID last_committed=0 sequence_number=1 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= '27c3e750-2376-11e9-aa51-0242ac110002:1'/*!*/; # at 219 #190314 16:58:51 server id 3306102 end_log_pos 310 CRC32 0x7f0987fa Query thread_id=7 exec_time=0 error_code=0 SET TIMESTAMP=1552553931/*!*/; SET @@session.pseudo_thread_id=7/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=83/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; XA START X'6d7932',X'',1 /*!*/; # at 310 #190314 16:58:51 server id 3306102 end_log_pos 358 CRC32 0xe885b2a1 Rows_query # insert into t3 values(2) # at 358 #190314 16:58:51 server id 3306102 end_log_pos 403 CRC32 0x2a2039e0 Table_map: `test`.`t3` mapped to number 109 # at 403 #190314 16:58:51 server id 3306102 end_log_pos 443 CRC32 0x0fcb989e Write_rows: table id 109 flags: STMT_END_F ### INSERT INTO `test`.`t3` ### SET ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ # at 443 #190314 16:59:05 server id 3306102 end_log_pos 532 CRC32 0xe69eb8b2 Query thread_id=7 exec_time=0 error_code=0 SET TIMESTAMP=1552553945/*!*/; XA END X'6d7932',X'',1 /*!*/; # at 532 #190314 16:59:05 server id 3306102 end_log_pos 571 CRC32 0x47bd8151 XA PREPARE X'6d7932',X'',1 XA PREPARE X'6d7932',X'',1 /*!*/; ROLLBACK /* added by mysqlbinlog */ /*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
重新登录到MySQL
[root@master /]# my Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 8 Server version: 5.7.25-log MySQL Community Server (GPL) ... root@localhost : (none) 04:59:36> xa recover; +----------+--------------+--------------+------+ | formatID | gtrid_length | bqual_length | data | +----------+--------------+--------------+------+ | 1 | 3 | 0 | my2 | +----------+--------------+--------------+------+ 1 row in set (0.00 sec) root@localhost : (none) 05:00:08> use test Database changed root@localhost : test 05:00:12> xa commit 'my2'; Query OK, 0 rows affected (0.36 sec) root@localhost : test 05:00:20> select * from t3; +----+ | c1 | +----+ | 2 | +----+ 1 row in set (0.00 sec)
binlog
[root@master archive]# mysqlbinlog -vv mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #190314 16:57:13 server id 3306102 end_log_pos 123 CRC32 0x2035b77c Start: binlog v 4, server v 5.7.25-log created 190314 16:57:13 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' '/*!*/; # at 123 #190314 16:57:13 server id 3306102 end_log_pos 154 CRC32 0x31517bb7 Previous-GTIDs # [empty] # at 154 #190314 16:59:05 server id 3306102 end_log_pos 219 CRC32 0x8aa31af9 GTID last_committed=0 sequence_number=1 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= '27c3e750-2376-11e9-aa51-0242ac110002:1'/*!*/; # at 219 #190314 16:58:51 server id 3306102 end_log_pos 310 CRC32 0x7f0987fa Query thread_id=7 exec_time=0 error_code=0 SET TIMESTAMP=1552553931/*!*/; SET @@session.pseudo_thread_id=7/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=83/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; XA START X'6d7932',X'',1 /*!*/; # at 310 #190314 16:58:51 server id 3306102 end_log_pos 358 CRC32 0xe885b2a1 Rows_query # insert into t3 values(2) # at 358 #190314 16:58:51 server id 3306102 end_log_pos 403 CRC32 0x2a2039e0 Table_map: `test`.`t3` mapped to number 109 # at 403 #190314 16:58:51 server id 3306102 end_log_pos 443 CRC32 0x0fcb989e Write_rows: table id 109 flags: STMT_END_F ### INSERT INTO `test`.`t3` ### SET ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ # at 443 #190314 16:59:05 server id 3306102 end_log_pos 532 CRC32 0xe69eb8b2 Query thread_id=7 exec_time=0 error_code=0 SET TIMESTAMP=1552553945/*!*/; XA END X'6d7932',X'',1 /*!*/; # at 532 #190314 16:59:05 server id 3306102 end_log_pos 571 CRC32 0x47bd8151 XA PREPARE X'6d7932',X'',1 XA PREPARE X'6d7932',X'',1 /*!*/; # at 571 #190314 17:00:19 server id 3306102 end_log_pos 636 CRC32 0x2d59a180 GTID last_committed=1 sequence_number=2 rbr_only=no SET @@SESSION.GTID_NEXT= '27c3e750-2376-11e9-aa51-0242ac110002:2'/*!*/; # at 636 #190314 17:00:19 server id 3306102 end_log_pos 728 CRC32 0x3af87320 Query thread_id=8 exec_time=1 error_code=0 SET TIMESTAMP=1552554019/*!*/; XA COMMIT X'6d7932',X'',1 /*!*/; ROLLBACK /* added by mysqlbinlog */ /*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
小结
- 通过上面的操作,发现在执行xa prepare后,xa prepare之前的操作都被记录到binlog,记录binlog的格式与其他普通事务不一致
- 在会话断开/崩溃后,通过xa recover可以查看到未提交的事务,可以通过操作xa commit使事务提交
- 如果MySQL有主从关系,主库在执行xa prepare后会话断开/崩溃;从库会回放xa prepare之前的所有操作。如果主库在恢复后没有对该xa事务commit;那么从库会在回放该xa事务后,会模拟主库断开会话,目的是解决避免该未提交xa事务堵塞后面的事务
操作步骤(MGR)
root@localhost : (none) 05:53:36> xa start 'my57'; Query OK, 0 rows affected (0.00 sec) root@localhost : (none) 05:53:41> use test Database changed root@localhost : test 05:53:49> insert into test1 values(2); Query OK, 1 row affected (0.00 sec) root@localhost : test 05:53:51> xa end 'my57'; Query OK, 0 rows affected (0.00 sec) root@localhost : test 05:53:54> xa prepare 'my57'; Query OK, 0 rows affected (0.00 sec) root@localhost : test 05:54:00> exit
binlog
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:145'/*!*/; # at 1073 #190314 17:53:51 server id 3306101 end_log_pos 1167 Query thread_id=203 exec_time=0 error_code=0 SET TIMESTAMP=1552557231/*!*/; XA START X'6d793537',X'',1 /*!*/; # at 1167 #190314 17:53:51 server id 3306101 end_log_pos 1214 Rows_query # insert into test1 values(2) # at 1214 #190314 17:53:51 server id 3306101 end_log_pos 1258 Table_map: `test`.`test1` mapped to number 119 # at 1258 #190314 17:53:51 server id 3306101 end_log_pos 1294 Write_rows: table id 119 flags: STMT_END_F ### INSERT INTO `test`.`test1` ### SET ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ # at 1294 #190314 17:54:00 server id 3306101 end_log_pos 1386 Query thread_id=203 exec_time=0 error_code=0 SET TIMESTAMP=1552557240/*!*/; XA END X'6d793537',X'',1 /*!*/; # at 1386 #190314 17:54:00 server id 3306101 end_log_pos 1422 XA PREPARE X'6d793537',X'',1 XA PREPARE X'6d793537',X'',1 /*!*/; # at 1422 #190314 17:55:14 server id 3306101 end_log_pos 1483 GTID last_committed=5 sequence_number=6 rbr_only=no
重新登录MySQL
[root@MGR1 ~]# mysql -uroot Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 204 Server version: 5.7.25-log MySQL Community Server (GPL) ... root@localhost : (none) 05:54:53> xa recover; +----------+--------------+--------------+------+ | formatID | gtrid_length | bqual_length | data | +----------+--------------+--------------+------+ | 1 | 4 | 0 | my57 | +----------+--------------+--------------+------+ 1 row in set (0.00 sec) root@localhost : (none) 05:55:01> xa commit 'my57'; Query OK, 0 rows affected (0.01 sec) root@localhost : test 05:58:21> select * from test1; +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.00 sec)
binlog
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:146'/*!*/; # at 1483 #190314 17:55:14 server id 3306101 end_log_pos 1574 Query thread_id=204 exec_time=0 error_code=0 SET TIMESTAMP=1552557314/*!*/; XA COMMIT X'6d793537',X'',1 /*!*/; ROLLBACK /* added by mysqlbinlog */ /*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
小结
- 结论与单机版MySQL5.7一致
- 通过测试证明MGR不支持集群内节点之间XA