Distributed Transaction

Introduction

The storage engine and WAL handle the atomicity of a database transaction in a single node, as described earlier.

A distributed transaction is a transaction that spawns over multiple database nodes. It is inherently complex because all nodes must commit together or abort. A subset of nodes committing and the rest aborting will lead to inconsistent results.

In this section, we will discuss the two-phase commit, which is a popular distributed transaction technique.

Two-phase commit

A two-phase commit (2PC) is a protocol for achieving an atomic distributed commit across multiple nodes, such that all nodes commit together or abort together. The protocol ensures that at no time does a subset of nodes commit and the rest abort.

These are the components involved in a 2PC:

• Coordinator: 2PC coordinates transactions across multiple nodes through a coordinator (or transaction manager). The coordinator is either a library or a process running on the client to orchestrate the distributed transaction.

• Participant: The database nodes involved in the transaction are called participants.

Workflow

A 2PC transaction begins with the coordinator generating a globally unique transaction identifier. Then, the coordinator starts the read and write operation on individual participants by attaching the generated transaction identifier. The coordinator immediately aborts the transaction if the operation fails in any participant node.

If all the operations of the transactions are successful for individual participants, 2PC happens in two phases:

• Prepare phase: When the client completes individual read/write operations on the individual participants, the coordinator sends a prepare request to all the participants with the global transaction identifier. The following scenarios may occur in the prepare phase:

  • If all the participants reply with a positive note to commit, the coordinator proceeds with the commit phase to make the data durable.

  • If any participant replies with a negative note to the prepare request, the coordinator sends an abort request. The request includes the transaction identifier to undo all the relevant operations.

  • If the request times out on participant nodes, the coordinator sends an abort request. The request includes the transaction identifier to undo all the relevant operations.

• Commit phase: If all the participants reply positively to the prepare commit, the coordinator first logs the decision to a transaction log local to the client. Then, the coordinator sends a commit request to all the participants and attaches the transaction identifier to make it durable.

Note: The checkpoint can be used to learn what decision is taken if the coordinator node goes down and recovers. It can also be used as the decision point if a new coordinator replaces the existing coordinator.