On this page

Show all

Help Center/ GaussDB/ MySQL Compatibility(Centralized)/ MySQL Compatibility in M-Compatible Mode/ Transactions

Transactions

Updated on 2024-12-06 GMT+08:00

View PDF

GaussDB is compatible with MySQL transactions, but there are some differences. This section describes transaction-related differences in GaussDB M-compatible databases.

Default Transaction Isolation Levels

The default isolation level of an M-compatible database is READ COMMITTED, and that of MySQL is REPEATABLE-READ.

-- View the current transaction isolation level.
m_db=# SHOW transaction_isolation;

Sub-transactions

In an M-compatible database, SAVEPOINT is used to create a savepoint (sub-transaction) in the current transaction, and ROLLBACK TO SAVEPOINT is used to roll back to a savepoint (sub-transaction). After the sub-transaction is rolled back, the parent transaction can continue to run, the rollback of a sub-transaction does not affect the transaction status of the parent transaction.

No savepoint (sub-transaction) can be created in MySQL.

Nested Transactions

A nested transaction refers to a new transaction started in a transaction block.

In an M-compatible database, if a new transaction is started in a normal transaction block, a warning is displayed indicating that an ongoing transaction exists and the start command is ignored. If a new transaction is started in an abnormal transaction block, an error is reported. The transaction can be executed only after ROLLBACK/COMMIT is executed. If ROLLBACK/COMMIT is executed, the previous statement is rolled back.

In MySQL, if a new transaction is started in a normal transaction block, the previous transaction is committed and then the new transaction is started. If a new transaction is started in an abnormal transaction block, the error is ignored, and the previous error-free statement is committed and the new transaction is started.

-- In an M-compatible database, if a new transaction is started in a normal transaction block, a warning is generated and the transaction is ignored.
m_db=# DROP TABLE IF EXISTS test_t;
m_db=# CREATE TABLE test_t(a int, b int);
m_db=# BEGIN;
m_db=# INSERT INTO test_t values(1, 2);
m_db=# BEGIN; -- The warning "There is already a transaction in progress" is displayed.
m_db=# SELECT * FROM test_t ORDER BY 1;
m_db=# COMMIT; 

-- In an M-compatible database, if a new transaction is started in an abnormal transaction block, an error is reported. The transaction can be executed only after ROLLBACK/COMMIT is executed.
m_db=# BEGIN;
m_db=# ERROR sql; -- Error statement.
m_db=# BEGIN; -- An error is reported.
m_db=# COMMIT; -- It can be executed only after ROLLBACK/COMMIT is executed.

Statements Committed Implicitly

An M-compatible database uses GaussDB for storage and inherits the GaussDB transaction mechanism. If a DDL or DCL statement is executed in a transaction, the transaction is not automatically committed.

In MySQL, if DDL, DCL, management-related, or lock-related statements are executed, the transaction is automatically committed.

-- In M-compatible database, table creation and GUC parameter setting support rollback.
m_db=# DROP TABLE IF EXISTS test_table_rollback;
m_db=# BEGIN;
m_db=# CREATE TABLE test_table_rollback(a int, b int);
m_db=# \d test_table_rollback;
m_db=# ROLLBACK;
The m_db=# \d test_table_rollback; -- This table does not exist.

Differences in SET TRANSACTION

In an M-compatible database, if SET TRANSACTION is used to set the isolation level or transaction access mode for multiple times, only the last one takes effect. Transaction features can be separated by spaces and commas (,).

In MySQL, SET TRANSACTION cannot be used to set the isolation level or transaction access mode for multiple times. Transaction features can only be separated by commas (,).

**Table 1** Differences in SET TRANSACTION
No.	Syntax	Function	Difference
1	SET TRANSACTION	Sets transactions.	In an M-compatible database, SET TRANSACTION takes effect in session-level transactions. In MySQL, SET TRANSACTION takes effect at the next transaction.
2	SET SESSION TRANSACTION	Sets session-level transactions.	-
3	SET GLOBAL TRANSACTION	Sets global session-level transactions. This feature applies to subsequent sessions and has no impact on the current session.	In an M-compatible database, GLOBAL takes effect in transactions at the global session level and applies only to the current database instance. In MySQL, this feature takes effect in all databases.

-- SET TRANSACTION takes effect in session-level transactions.
m_db=# SET TRANSACTION ISOLATION LEVEL READ COMMITTED READ WRITE;
m_db=# SHOW transaction_isolation;
m_db=# SHOW transaction_read_only;
-- In an M-compatible database, if the isolation level or transaction access mode is set for multiple times, only the last one takes effect. 
m_db=# SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED, ISOLATION LEVEL REPEATABLE READ, READ WRITE, READ ONLY;
m_db=# SHOW transaction_isolation; -- repeatable read
m_db=# SHOW transaction_read_only; -- on

Differences in START TRANSACTION

In an M-compatible database, when START TRANSACTION is used to start a transaction, the isolation level can be set. If the isolation level or transaction access mode is set for multiple times, only the last one takes effect. In the current version, consistency snapshot cannot be enabled immediately. Transaction features can be separated by spaces and commas (,).

In MySQL, if START TRANSACTION is used to start a transaction, the isolation level cannot be set and the transaction access mode cannot be set for multiple times. Transaction features can only be separated by commas (,).

-- Start a transaction and set the isolation level.
m_db=# START TRANSACTION ISOLATION LEVEL READ COMMITTED;
m_db=# COMMIT;
-- Set the access mode for multiple times.
m_db=# START TRANSACTION READ ONLY, READ WRITE;
m_db=# COMMIT;

Transaction-related GUC Parameters

**Table 2** Differences in transaction-related GUC parameters
No.	GUC Parameter	Function	Difference
1	autocommit	Sets the automatic transaction commit mode.	-
2	transaction_isolation	Sets the isolation level of the current transaction in GaussDB. Sets the isolation level of a session-level transaction in MySQL.	In GaussDB, you can only change the isolation level of the current transaction by running the SET command. To change the session-level isolation level, use default_transaction_isolation. In MySQL, you can run the SET command to change the isolation level of a session-level transaction. The supported range is different. MySQL supports the following isolation levels, which are case-insensitive but space-sensitive: READ-COMMITTED READ-UNCOMMITTED REPEATABLE-READ SERIALIZABLE GaussDB supports the following isolation levels, which are case-sensitive and space-sensitive: read committed read uncommitted repeatable read serializable default (The level is set to be the same as the default isolation level in the session.) If m_format_dev_version is set to 's2', the isolation levels of MySQL can be set. In GaussDB, the value of transaction_isolation of a new transaction is initialized to the value of default_transaction_isolation. When m_format_dev_version is set to 's2': You can set the features of the next transaction by running set @@transaction_isolation = value; set transaction isolation level value. You can modify the features of a session-level transaction by running set [local\|session\|@@session.] transaction_isolation = value. The features of the next transaction cannot be used within the current transaction. If an error is reported for an implicit transaction, that is, a single SQL statement, the features of the next transaction are retained.
3	tx_isolation	Sets the transaction isolation level. tx_isolation and transaction_isolation are synonyms.	This parameter can be queried but cannot be modified in GaussDB.
4	default_transaction_isolation	Sets the transaction isolation level.	In GaussDB, the SET command is used to change the isolation level a session-level transaction. MySQL does not support this system parameter.
5	transaction_read_only	In GaussDB, this parameter is used to set the access mode of the current transaction. In MySQL, this parameter is used to set the access mode of session-level transactions.	In GaussDB, only the access mode of the current transaction can be changed by using the SET command. If you want to change the access mode of a session-level transaction, you can use default_transaction_read_only. In MySQL, you can run the SET command to change the isolation level of a session-level transaction. In GaussDB, the value of transaction_read_only of a new transaction is initialized to the value of default_transaction_read_only. When m_format_dev_version is set to 's2': You can set the next transaction feature by running set @@transaction_read_only = value; set transaction {read write \| read only}. You can modify the features of a session-level transaction by running set [local\|session\|@@session.] transaction_read_only = value. The features of the next transaction cannot be used within the current transaction. If an error is reported for an implicit transaction, that is, a single SQL statement, the features of the next transaction are retained.
6	tx_read_only	Sets the access mode of a transaction. tx_read_only and transaction_read_only are synonyms.	This parameter can be queried but cannot be modified in GaussDB.
7	default_transaction_read_only	Sets the access mode of a transaction.	In GaussDB, the SET command is used to change the access mode of a session-level transaction. MySQL does not support this system parameter.

Parent topic: MySQL Compatibility in M-Compatible Mode

Previous topic: Collation Rules

Next topic: SQL

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot