This topic describes the TCL statements supported by PolarDB-X and the usage of these statements.

A database transaction is a single logical unit of work that consists of a collection of operations. You can perform transactions to maintain the data integrity of databases. This ensures that SQL operations in a batch are all executed or are not executed.

By default, PolarDB-X runs in autocommit mode. If the autocommit mode is enabled, each SQL statement forms a single transaction. The distributed transaction feature is disabled for DML statements that are executed across shards. The feature is enabled only for DML statements that update global secondary indexes, broadcast tables, and shard keys. This ensures high performance. You can manually start an interactive transaction that consists of multiple statements by executing the SET AUTOCOMMIT=0 statement or specifying the transaction name.

An explicit transaction is a user-defined or user-specified transaction. An explicit transaction is a transaction that starts with the START TRANSACTION or BEGIN statement and ends with the COMMIT or ROLLBACK statement. BEGIN can be used as an alias of the START TRANSACTION statement.

Syntax

START TRANSACTION
  [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic: {
    WITH CONSISTENT SNAPSHOT
   ISOLATION LEVEL {REPEATABLE READ  READ COMMITTED}
   READ WRITE
   READ ONLY
}

BEGIN
COMMIT
ROLLBACK
SET autocommit = {0  1}

The following table describes the parameters in the preceding statement.

Parameter Description
START TRANSACTION [READ ONLY READ WRITE] Starts a new transaction. After the transaction is started, the DML statements such as INSERT, UPDATE, and DELETE take effect until the transaction is committed.
  • The READ ONLY clause specifies that the transaction is started in read-only mode. This ensures that the transaction is not modified after the transaction is started.
  • The READ WRITE clause specifies that the transaction is started in read/write mode. By default, the read/write mode is used.
START TRANSACTION WITH CONSISTENT SNAPSHOT Starts a new transaction. If no isolation level is specified, set the transaction isolation level to REPEATABLE READ.
START TRANSACTION ISOLATION LEVEL {REPEATABLE READ READ COMMITTED} Starts a new transaction and specifies the isolation level for the transaction.
BEGIN BEGIN can be used as an alias of the START TRANSACTION statement.
COMMIT Commits the current transaction.
ROLLBACK Rolls back the current transaction.
SET autocommit = {0 1} Specifies whether to enable the autocommit mode for the current session.