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 On-Premises users can turn this feature off as follows:

SET GLOBAL two_phase_commit = OFF;

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