XA Transaction - Solution for Transaction More Than One Database

Have you ever think that it's too difficult for making database transaction in two different places (or databases) ? If so, XA Transaction is one of solutions. XA Transaction will make data for being consistent in both places and will be maintained in accordance with the principle of ACID.

In this XA Transaction, the concepts is 2PC or Two-Phase Commit. So, we could use 2 databases for our transaction and each database could make it's own transaction and then at the end of all transactions, we could join all the transaction to commit or rollback the transactions as the one action.

Here is an example application with XA Transaction 2PC in PostgreSQL, namely:

// Start a local transaction

BEGIN;

// Make the data change process ... it could be anything:-P

INSERT INTO dd_users (user_name, pass_word) VALUES ( 'hadi', 'ARIWIBOWO');

// Prepare this transaction to 2PC

Prepare TRANSACTION 'xatest';

// Doing the settlement for local transactions

COMMIT;

// ... Or

ROLLBACK;

// Now we can see the status of existing 2PC transaction with ...

SELECT * FROM pg_prepared_xacts;

// Finish 2PC to commit ...

Prepared COMMIT 'xatest';

// ... Or

Prepared ROLLBACK 'xatest';

For MySQL (version 5.0 and above), 2PC process may be done by:

// Start a transaction 2PC, unlike PostgreSQL

XA START 'xatest';

// Make the data change process ... it could be anything:-P

INSERT INTO dd_users (user_name, pass_word) VALUES ('hadi', md5 ('ARIWIBOWO'));

// Enter idle stage ...

XA END 'xatest';

// Preparing the transaction 2PC

XA Prepare 'xatest';

// Now we can see the status of existing 2PC transaction with ...

Recover XA;

// Finish 2PC to commit ...

XA COMMIT 'xatest';

// ... Or

XA ROLLBACK 'xatest';

Ok, that's all ... for implementation..it's up to you :-)