Implicit Transactions in MatrixOne

In MatrixOne’s Implicit transactions also obey the following rules:

Implicit transaction rules

  • When AUTOCOMMIT changes, if the implicit transaction is not committed, MatrixOne will report an error and prompt the user to submit the change.

  • AUTOCOMMIT=0, and when there are no active transactions, DDL (Data Definition Language) and parameter configuration files can be executed.

  • In the case of AUTOCOMMIT=1, each DML (Data Manipulation Language, Data Manipulation Language) statement is a separate transaction and is committed immediately after execution.

  • In the case of AUTOCOMMIT=0, each DML statement will not be submitted immediately after execution, and COMMIT or ROLLBACK needs to be performed manually. If the client exits without committing or rolling back, then Rollback by default.

  • In the case of AUTOCOMMIT=0, DML and DDL can exist at the same time.

  • If a CREATE/DROP DATABASE or CREATE/DROP SEQUENCE operation occurs in the state of AUTOCOMMIT=0, all previously uncommitted content will be forced to be committed. Also, the CREATE/DROP DATABASE functions will be committed immediately as a separate transaction.

  • When there is uncommitted content in the implicit transaction, opening an explicit transaction will force the submission of the previously uncommitted content.

The difference between MatrixOne and MySQL implicit transactions

In MatrixOne, if the implicit transaction is enabled (SET AUTOCOMMIT=0), all operations must manually execute COMMIT or ROLLBACK to end the transaction. In contrast, MySQL automatically commits when encountering a DDL or similar SET statement.

MySQL Implicit Transaction Behavior

Transaction typeTurn on autocommitTurn off autocommit
Implicit transactions and autocommitWhen AUTOCOMMIT=1, MySQL will not change the transaction; each statement is a separate auto-commit transaction.When AUTOCOMMIT=0, MySQL also does not change the transaction, but subsequent statements continue to execute within the current transaction until the transaction is explicitly committed or rolled back.
Implicit transactions and non-autocommitWhen AUTOCOMMIT=1, MySQL will automatically commit uncommitted transactions after each statement execution.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

  1. mysql> select @@SQL_SELECT_LIMIT;
  2. +----------------------+
  3. | @@SQL_SELECT_LIMIT   |
  4. +----------------------+
  5. | 18446744073709551615 |
  6. +----------------------+
  7. 1 row in set (0.01 sec)
  9. mysql> set autocommit=0;
  10. Query OK, 0 rows affected (0.00 sec)
  12. mysql> set SQL_SELECT_LIMIT = 1;
  13. Query OK, 0 rows affected (0.00 sec)
  15. mysql> select @@SQL_SELECT_LIMIT;
  16. +--------------------+
  17. | @@SQL_SELECT_LIMIT |
  18. +--------------------+
  19. |                  1 |
  20. +--------------------+
  21. 1 row in set (0.01 sec)
  23. mysql> rollback;
  24. Query OK, 0 rows affected (0.00 sec)
  26. mysql> select @@SQL_SELECT_LIMIT;
  27. +--------------------+
  28. | @@SQL_SELECT_LIMIT |
  29. +--------------------+
  30. |                  1 |
  31. +--------------------+
  32. 1 row in set (0.00 sec)
  34. mysql> set autocommit=0;
  35. Query OK, 0 rows affected (0.00 sec)
  37. mysql> set SQL_SELECT_LIMIT = default;
  38. Query OK, 0 rows affected (0.00 sec)
  40. mysql> select @@SQL_SELECT_LIMIT;
  41. +----------------------+
  42. | @@SQL_SELECT_LIMIT   |
  43. +----------------------+
  44. | 18446744073709551615 |
  45. +----------------------+
  46. 1 row in set (0.00 sec)
  48. mysql> rollback;
  49. Query OK, 0 rows affected (0.00 sec)
  51. mysql> select @@SQL_SELECT_LIMIT;
  52. +----------------------+
  53. | @@SQL_SELECT_LIMIT   |
  54. +----------------------+
  55. | 18446744073709551615 |
  56. +----------------------+
  57. 1 row in set (0.00 sec)

MatrixOne Implicit Transaction Behavior Example

  1. mysql> select @@SQL_SELECT_LIMIT;
  2. +----------------------+
  3. | @@SQL_SELECT_LIMIT   |
  4. +----------------------+
  5. | 18446744073709551615 |
  6. +----------------------+
  7. 1 row in set (0.01 sec)
  9. mysql> set autocommit=0;
  10. Query OK, 0 rows affected (0.00 sec)
  12. mysql> set SQL_SELECT_LIMIT = 1;
  13. Query OK, 0 rows affected (0.00 sec)
  15. mysql> select @@SQL_SELECT_LIMIT;
  16. +--------------------+
  17. | @@SQL_SELECT_LIMIT |
  18. +--------------------+
  19. | 1                  |
  20. +--------------------+
  21. 1 row in set (0.00 sec)
  23. mysql> rollback;
  24. Query OK, 0 rows affected (0.00 sec)
  26. mysql> select @@SQL_SELECT_LIMIT;
  27. +--------------------+
  28. | @@SQL_SELECT_LIMIT |
  29. +--------------------+
  30. | 1                  |
  31. +--------------------+
  32. 1 row in set (0.01 sec)
  34. mysql> set autocommit=0;
  35. ERROR 20101 (HY000): internal error: Uncommitted transaction exists. Please commit or rollback first.

Implicit Transactions Example

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

  1. CREATE TABLE t1(a bigint, b varchar(10), c varchar(10));
  2. START TRANSACTION;
  3. INSERT INTO t1 values(1,2,3);
  4. COMMIT;
  6. -- Check the AUTOCOMMIT parameters
  7. mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
  8. +---------------+-------+
  9. | Variable_name | Value |
  10. +---------------+-------+
  11. | autocommit    | 1     |
  12. +---------------+-------+
  13. 1 row in set (0.00 sec)
  14. -- Here an implicit transaction begins, with each DML committed immediately after execution with AUTOCOMMIT=.1
  15. insert into t1 values(4,5,6);
  17. -- Implicit transaction is committed automatically, and the table structure is shown below
  18. mysql> select * from t1;
  19. +------+------+------+
  20. | a    | b    | c    |
  21. +------+------+------+
  22. |    1 | 2    | 3    |
  23. |    4 | 5    | 6    |
  24. +------+------+------+
  25. 2 rows in set (0.00 sec)