Why two phase commit

Why should I need two phase commit protocol?

This question is fundamental to understand when a Transaction Manager is useful for your application.

I think the best way to answer the question is a simple example.

This is the use case

You have a web store application to sell some products: it's a recent application developed for the web.
You have a warehouse application: it's a legacy application you developed some times ago.

The recent web store application uses PostgreSQL, the legacy warehouse application uses Oracle database server.

Until yesterday the web store application passed warehouse update to the warehouse application using a batch process: the web store application produced a file with all the orders, the warehouse application consumed the file to update the warehouse (I mean, this is not perfect, but it's just an example!)

Today the boss is knocking your door and asking: "Can we update the warehouse state every time something is sold?".

The answer must be: "Yes, we can" (you already know it!).

The naive approach

There's a naive approach to the problem: creating a program that updates both PostgreSQL and Oracle at the same time.

I'm using a supposed intuitive syntax to show the flow:

1. CHECK_WAREHOUSE(Oracle)
2. START_TRANSACTION(PostgreSQL)
3. START_TRANSACTION(Oracle)
4. UPDATE_ORDER(PostgreSQL)
5. UPDATE_WAREHOUSE(Oracle)
6. COMMIT_TRANSACTION(PostgreSQL)
7. COMMIT_TRANSACTION(Oracle)

The happy path works fine: for every order there's an update in PostgreSQL database and an update in Oracle database.

What may go wrong with the naive approach?

If step 6 failed, COMMIT_TRANSACTION(PostgreSQL) returned an error, you could change step 7 as below:

• ROLLBACK_TRANSACTION(Oracle)

If step 6 completed without errors and step 7 failed, how could you fix the issue?

Unfortunately the first transaction "COMMIT_TRANSACTION(PostgreSQL)" completed and it probably fired some business action like "package something and send to the customer": you would not be able to back-out (rollback) that operation because it was confirmed.

The answer to this question is compensating transaction, but you will discover it could be very difficult to develop simple and clean code using compensating transactions.

The issue becomes harder and harder adding a question like: "What happens if the application will crash while it's in the middle of steps 6 and 7?"

Historically, there's a standard solution, it's name is two phase commit.

The two phase commit approach

We can rewrite the flow in a different way:

1. CHECK_WAREHOUSE(Oracle)
2. START_GLOBAL_TRANSACTION(PostgreSQL, Oracle)
3. UPDATE_ORDER(PostgreSQL)
4. UPDATE_WAREHOUSE(Oracle)
5. COMMIT_GLOBAL_TRANSACTION(PostgreSQL, Oracle)

Easy enough?

We substituted two distinct START_TRANSACTION with only one START_GLOBAL_TRANSACTION and we substituted two distinct COMMIT_TRANSACTION with only one COMMIT_GLOBAL_TRANSACTION.

Where's the trick? Where's the issue?

Neither PostgreSQL nor Oracle gives you a

START_GLOBAL_TRANSACTION(PostgreSQL, Oracle)

or

COMMIT_GLOBAL_TRANSACTION(PostgreSQL, Oracle)

equivalent verb. Both give you these verbs:

START_TRANSACTION(Mydatabasetype)

COMMIT_TRANSACTION(Mydatabasetype)

you do need a Transaction Manager to perform Distributed Transaction Processing.

LIXA is an XA compliant Transaction Manager able to coordinate Distributed Transaction Processing between your Application Program and some Resource Managers (PostgreSQL and Oracle in the above example).

The TX (Transaction Demarcation) standard

Using the TX standard the flow of our program becomes:

1. tx_open()
2. CHECK_WAREHOUSE(Oracle)
3. tx_begin()
4. UPDATE_ORDER(PostgreSQL)
5. UPDATE_WAREHOUSE(Oracle)
6. tx_commit()
7. tx_close()

This is a pointer to the full specification: Distributed TP: The TX (Transaction Demarcation) Specification

Should I use two phase commit every time I could?

You generally don't have to use two phase commit just because you can use two phase commit.

In the past two phase commit was proposed as a generic solution, valid for every type of problem; this is not true, especially because:

• a Transaction Manager is itself a software component with all the good and bad consequences
• a two phase commit transaction is generally slower than two one phase commit transactions; this produce some effects: locks live longer and scalability will decrease if pessimistic lock is used.

But two phase commit can elegantly and effectively solve complex issues when data consistency between distinct databases is a must have condition.

Could I use two phase commit with something different than databases?

Potentially every process could support two phase commit (from e-mail to fax server), but practically two phase commit support has been developed only for some type of middlewares: databases, message oriented middlewares and something else.

The list of the Resource Managers available to LIXA managed Application Programs is available at FAQ: Frequently Asked Questions.