XA Transaction ~ Cross Databases Transaction

Describing XA Transaction, database transaction, postgresql database, mysql database, postgresql transaction, mysql transaction, cross transaction, two phase commit transaction and transaction

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 :-)

Describing XA Transaction, database transaction, postgresql database, mysql database, postgresql transaction, mysql transaction, cross transaction, two phase commit transaction and transaction