XA Transactions between TokuDB and InnoDB

The recently released TokuDB brings many features. One of those features is support for XA Transactions. InnoDB already has support for XA Transactions.

XA Transactions are transactions which span multiple databases and or applications. XA Transactions use 2-phase commit, which is also the same method which MySQL Cluster uses.

Internal XA Transactions are used to keep the binary log and InnoDB in sync.

Demo 1: XA Transaction on 1 node:

mysql55-tokudb6> XA START 'demo01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> INSERT INTO xatest(name) VALUES('demo01');
Query OK, 1 row affected (0.01 sec)

mysql55-tokudb6> SELECT * FROM xatest;
+----+--------+
| id | name   |
+----+--------+
|  3 | demo01 |
+----+--------+
1 row in set (0.00 sec)

mysql55-tokudb6> XA END 'demo01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA PREPARE 'demo01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA COMMIT 'demo01';
Query OK, 0 rows affected (0.00 sec) 

This show a transaction with a transaction ID of 'demo01'.    

XA START Starts the transaction and puts it in ACTIVE state.  
XA END Ends the transaction and puts it IDLE state.
XA PREPARE Will prepare the transaction and puts it in PREPARED state.  
Then XA COMMIT can be used to COMMIT the transaction or XA ROLLBACK can be used to rollback the transaction.    

Demo 2: XA Transaction between 2 nodes:

mysql55-tokudb6> XA START 'tr01';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA START 'tr01';
Query OK, 0 rows affected (0.01 sec)

mysql55-tokudb6> INSERT INTO xatest(name) VALUES('tr01');
Query OK, 1 row affected (0.00 sec)

mysql56-innodb> INSERT INTO xatest(name) VALUES('tr01');
Query OK, 1 row affected (0.00 sec)

mysql55-tokudb6> XA END 'tr01';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA END 'tr01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA PREPARE 'tr01';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA PREPARE 'tr01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA COMMIT 'tr01';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA COMMIT 'tr01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> SELECT * FROM xatest;
+----+------+
| id | name |
+----+------+
|  1 | tr01 |
+----+------+
1 row in set (0.00 sec)

mysql56-innodb> SELECT * FROM xatest;
+----+------+
| id | name |
+----+------+
|  1 | tr01 |
+----+------+
1 row in set (0.00 sec) 

Demo 3: XA Transaction with rollback:

mysql55-tokudb6> XA START 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA START 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> INSERT INTO xatest(name) VALUES('tr02');
Query OK, 1 row affected (0.00 sec)

mysql56-innodb> INSERT INTO xatest(name) VALUES('tr02');
Query OK, 1 row affected (0.00 sec)

mysql55-tokudb6> XA END 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA END 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA PREPARE 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA PREPARE 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA ROLLBACK 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA ROLLBACK 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA ROLLBACK 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> SELECT * FROM xatest;
+----+------+
| id | name |
+----+------+
|  1 | tr01 |
+----+------+
1 row in set (0.00 sec)

mysql56-innodb> SELECT * FROM xatest;
+----+------+
| id | name |
+----+------+
|  1 | tr01 |
+----+------+
1 row in set (0.00 sec)

These transactions are between two MySQL instances: One with InnoDB and one with TokuDB. It's possible to run TokuDB and InnoDB both in one database instance, but separating them between instances (and hosts) might be needed for performance or some other reason.

It's possible to run a transaction with TokuDB and a PostgreSQL database:

mysql55-tokudb6> XA START 'tr03';
Query OK, 0 rows affected (0.00 sec)

xatest=# BEGIN;
BEGIN

mysql55-tokudb6> INSERT INTO xatest(name) VALUES('tr03');
Query OK, 1 row affected (0.00 sec)

mysql55-tokudb6> DELETE FROM xatest WHERE name='tr02';
Query OK, 0 rows affected (0.00 sec)

xatest=# INSERT INTO xatest(name) VALUES('tr03');
INSERT 0 1

mysql55-tokudb6> XA END 'tr03';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA PREPARE 'tr03';
Query OK, 0 rows affected (0.00 sec)

xatest=# PREPARE TRANSACTION 'tr03';
PREPARE TRANSACTION

mysql55-tokudb6> XA COMMIT 'tr03';
Query OK, 0 rows affected (0.00 sec)

xatest=# COMMIT PREPARED 'tr03';
COMMIT PREPARED

For PostgreSQL this only works if max_prepared_transactions is set to a non-zero value.

Transactions can not only run between databases but applications, filesystems and many other components can also be a member of the transactions. They need to support two-phase commit.

An XA Transaction is coordinated by a transaction coordinator which can be an application on a application server.