Two-Phase Transaction Commit

The 2PC Protocol

The 2PC protocol ensures that all changes in a transaction are either committed or rolled back. Transactions in distributed database systems such as SingleStore modify data on multiple nodes. To maintain the integrity of a transaction, the database must ensure that one of the following two things happens:

• The transaction commits: All of the transaction's updates on all involved nodes are completed.

• The transaction rolls back: None of the updates are completed.

The 2PC protocol coordinates among the nodes to ensure this all-or-nothing behavior, also called transaction atomicity, is enforced.

2PC Workflow

In 2PC, a node is designated as the coordinator and is responsible for coordinating transaction commits across other participating nodes. In SingleStore, the coordinator role is performed by the aggregator node(s).

The 2PC protocol operates in two phases, a prepare phase and a commit phase.

2PC Example

Consider a songs table and the following query. Assume that there are millions of rows in the songs table which are distributed across many nodes.

CREATE TABLE songs(Id INT,
    Name TEXT,
    Genre TEXT,
    Rating DOUBLE,
    PublishTimestamp DATETIME);

UPDATE songs
SET Genre = 'Pop' WHERE Genre = 'Pop Music';

This query updates all the rows in the songs table that have Genre = 'Pop Music' to Genre = 'Pop'. As the rows that need to be updated are distributed across multiple nodes, this update also involves multiple nodes. Because this query runs as a transaction, this update must be atomic (all-or-nothing); either every row with Genre='Pop Music' is updated, or none are. This is called atomicity, one of the ACID properties of transactions, and 2PC helps guarantee it.

The following figure illustrates 2PC for the songs table when the affected rows are distributed across two nodes. The coordinator first sends prepare requests to both the nodes. The nodes then vote either YES or NO. When both the nodes vote YES, then commit requests are sent to both nodes. The nodes commit the transactions locally, and respond with an ack to the coordinator. The commit phase completes when all participating nodes have committed their local transactions and acknowledged the outcome. If any node votes NO, the rollback phase completes when all participating nodes have rolled back their local transactions and acknowledged the outcome.

In SingleStore, the coordinator role is performed by the aggregator and Node 1, Node 2 are leaf nodes.

All queries such as the preceding are run in transactions. The BEGIN, COMMIT, and ROLLBACK statements can be used to run multi-statement transactions, i.e., run multiple updates within a transaction. The same 2PC process is used for those transactions.

The discussion and behavior apply to all data updates including INSERT and DELETE DML commands.

Troubleshooting 2PC

When a distributed transaction enters 2PC, an interrupt or failure can prevent SingleStore from returning a confirmed final result to the client. For example, a kill query request, client disconnection, or another failure that interrupts processing or commit decisions. In this case, the query may end up in an unknown state and return the following error:

OperationalError: 1997: Attempted to interrupt transaction execution, but the outcome is unknown because the transaction was already in its commit phase. Please check if it succeeded.

This error message means that the transaction outcome is unknown from the client's perspective. It does not mean that SingleStore partially applied the transaction or the database is in an inconsistent state. The transaction still resolves atomically; it is either fully committed or fully rolled back. If all participants prepared successfully, the final outcome is a commit. Otherwise, the final outcome is a rollback.

Therefore, when Error 1997 is returned, verify the final result in the affected data before retrying application logic.

2PC Impacts

The 2PC feature is enabled by default on all SingleStore 8.5 and later and has a negligible performance impact.