How to process distributed transaction within postgresql?

Question

Anyone can kindly tell me how to process distributed transaction within postgresql, which is also called "XA"? Is there any resources about it? Great thanks for any answer.

Answer 1

It looks like you are a bit confused. Generally database systems support two notions of distributed transaction types:

• Native distributed transactions and
• XA transactions.

Native distributed transactions are generally between different servers of the same RDBMS. Postgres also supports this with the dblink_exec command. Generally the connection to the other server is created by a so called database link. Postgres is a bit more clumsy to use then some other commercial grade RDBMS. You first need to install an extension to be able to use database links. However the postgres rdbms is managing the transaction.

XA transactions on the other hand are managed by the external transaction manager (TM) and each of the participating database has the role of a XA resource, which enlists with the transaction manager. The RDBMS can no longer decide itself when to commit a transaction. This is the task of the XA transaction manager. He uses a 2PC protocol to make sure the changes are applied or rolled back in a consistent manner across the databases.

On some OSes like windows a transaction manager is part of the operating system on others not. Generally java is shipped with a transaction manager and the corresponding data source needs to be configured to use XA.