Two-Phase Transaction Commit

Example

CREATE TABLE orders (
   order_id BIGINT(11) NOT NULL,
   customer_id INT(11) NOT NULL,
   order_date DATE NOT NULL,
   order_status CHAR(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
   SHARD KEY (order_id),
   SORT KEY (order_date));

In the following transaction, a set of rows is deleted from the order table. This operation spans multiple partitions:

BEGIN;
   START TRANSACTION;
   DELETE FROM orders    
   WHERE order_date BETWEEN '1994-01-01' AND '1997-12-31';
COMMIT;

The 2PC protocol ensures that this operation is atomic, even if there is a failure during the COMMIT processing.

How to Enable the Two-Phase Commit Feature

The 2PC feature is enabled by default on all SingleStore 8.5 versions and greater.

SingleStore Helios users must contact support to turn this feature on or off.

The 2PC feature is not backward compatible with version 8.1.