Skip to content

Explicit transactions

In the transaction category of MatrixOne Intelligence, explicit transactions also follow the following rules:

Explicit transaction rules

  • An explicit transaction refers to the beginning and ending with BEGIN...END or START TRANSACTION...COMMIT or ROLLBACK.
  • In explicit transactions, DML (Data Manipulation Language) and DDL (Data Definition Language) can exist at the same time, supporting DDL of all object types except databases and sequences.
  • In an explicit transaction, other explicit transactions cannot be nested, such as START TRANSACTIONS and then encounter START TRANSACTIONS. All statements between the two START TRANSACTIONS are forced to be submitted, regardless of the value of AUTOCOMMIT is 1 or 0.
  • In an explicit transaction, the SET command and management class command (CREATE USER/ROLE or GRANT) cannot exist, and can only contain DML and DDL.
  • In an explicit transaction, if a write-write conflict occurs when a new transaction is started without explicit commit or rollback, a previously uncommitted transaction will be rolled back and an error will be reported.

Differences from MySQL explicit transactions

Transaction Type Turn on AutoCommit Turn off AutoCommit
Explanatory transactions and autocommit When AUTOCOMMIT=1, MySQL does not make any changes to the transaction, and each statement is executed in a new autocommit transaction. When AUTOCOMMIT=0, each statement is executed in an explicitly opened transaction until the transaction is explicitly committed or rolled back.
Explanatory transactions and non-automatic commits When AUTOCOMMIT=1, MySQL automatically commits uncommitted transactions after each statement is executed. When AUTOCOMMIT=0, each statement is executed in an explicitly opened transaction until the transaction is explicitly committed or rolled back.

Example of explicit transaction behavior for MySQL and MatrixOne Intelligence

mysql> CREATE TABLE Accounts (account_number INT PRIMARY KEY, balance DECIMAL(10, 2));
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO Accounts (account_number, balance) VALUES (1, 1000.00), (2, 500.00);
Query OK, 2 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql> UPDATE Accounts SET balance = balance - 100.00 WHERE account_number = 1;
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE Accounts SET balance = balance + 100.00 WHERE account_number = 2;
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE Accounts SET balance = balance - 100.00 WHERE account_number = 1;
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE Accounts SET invalid_column = 0 WHERE account_number = 2;
ERROR 20101 (HY000): internal error: column 'invalid_column' not found in table
Previous DML conflicts with existing constraints or data format. This transaction has to be aborted
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM Accounts;
+----------------------------+
| account_number | balance |
+----------------------------+
| 1 | 900.00 |
| 2 | 600.00 |
+----------------------------+
2 rows in set (0.01 sec)

Cross-library transaction behavior example

MatrixOne Intelligence supports cross-library transaction behavior, which will be explained in a simple example here.

First, let's create two databases (db1 and db2) and their respective tables (table1 and table2):

-- Create a db1 database
CREATE DATABASE db1;
USE db1;

-- Create table1 in db1
CREATE TABLE table1 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    field1 INT
);

-- Create a db2 database
CREATE DATABASE db2;
USE db2;

-- Create table in db2 table2
CREATE TABLE table2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    field2 INT
);

Now, we have created two databases and their tables. Next, we will insert some data:

-- Insert data in table1 of db1
INSERT INTO db1.table1 (field1) VALUES (100), (200), (300);

-- Insert data in table2 of db2
INSERT INTO db2.table2 (field2) VALUES (500), (600), (700);

Now, we have data from two databases. Next, we will execute a cross-store transaction and modify the data in both databases at the same time:

-- Start cross-store transactions
START TRANSACTION;

-- Update table1's data in db1
UPDATE db1.table1 SET field1 = field1 + 10;

-- Update table2 data in db2
UPDATE db2.table2 SET field2 = field2 - 50;

-- Submit cross-store transactions
COMMIT;

In the cross-library transaction above, first use START TRANSACTION; to start the transaction, then update the respective table data in db1 and db2 respectively, and finally use COMMIT; to commit the transaction. If any step fails during a transaction, the entire transaction will be rolled back to ensure data consistency.

This example shows a complete cross-library transaction that can be more complex in practical applications. This simple example can help understand basic concepts and operations.