Overview

This topic describes basic transaction information and transaction control statements.

About transactions

A database transaction is a series of database operations that are treated as a unit. The database is transformed from one consistent state to another after a transaction is executed. Generally, the SQL statements in a transaction include both DML statements and query statements. A transaction that contains only query statements is generally called a read-only transaction.

You can execute the ROLLBACK statement to roll back a transaction before it is committed.

Transaction control statements

Basic transaction control statements:

• BEGIN: explicitly starts a transaction. It is optional.

  • If the value of the autocommit system variable is 0 for a tenant session, the autocommit mode is disabled. In this case, you do not need to explicitly issue the BEGIN statement to combine multiple SQL statements into one transaction.

  • If the value of the autocommit system variable is 1 for a tenant session, the autocommit mode is enabled. In this case, each SQL statement is an independent transaction. To combine multiple SQL statements into one transaction, you need to explicitly issue the BEGIN statement. In this case, the autocommit mode is disabled and will be resumed after a COMMIT or ROLLBACK statement is executed.

• SAVEPOINT: marks a savepoint in a transaction. After the transaction is executed, you can roll back the transaction to the savepoint. Savepoints are optional. A transaction can have multiple savepoints.

• COMMIT: commits and ends the current transaction, permanently applies all the changes in the transaction, deletes all the savepoints, and releases the locks that the transaction holds.

• ROLLBACK: rolls back all the changes in a transaction or the changes after a specific savepoint, deletes all the savepoints in the statements that are rolled back, and releases the locks that the transaction holds.

In the CLI environment of OceanBase Client (OBClient), you can run a transaction control command after the SQL prompt, or modify the autocommit parameter at the session level to control whether to automatically commit transactions.

• If you execute the SET autocommit statement to set the autocommit parameter, the parameter takes effect immediately for the current session and becomes invalid after the session is closed.

• If you execute the SET GLOBAL autocommit statement to set the autocommit parameter at the tenant level, the parameter takes effect only after you close and reconnect the session.

  Note

  If the autocommit variable of a session is set to 0 and no transaction is to be explicitly committed, when the program terminates abnormally, OceanBase Database automatically rolls back the last uncommitted transaction.

More information

• Start a transaction

• Mark a savepoint

• Commit a transaction

• Roll back a transaction