Transactions

Default Transaction Isolation Levels

The default isolation level in GaussDB is READ COMMITTED, and that of MySQL is REPEATABLE-READ.

-- View the current transaction isolation level.
m_db=# SHOW transaction_isolation;
 transaction_isolation
-----------------------
 read committed
(1 row)

Sub-transactions

In GaussDB, you can use SAVEPOINT to create a savepoint (sub-transaction) in the current transaction, and ROLLBACK TO SAVEPOINT to roll back to the savepoint. After the rollback, the current transaction can continue to run, whose status will not be affected.

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 GaussDB, if you run a command to start a new transaction in a normal transaction block, a warning is displayed, indicating an ongoing transaction exists and the command will be ignored; while if you do so in an abnormal transaction block, an error is reported and the transaction can only be started after ROLLBACK is executed or COMMIT is used to commit other statements.

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 GaussDB, 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 GaussDB, if a new transaction is started in an abnormal transaction block, an error is reported. The transaction can be executed only after ROLLBACK or 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

Databases under GaussDB use 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 GaussDB, table creation and GUC parameter settings can be rolled back.
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;
m_db=# \d test_table_rollback; -- This table does not exist.
Did not find any relation named "test_table_rollback".

Differences in SET TRANSACTION

In GaussDB, if SET TRANSACTION is used to set the isolation level or transaction access mode for multiple times, only the last setting 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 (,).

-- 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 GaussDB, 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
 transaction_isolation
-----------------------
 repeatable read
(1 row)
m_db=# SHOW transaction_read_only; -- on
 transaction_read_only
-----------------------
 on
(1 row)

Differences in START TRANSACTION

In GaussDB, 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 setting takes effect. In the current version, consistency snapshot cannot be enabled immediately. Transaction features can be separated by spaces or 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 (,).

In MySQL, a transaction at the repeatable read isolation level starts snapshot read only after the first SELECT statement is executed. In GaussDB, once a transaction is started, not only the first SELECT statement performs snapshot read, but also the first executed DDL, DML, or DCL statement creates a consistent read snapshot of the transaction.

-- 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