This blog was co-authored by Abbas Butt
The research and development team in EnterpriseDB has been working hard in recent months on a solution for XA compatibility. The goal is to make our database, Postgres Plus Advanced Server compatible with the XA standard so it works with commercially available transaction managers. EDB’s Postgres Plus database is an RDBMS that integrates enterprise-class performance, security and manageability enhancements as well as database compatibility for Oracle into community PostgreSQL so global brands can take advantage of the benefits of Postgres.
In a nutshell, the purpose of XA compatibility is to support distributed transactions in a heterogeneous environment. As an XA compatible database, Postgres Plus can participate in a distributed transaction as a resource manager. Before we go into the specifics of the XA standard and architecture, it is imperative to define what we mean by a distributed transaction.
What is a distributed transaction?
A transaction is used to define a logical unit of work that either wholly succeeds or has no effect whatsoever. A distributed transaction is a transaction that involves two or more network hosts. Distributed transactions, like all other transactions, must have all four ACID properties: atomicity, consistency, isolation and durability. Atomicity is what guarantees all-or-nothing outcomes for the unit of work.
The work being performed (within the bounds of a transaction) can occur on different platforms and can involve different databases from different vendors. As a result, a standard has developed to allow a manager process to coordinate and control the behavior of the databases.
X/Open is a standards body that developed the Distributed Transaction Processing Model and the XA interface to solve the problem of heterogeneous databases.
X/Open applications run in a distributed transaction processing (DTP) environment. An X/Open application calls on resource managers (RMs) to provide a variety of services. Resource managers interact with a transaction manager (TM), which controls all transactions for the application.
The X/Open XA interface is a specification that describes the protocol for transaction coordination, commitment, and recovery between a Transaction Manager and one or more Resource Managers.
Some of the common XA functions that PPAS will support include the following:
int xa_open(char *xa_info, int rmid, long flags);
The xa_open function opens a connection with a resource manager.
int xa_close(char *xa_info, int rmid, long flags);
The xa_close function closes an open connection with a resource manager.
int xa_start(XID *xid, int rmid, long flags);
The xa_start starts a new transaction with a resource manager.
int xa_rollback(XID *xid, int rmid, long flags);
The xa_rollback function would rollback a transaction in case part of a transaction failed.
int xa_prepare(XID *xid, int rmid, long flags);
The xa_prepare function would create a prepared transaction on a resource manager.
int xa_commit(XID *xid, int rmid, long flags);
The xa_commit function would send commit message to resource manager.
There are many commercially available transaction managers, for example Oracle Tuxedo, IBM Websphere MQ and IBM Tx Series. The XA compatibility in EDB Postgres Plus Advanced Server means that one or more Postgres Plus instances can participate as resource manager(s) in a distributed transaction with any commercially available transaction manager that supports the XA protocol.
A typical example of an XA application is a banking application. A common banking transaction involves touching multiple accounts, crediting from one and debiting from another. A transaction like this is a distributed transaction that needs to comply with ACID properties.
The diagram above provides a high level view of distributed environment, with a Tuxedo transaction manager used as an example here. Postgres Plus would work with any XA compatible transaction manager.
As illustrated in the diagram above, multiple concurrent clients are supported, and the maximum number of concurrent clients depends on the transaction manager configuration. Multiple resource managers can participate in a transaction, and the resource manager can be from different database vendors and can run on different OS platforms, as long as they are compatible with the XA protocol. The Postgres Plus connection manager acts as middleware between Postgres Plus and Transaction Manager. The connection manager will handle connection pooling and connection sharing.
Every application in Tuxedo spawns a minimum of TWO instances of the TMS (Transaction Manager Server). The same resource manager connection needs to be shared between the instances of TMS and the application. The connection manager shown in the above diagram will take care of this connection sharing.
In a financial application, the following are of paramount importance:
- High Availability
All the communication that happens between the various components can be encrypted and all components support the appropriate authentication methods. High availability is also very important for financial application so appropriate automatic failover needs to be in place for all the components participating in the transaction. The number of concurrent clients and throughput (measured in TPS) is also of prime importance. We have not found a benchmarking solution that does benchmarking for distributed transactions, so the plan is to design something ourselves that can be used for measuring performance and reporting performance numbers.
In order to deploy a Postgres Plus XA solution, the user would need the Postgres Plus connection manager, which acts as a middleware layer. The XA switch file (shown as TMS in architecture diagram) is a loadable module that is required by the transaction manager.
Current Status & Public availability
We are currently in a process of running the sample banking application that is shipped with Tuxedo transaction manager with PPAS as the RM. It is a comprehensive financial application and if run successfully would be a good testament for an XA compatible Postgres Plus solution. We will publish a “how-to” document that would describe the steps required to run the Tuxedo banking sample application with PPAS.
So far most of the testing done was through a sample application that works on command line, we are also planning to develop a sample web-based applications, which are the most common use case. We are also planning to do some benchmarking using the banking application. A white paper for Postgres Plus XA compatibility will be published in due course with relevant information.
In addition to above, we are also engaging with pilot customers to try our solution in their environment.
The public availability of this is still a subject of discussion. It won’t be ambitious to say that we may have phase I ready for Postgres Plus Advanced Server 9.5.
Ahsan Hadi is Senior Director Product Development at EnterpriseDB.
Abbas Butt is a Database Architect at EnterpriseDB.