Transaction usage guide

This chapter introduces you how to simply start, commit, and roll back a transaction, and how to automatically commit a transaction.

Start transactions

To enable a transaction, you can start a transaction by using START TRANSACTION or use the dialect command BEGIN. Code example:

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

or:

BEGIN;
insert into t1 values(123,'123');

Submit transaction

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

START TRANSACTION;
insert into t1 values(123,'123');
commit;

Roll back transactions

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

START TRANSACTION;
insert into t1 values(123,'123');
rollback;

Automatic submission

In MatrixOne Intelligence, there is a parameter AUTOCOMMIT that determines whether a single SQL statement is automatically committed as a standalone transaction without START TRANSACTION or BEGIN. The syntax is as follows:

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

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

-- Automatic submission at this time
insert into t1 values(1,2,3);

When this parameter is set to OFF or 0, it means that non-automatic submissions are made. All SQL statements not in START TRANSACTION or BEGIN need to use COMMIT or ROLLBACK to perform submission or rollback.

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