4

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?

jcm
  • 5,499
  • 11
  • 49
  • 78
  • 1
    Please have a look [here](http://stackoverflow.com/questions/128377/what-is-the-best-way-to-do-distributed-transactions-across-multiple-databases), which recommends _not_ doing this. Can you update your question to let us know how you plan on using the two databases? – Tim Biegeleisen Apr 01 '16 at 13:22
  • 1
    https://en.wikipedia.org/wiki/Java_Transaction_API – Gimby Apr 01 '16 at 13:25
  • 1
    I didn't mark it because we might discover that he doesn't need to write to both databases in one transaction. Perhaps finding an alternative to his business logic could avoid the two database transaction. – Tim Biegeleisen Apr 01 '16 at 13:33
  • I agree with Tim, this is a bad idea unless both writes are not related to each other or won't/can't effect each other, or something critical won't be lost. – We are Borg Apr 01 '16 at 13:40
  • The concept is called [two phase commit](https://en.wikipedia.org/wiki/Two-phase_commit_protocol). Postgres supports this http://www.postgresql.org/docs/current/static/sql-prepare-transaction.html. Don't know about Cassandra –  Apr 19 '16 at 06:54

1 Answers1

5

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.

rystsov
  • 1,868
  • 14
  • 16
  • Original Percolator provides only a Snapshot Isolation that is not Serializable. Even though its behavior is similar to classic 2PL, it does not put locks on reads (in that sense it's of class of pessimistic hybrid CC algorithms). It could be changed tho in a way to provide Serializability. – Ivan Prisyazhnyy Aug 04 '20 at 07:45