How to use MatrixOne Transaction?

This document will teach you how to simply start, commit, rollback a transaction, and how to automatically commit a transaction.

Start transaction

To start a transaction, you can start a transaction with START TRANSACTION, or you can use the dialect command BEGIN.

The code example is as follows:

  1. START TRANSACTION;
  2. insert into t1 values(123,'123');

Or:

  1. BEGIN;
  2. insert into t1 values(123,'123');

Commit transaction

When committing a transaction, MatrixOne accepts the COMMIT command as a commit command. The code example is as follows:

  1. START TRANSACTION;
  2. insert into t1 values(123,'123');
  3. commit;

Rollback transaction

When rolling back a transaction, MatrixOne accepts the ROLLBACK command as a commit command. The code example is as follows:

  1. START TRANSACTION;
  2. insert into t1 values(123,'123');
  3. rollback;

Autocommit

In MatrixOne, there is a parameter AUTOCOMMIT, which determines whether there is no single SQL statement to be automatically committed as an independent transaction without START TRANSACTION or BEGIN. The syntax is as follows:

  1. -- Set the value of this parameter
  2. SET AUTOCOMMIT={on|off|0|1}  
  3. SHOW VARIABLES LIKE 'AUTOCOMMIT';

When this parameter is set to ON or 1, it means automatic submission. All single SQL statements not in START TRANSACTION or BEGIN will be automatically submitted when executed.

  1. -- Autocommit
  2. insert into t1 values(1,2,3);

When this parameter is set to OFF or 0, it is not automatically committed. All SQL statements not in START TRANSACTION or BEGIN need to use COMMIT or ROLLBACK to perform commit or rollback.

  1. insert into t1 values(1,2,3);
  2. -- Manual submission is required here
  3. COMMIT;