XA Transactions in MySQL

下载 1

无羡

发布于

2164

人观看

#信息技术

随着应用程序的发展，分布式事务（XA）变得越来越重要。在本次网络研讨会中，我们将学习什么是分布式事务，以及MySQL如何实现XA规范。我们将学习必要的调查和调试技术，以确保跨不同环境的高可用性和数据一致性。本次网络研讨会的目的不是深入了解事务管理器，而是只关注资源管理器。它主要面向数据库管理员和站点可靠性工程师。

展开查看详情

3 .ACID Defined As ▪ Atomic - committed as a whole ▪ Consistent - the data is either valid, or rolled back to the last valid state ▪ Isolated - uncommitted transactions are isolated from other transactions ▪ Durable - once committed, transactions stay committed 3 © 2018 Percona

4 .Distributed Transaction Processing The XA or eXtended Architecture specification, developed by the Open Group describes the interaction between a Transaction Manager and one or more Resource Managers. Resource managers are disparate data sets whose transactions must ensure ACID compliance internally and with each other. 4 © 2018 Percona

6 .XA Transaction Flow Transaction Manager creates Transaction Manager distinct XID and instructs the Begin XA Resource Managers to start Transaction the transaction ▪ XID sent to Resource Managers XA Transaction Flow Resource Manager XA START XID ▪ XA transaction initiated with XA START XID on Resource Managers Resource Manager XA START XID 6 © 2018 Percona

7 .XA Transaction Flow Resource Managers initiate Transaction Manager and execute local Begin XA transactions. Local Transaction transactions are isolated to Resource Managers and are independent of each XA Transaction Flow Resource Manager other. XA START XID DML Resource Manager XA START XID DML DML 7 © 2018 Percona

8 .XA Transaction Flow After issuing the XID, Transaction Manager the Transaction Begin XA Transaction Is XID prepared? Manager polls the Resource Managers XA Transaction Flow Resource Manager for the transactions XA START XID DML to come to a PREPARED state. Resource Manager XA START XID DML DML 8 © 2018 Percona

9 .XA Transaction Flow When they have Transaction Manager completed the local Begin XA Transaction Is XID prepared? transactions, the Resource Managers XA Transaction Flow Resource Manager execute XA END and XA START XID DML XA END XID XA PREPARE XID XA PREPARE on the XID. Resource Manager XA PREPARE XA START XID DML DML XA END XID XID 9 © 2018 Percona

10 .XA Transaction Flow Only when ALL Transaction Manager Resource Managers Begin XA Transaction Is XID prepared? Commit XA Transaction have prepared the XID will the XA Transaction Flow Resource Manager Transaction Manager XA START XID DML XA END XID XA PREPARE XID give the command to EXECUTE. Resource Manager XA PREPARE XA START XID DML DML XA END XID XID 10 © 2018 Percona

11 .XA Transaction Flow Receiving the PREPARED Transaction Manager notification Begin XA Transaction Is XID prepared? Commit XA Transaction ▪ The Transaction Manager initiates the commit command when ALL Resource Managers report XA Transaction Flow Resource Manager XA PREPARE the XID prepared XA START XID DML XA END XID XID ▪ The Resource Managers XA COMMIT XID execute XA COMMIT XID as an atomic unit* Resource Manager XA PREPARE XA START XID DML DML XA END XID XA COMMIT XID XID 11 © 2018 Percona

14 .XID Composition xid = gtrid [, bqual [, formatID ]] • gtrid - global transaction identifier • bqual - branch qualifier • formatID - identifier of the format used by gtrid and bqual 14 © 2018 Percona

15 .Sample Tables and Trigger mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(7) NOT NULL AUTO_INCREMENT, `key_name` varchar(16) NOT NULL DEFAULT '', `val` varchar(256) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql> show create table t_i\G *************************** 1. row *************************** Table: t_i Create Table: CREATE TABLE `t_i` ( `id` int(7) NOT NULL AUTO_INCREMENT, `t_id` int(7) NOT NULL DEFAULT '0', `insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql> show triggers like 't'\G *************************** 1. row *************************** Trigger: ins_after_ins Event: INSERT Table: t Statement: begin insert into t_i (id, t_id, insert_time) values (NULL, new.id, NOW()); end Timing: AFTER 15 © 2018 Percona

16 .Example Transaction 1 mysql> XA START 'foo'; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t (key_name,val) VALUES ('foo','bar'); Query OK, 1 row affected (0.00 sec) mysql> XA END 'foo'; Query OK, 0 rows affected (0.00 sec) mysql> XA PREPARE 'foo'; Query OK, 0 rows affected (0.01 sec) 16 © 2018 Percona

17 .XA RECOVER 1 [vagrant@mysql1 ~]$ sudo mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from t; Empty set (0.00 sec) mysql> XA RECOVER; +----------+--------------+--------------+------+ | formatID | gtrid_length | bqual_length | data | +----------+--------------+--------------+------+ | 1 | 3 | 0 | foo | +----------+--------------+--------------+------+ 17 © 2018 Percona

18 .XA COMMIT 1 Connection 1: mysql> XA COMMIT 'foo'; Query OK, 0 rows affected (0.00 sec) mysql> Connection 2: mysql> SELECT * FROM t; +----+----------+-----+ | id | key_name | val | +----+----------+-----+ | 1 | foo | bar | +----+----------+-----+ 1 row in set (0.00 sec) mysql> SELECT * FROM t_i; +----+------+---------------------+ | id | t_id | insert_time | +----+------+---------------------+ | 1 | 1 | 2018-06-29 23:51:05 | +----+------+---------------------+ 1 row in set (0.00 sec) mysql> 18 © 2018 Percona

19 .Example Transaction 2 Connection 1 mysql> XA START '123456abcdefg','gfedcba',1234; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t (key_name,val) VALUES ('fop','bas'); Query OK, 1 row affected (0.00 sec) mysql> XA END '123456abcdefg','gfedcba',1234; Query OK, 0 rows affected (0.00 sec) mysql> XA PREPARE '123456abcdefg','gfedcba',1234; Query OK, 0 rows affected (0.00 sec) mysql> quit Bye [root@mysql1 log]# Connection 2 mysql> INSERT INTO t_i VALUES(2,2,'1999-01-01 00:00:01'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> 19 © 2018 Percona

20 .XA RECOVER and COMMIT Connection 2 mysql> XA RECOVER; +----------+--------------+--------------+----------------------+ | formatID | gtrid_length | bqual_length | data | +----------+--------------+--------------+----------------------+ | 1234 | 13 | 7 | 123456abcdefggfedcba | +----------+--------------+--------------+----------------------+ 1 row in set (0.00 sec) mysql> XA COMMIT '123456abcdefggfedcba'; ERROR 1397 (XAE04): XAER_NOTA: Unknown XID mysql> XA COMMIT '123456abcdefg','gfedcba',1234; Query OK, 0 rows affected (0.00 sec) 20 © 2018 Percona

21 .XA RECOVER CONVERT and COMMIT Converting the HEX value mysql> XA RECOVER CONVERT XID; +----------+--------------+--------------+--------------------------------------------+ | formatID | gtrid_length | bqual_length | data | +----------+--------------+--------------+--------------------------------------------+ | 1234 | 13 | 7 | 0x3132333435366162636465666767666564636261 | +----------+--------------+--------------+--------------------------------------------+ 1 row in set (0.01 sec) mysql> XA COMMIT '0x3132333435366162636465666767666564636261'; ERROR 1397 (XAE04): XAER_NOTA: Unknown XID mysql> XA COMMIT X'31323334353661626364656667',X'67666564636261'; Query OK, 0 rows affected (0.01 sec) 21 © 2018 Percona

22 .InnoDB Engine Status ------------ TRANSACTIONS ------------ Trx id counter 168236 Purge done for trx's n:o < 168236 undo n:o < 0 state: running but idle History list length 3 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 281479495003712, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 168231, ACTIVE (PREPARED) 61715 sec recovered trx 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 ---TRANSACTION 164647, ACTIVE (PREPARED) 63710 sec recovered trx 1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1 22 © 2018 Percona

24 .Binary Log Entry XA START X'4f170100cfc2',X'6c61736c63',1 /*!*/; # at 8993 #180701 17:43:18 server id 303 end_log_pos 9043 CRC32 0x251f5c99 Table_map: `test`.`t` mapped to number 116 # at 9043 #180701 17:43:18 server id 303 end_log_pos 9092 CRC32 0xfb138fdb Table_map: `test`.`t_i` mapped to number 117 … '/*!*/; ### INSERT INTO `test`.`t` ### SET ### @1=6 ### @2='for' ### @3='bat' ### INSERT INTO `test`.`t_i` ### SET ### @1=6 ### @2=6 ### @3='2018-07-01 17:43:18' # at 9190 #180701 17:43:32 server id 303 end_log_pos 9295 CRC32 0x938e96e6 Query thread_id=13 exec_time=0error_code=0 SET TIMESTAMP=1530467012/*!*/; XA END X'4f170100cfc2',X'6c61736c63',1 /*!*/; 24 © 2018 Percona

27 . DATABASE PERFORMANCE Champions of Unbiased Open Source Database Performance Matters MATTERS Database Solutions 27

1点赞

1收藏

1下载