What is a two phase commit / XA transaction

A two-phase commit is a standardized protocol that ensures that a database commit is done properly (atomic) over multiple resources. But what does this mean?

Let us assume you have an application which has multiple resources for changing data. E.g. you have an enterprise application which has:

• local application SQL database
• a foreign application SQL database
• a messaging broker

Now let us assume these 3 resources are needed for a simple business operation: transfer money from one bank account to a different bank account.
Let us dive deeper into the system architecture:

• The local database contains all bank accounts on your application side
• The second database is the remote bank account of a daughter bank, and you have access to the bank accounts via the foreign application database
• For law regulations you need to ensure that all transactions are also published in the transaction log - the messaging broker

Now let's say you need to transfer 500 USD from local to foreign bank. This would result in the following basic operations:

1. update bank account on local database by subtracting -500 USD from the current balance
2. update bank account on the foreign database by adding 500 USD to the current balance
3. send a message to the broker saying from which account to which account a transfer of 500 USD was made

The straight forward way to implement this would be to do a "commit" to a resource after reading and updating each resource. This works well if all resources confirm the commit. But what happens if the local database reported a commit but the second database reports an error and the commit fails?
The local bank account would have a reduced balance of 500 USD but the receiver will never get the 500 USD.
(Just thinking if I could be the owner of a local bank ;-) great profits in the near future :-) )

To solve the issue we need some sort of a surrounding management for the whole transaction over all resources. This means that at the start of our money transfer we need to tell all resources:

Hey I want to start a transaction over multiple resources (distributed transaction), please keep in mind that I will update some data on your resource, but you will get one final commit after all my resources reported that the local commit will go through without any error. Otherwise ensure to rollback the change, as if I have never asked you to change anything.

Already in the early days of distributed computing the engineers have introduced a distributed transaction management for such cases. There are many names for it:

• two phase commit (commonly used)
• 2-phase commit
• 2pc (abbreviation for both upper namings, commonly used)
• distributed transaction
• XA transaction (commonly used)

1. What does the XA in XA transaction mean?

XA ist the abbreviation for the X/Open XA standard specification (XA stands for eXtended Architecture) of the Open Group, which defined a common standard for the Distributed Transaction Processing. This standard describes specifically how Resources (with resource managers) and Transactions (with transactions managers aka XA coordinator) are done.

2. How is the 2pc / two phase commit implemented?

In the first phase of the protocol, called a commit-request phase or a voting phase, a coordinator (normally the process that initiated the commit) gets the approval or rejection to commit the data changes of involved processes. Only if participants/subscribers approve it the coordinator decides to commit, otherwise it decides to abort.

In the second phase (a commit phase) the coordinator informs the participants about the result. According to the result, either the entire transaction is rolled back or all the subtransactions are successfully completed and the temporarly locked resources are released.

During the two phases, the following messages are exchanged between the coordinator and the participants:

2.1. Commit – request phase (also called voting phase):

1. The coordinator sends a query to commit a message to all participants and waits for their answers.
2. Participants process the transaction and either commit it or do a rollback. They write the entries in their undo and redo log.
3. Participants respond to the coordinator with “ready” if the transaction was successful, or with “failed” if the transaction failed.

2.2. Commit phase:

1. If the coordinator receives a “ready” message from all the participants:
   1. Coordinator sends commit to all participants.
   2. Participants can complete the transaction with a commit and release all locks and resources.
   3. Participants send back an acknowledgement.
   4. The coordinator completes the transaction if it receives an acknowledgement from all the participants.
2. In case one of the participants responds with a “failed” message
   1. Coordinator sends abort to all participants.
   2. Participants terminate the transaction with a rollback (using the undo log) and release all locks and resources.
   3. Participants send an acknowledgement to the coordinator
   4. Coordinator will rollback the transaction after receiving an acknowledgement from all the participants.

3. Disadvantages

2pc is a blocking protocol, which means that if the coordinator fails permanently, some participants will never resolve their transactions, which might result in indefinite blocking. To eliminate this issue, we can use 3pc protocol. You can read more about it in What is a 3 phase commit / 3pc?