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 make sure that one of two things happens: either all of the transaction's updates on all involved nodes are completed (the transaction commits) or none of the updates are completed (the transaction is rolled back). 2PC coordinates among the nodes to ensure this all-or-nothing behavior, also called transaction atomicity, is enforced.

In 2PC, a node is designated as the coordinator and is responsible for coordinating transaction commit with the other nodes. The  2PC protocol operates in two phases, a prepare phase and a commit phase. In the prepare phase, the coordinator contacts all nodes involved in the update and sends them a prepare request, each node responds with a vote to confirm if they are (or are not) OK to commit. Once all the votes are received, if all nodes have indicated they are OK to commit, the coordinator sends out commit requests. The nodes each commit the transaction locally and send an acknowledgement (ack) back to the coordinator to indicate their local commit is complete. If one or more nodes have voted NO indicating they cannot commit, the coordinator will send rollback requests to all the nodes to rollback the entire transaction.

Once the coordinator has received and recorded all the YES votes, the transaction is considered committed. If a node crashes or the connection between nodes is lost after this point, the logging process on all the nodes will ensure that all changes in the transaction are still recorded.

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 Music' WHERE Genre = 'Pop';

This query updates all the rows in the songs table that have Genre = 'Pop Music' to Genre = 'Pop'. The rows with Genre = 'Pop Music', which need to be updated, are distributed across multiple nodes, so this update involves multiple nodes. Since all the queries are run as transactions, this update must be atomic (all-or-nothing); that is either all rows with Genre='Pop Music' are updated or none of them are updated. Or, in other words, the updates in this query must be atomic, one of the ACID properties of transactions. The 2PC protocol helps ensure that atomicity.

The figure below illustrates 2PC on the songs table distributed across two nodes. The coordinator first sends prepare requests to both the nodes. The nodes then vote either YES or NO. If 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. If any of the nodes vote NO then the coordinator sends a rollback request to all the nodes.

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

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

The above query uses an UPDATE statement, the discussion and behavior apply to all data updates including INSERT and DELETE DML commands.

Troubleshooting 2PC

If an attempt to kill a query (transaction) occurs while 2PC is in process, the query may end up in an unknown state and the system will send the following error message. Note that the request to terminate a query may come from a user request or from the SingleStore system.

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 a transaction (or a DML command) was in 2PC at the time the request to kill the transaction was received and so the system cannot determine if the request was received in time to kill the transaction or not. In this case, the user needs to check their data given their knowledge of what the update was doing, and determine if the transaction was committed or rolled back. That is, the transaction will either be rolled back or committed; however, the system cannot tell which action occurred.

2PC Impacts

The 2PC feature is enabled by default on all SingleStore 8.5 versions and greater. The 2PC protocol causes a negligible performance impact. SingleStore recommends leaving 2PC enabled.