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