XA Transactions in MySQL

1 . XA Transactions in MySQL An overview and troubleshooting guide to distributed transactions Dov Endress Senior MySQL DBA July 25th 2018 1 © 2016 Percona 

2 .ACID Compliant Distributed Transactions Distributed Transaction Process 2 © 2016 Percona 

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 

5 .XA Transaction Flow Sample transaction walkthrough 5 © 2016 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 

12 .Working with XA Transactions Interaction with Transactions, De-Bugging, and Investigative Techniques 12 © 2016 Percona 

13 .Command Overview • XA START XID • XA END XID • XA PREPARE XID • XA COMMIT XID • XA ROLLBACK XID • XA RECOVER [CONVERT XID] 13 © 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 

23 .Example Transaction 3 mysql> XA START X'4F170100CFC2',X'6C61736C63'; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t (key_name,val) VALUES ('for','bat'); Query OK, 1 row affected (0.00 sec) … mysql> XA RECOVER; +----------+--------------+--------------+-------------+ | formatID | gtrid_length | bqual_length | data | +----------+--------------+--------------+-------------+ | 1 | 6 | 5 | O ??laslc | +----------+--------------+--------------+-------------+ 1 row in set (0.00 sec) mysql> XA RECOVER CONVERT XID; +----------+--------------+--------------+--------------------------+ | formatID | gtrid_length | bqual_length | data | +----------+--------------+--------------+--------------------------+ | 1 | 6 | 5 | 0x4F170100CFC26C61736C63 | +----------+--------------+--------------+--------------------------+ 1 row in set (0.00 sec) mysql> 23 © 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 

25 .Other Considerations and Limitations •Only supported by InnoDB storage engine •Distinct bqual MySQL only •Flush tables with read lock •Replication 25 © 2018 Percona 

26 .Conclusion Synopsis + Q&A 26 © 2016 Percona 

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