How to manage transactions over multiple databases

Question

I have a web application which receives requests to save orders in a database. I want to write to 2 different databases - one Cassandra instance and one PostgreSQL instance. I am using plain Java and JDBC (with apache DBUtis) with a lightweight web application library at the front.

What I am unsure about is how to implement transactionality across the two databases, i.e. if a write to one of the databases fails, then rollback the other write and put an error message in the error log.

Are there any mechanisms in Java to implement this? I know of such a thing as two phase commit, is that what I would be looking for here? Are there any alternatives?

Answer 1

Both Cassandra & PostgreSQL support linearizability and compare-and-set (CAS), so you can implement transactions on the client side.

If you want Serializable Isolation level then you should take a look on the Percolator's transactions. The Percolator's transactions are quite known in the industry and have been used in the Amazon's DynamoDB transaction library, in the CockroachDB database and in the Google's Pecolator system itself. A step-by-step visualization of the Percolator's transactions may help you to understand it.

If you expect contention and can deal with Read Committed isolation level then RAMP transactions by Peter Bailis may suit you. I also created a step-by-step RAMP visualization.

The third approach is to use compensating transactions also known as the saga pattern. It was described in the late 80s in the Sagas paper but became more actual with the raise of distributed systems. Please see the Applying the Saga Pattern talk for inspiration.