This topic describes basic information about transactions and related control statements.
About transactions
A database transaction consists of a series of operations on a database, which transforms the database from one consistent state to another. Typically, SQL statements in a transaction include DML statements as well as query statements. If a transaction only contains query statements, it is usually called a read-only transaction.
Before a transaction is committed, you can roll back it by using the ROLLBACK command. For more information about transactions, see Overview of transactions.
Transaction control statements
The basic transaction control statements are as follows:
BEGIN: explicitly starts a transaction. It is optional.If the value of the system variable
autocommitis0for a tenant session, the autocommit mode is disabled. In this case, you do not need to explicitly issue theBEGINcommand to combine multiple SQL statements into one transaction.If the value of the system variable
autocommitis1for a tenant session, the autocommit mode is enabled. In this case, each SQL statement is an independent transaction. If you want a transaction that includes more than one statement, you can explicitly run theBEGINcommand to start a transaction. In this case, the autocommit mode is disabled until the execution of theCOMMITorROLLBACKstatement.
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.
On the command-line interface (CLI) of OceanBase Client (OBClient), you can run a transaction control command after the SQL prompt, or modify the autocommit variable at the session level to control whether to automatically commit transactions.
If you run
SET autocommitto set theautocommitvariable, the variable takes effect immediately for the current session and becomes invalid after the session is closed.If you run
SET GLOBAL autocommitto set theautocommitvariable at the tenant level, the variable takes effect only after the session is closed and reconnected.Note
If the
autocommitvariable of a session is set to0and no transaction is explicitly committed, when the program terminates abnormally, OceanBase Database automatically rolls back the last uncommitted transaction.