Implicit transactions

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

Implicit transaction rules

MatrixOne Intelligence When AUTOCOMMIT changes, errors will be handled for implicit transactions that are not committed, and the user will be prompted that the change needs to be submitted first.
In the case of AUTOCOMMIT=0 and active transactions are currently present, the modification of the automatic commit and isolation levels, as well as the management class and parameter setting commands, such as SET, CREATE USER/ROLE or authorization operations will also be restricted.
When AUTOCOMMIT=1, each DML statement is considered a separate transaction and is submitted immediately after execution.
In the case of AUTOCOMMIT=0, each DML statement will not be submitted immediately after execution, and the COMMIT or ROLLBACK operation needs to be manually executed. If the client is exited without committing or rollback, the rollback operation is performed by default.
In the case of AUTOCOMMIT=0, DML and DDL can exist in an implicit transaction at the same time, but the object type of DDL cannot be database or sequence.
If the CREATE/DROP DATABASE or CREATE/DROP SEQUENCE operation occurs when AUTOCOMMIT=0, all content that was not submitted before is forced to be submitted. Meanwhile, the CREATE/DROP DATABASE operation will be submitted immediately as a separate transaction.
When an explicit transaction exists that have not been committed, if an explicit transaction is initiated, the previously uncommitted content will be forced to commit.

Difference between MatrixOne Intelligence and MySQL Implicit Transactions

In MatrixOne Intelligence, if an implicit transaction is enabled (SET AUTOCOMMIT=0), all operations require manual execution of COMMIT or ROLLBACK to end the transaction. By contrast, MySQL is automatically submitted when encountering a DDL or similar SET statement.

MySQL Implicit Transaction Behavior

Transaction Type	Turn on AutoCommit	Turn off AutoCommit
Implicit Transactions and AutoCommits	When `AUTOCOMMIT=1`, MySQL does not make any changes to the transaction, and each statement is a separate AutoCommit transaction.	When `AUTOCOMMIT=0`, MySQL does not make any changes to the transaction, but subsequent statements will continue to be executed in the current transaction until the transaction is explicitly committed or rolled back.
Implicit transactions and non-automatic commits	When `AUTOCOMMIT=1`, MySQL will automatically commit uncommitted transactions after each statement is executed.	When `AUTOCOMMIT=0`, MySQL continues to execute subsequent statements in the current transaction until the transaction is explicitly committed or rolled back.

MySQL Implicit Transaction Behavior Example

mysql> select @@SQL_SELECT_LIMIT;
+--------------------------+
| @@SQL_SELECT_LIMIT |
+--------------------------+
| 18446744073709551615 |
+--------------------------+
1 row in set (0.01 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set SQL_SELECT_LIMIT = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@SQL_SELECT_LIMIT;
+-------------------------+
| @@SQL_SELECT_LIMIT |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.01 sec)

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

mysql> select @@SQL_SELECT_LIMIT;
+-------------------------+
| @@SQL_SELECT_LIMIT |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set SQL_SELECT_LIMIT = default;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@SQL_SELECT_LIMIT;
+--------------------------+
| @@SQL_SELECT_LIMIT |
+--------------------------+
| 18446744073709551615 |
+--------------------------+
1 row in set (0.00 sec)

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

mysql> select @@SQL_SELECT_LIMIT;
+--------------------------+
| @@SQL_SELECT_LIMIT |
+--------------------------+
| 18446744073709551615 |
+--------------------------+
1 row in set (0.00 sec)

MatrixOne Intelligence Implicit Transaction Behavior Example

mysql> select @@SQL_SELECT_LIMIT;
+--------------------------+
| @@SQL_SELECT_LIMIT |
+--------------------------+
| 18446744073709551615 |
+--------------------------+
1 row in set (0.01 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set SQL_SELECT_LIMIT = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@SQL_SELECT_LIMIT;
+-------------------------+
| @@SQL_SELECT_LIMIT |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)

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

mysql> select @@SQL_SELECT_LIMIT;
+-------------------------+
| @@SQL_SELECT_LIMIT |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.01 sec)

mysql> set autocommit=0;
ERROR 20101 (HY000): internal error: Uncommitted transaction exists. Please commit or rollback first.

Implicit transaction example

For example, inserting data (4,5,6) to t1 becomes an implicit transaction. Whether the implicit transaction is committed immediately depends on the value of the AUTOCOMMIT parameter:

CREATE TABLE t1(a bigint, b varchar(10), c varchar(10));
START TRANSACTION;
INSERT INTO t1 values(1,2,3);
COMMIT;

-- View AUTOCOMMIT switch parameters
mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
+---------------------+
| Variable_name | Value |
+---------------------+
| autocommit | 1 |
+---------------------+
1 row in set (0.00 sec)
-- An implicit transaction is started here, and in the case of AUTOCOMMIT=1, each DML is submitted immediately after execution
insert into t1 values(4,5,6);

-- Implicit transactions are automatically submitted, the table data is as follows
mysql> select * from t1;
+------+------+
| a | b | c |
+------+------+
| 1 | 2 | 3 |
| 4 | 5 | 6 |
+------+------+
2 rows in set (0.00 sec)