Using XA with databases that don't support it natively?

Question

Is there an open-source Java library that adds XA support to databases that don't support it natively? That is, it wraps a non-XA JDBC datasource and takes care of the necessary commits/rollbacks behind the scenes for 2-phase commits?

Answer 1

No, because it's impossible.

Let's review what XA is designed to achieve. It's a consensus protocol for guaranteeing ACID properties on transactions that span multiple resource managers. To do that it utilises a two phase commit protocol: the transaction manager prepares each resource manager, then commits each of them.

For the protocol to function correctly, the resource manager e.g. database, must make certain guarantees at the prepare stage. These include a) not making any changes visible to other processes until the commit phase ('Isolation'), b) ensuring it can perform the update at commit time if required, even if it crashes between prepare and commit ('Durability') and c) ensuring that data manipulated in different transactions exhibits the promised consistency properties. Realistically the only way to implement that is exclusive locking. Even resource managers e.g. pgsql and oracle, that use MVCC or other techniques during most operations will take exclusive locks at prepare.

Without access to the db internals, you can't acquire locks and hold them across connections. Hence you can't write code that can meet the transactional requirements. So, no layering of XA on top of a database engine - it has to be baked in.

However...

You can fake some aspects of the XA behaviour. Depending on your exact application requirements this may allow a useful solution to be crafted.

First up, you can use Last Resource Optimization (aka Last Resource Commit Optimization or Last Resource Gambit) to enlist a single non-XA i.e. one phase resource into a XA transaction with one or more real XA resources. By ordering the one phase resource last in the processing order you can achieve something that behaves like XA for most scenarios. It breaks horribly if a crash occurs at certain points in the execution, so you have to custom write data reconciliation code or rely on a human to handle that contingency. Depending on the semantics of your data that may or may not be an attractive option.

Next up, you can implement a custom driver that operates much like semantic replication. It records the sequence of SQL operations to a log at prepare time, but does not actually apply them to the db until the commit phase. This works for transactional updates that are isolated at the application level, but won't work if you're relying on the db to do concurrency control for you. For example, you may find the commit fails because something else snuck in a conflicting update between the prepare and commit phases. You could use an external lock manager, but only if your custom driver is the only thing talking to the db. As soon as a client that is not aware of that lock manager comes along all bets are off.

Finally, you can invert that model and use compensation based transactions under XA. In this model you apply the updates at prepare time and apply additional operations to reverse their effect in the rollback phase if needed. This has two drawbacks: concurrent operations may read and operate on the prematurely committed values of a tx that later rolls back, as there is no isolation between the prepare and commit; also depending on the business logic it's not easy to generate suitable compensation statements. Even if you can, you need quite a lot of complex plumbing to ensure they are run properly even in crash scenarios.

Realistically you're probably limited to LRCO, which is supported out of the box by most transaction managers. The other options require substantial transactions expertise to get right and the dev/test overhead usually isn't justified. If LRCO won't work for you then frankly it's going to be easier to redesign your app to avoid the need for XA.