This topic describes basic information about transactions and related control statements.
Introduction to transactions
A database transaction consists of a series of operations on the database, which transforms the database from one consistent state to another. Typically, the SQL statements in a transaction include both DML and query statements. If a transaction contains only query statements, it is usually called a read-only transaction.
Before a transaction is committed, you can use the ROLLBACK statement to roll it back.
Transaction control statements
The basic transaction control statements are as follows:
Start a transaction: Explicitly starts a transaction. This statement is optional in the following cases:
If the value of the system variable
autocommitfor a tenant session is0, the automatic commit feature is disabled. In this case, you do not need to explicitly issue theBEGINstatement to combine multiple SQL statements into one transaction.If the value of the system variable
autocommitfor a tenant session is1, the automatic commit feature is enabled. In this case, each SQL statement is an independent transaction. If you want a transaction that contains more than one statement, you can explicitly run theBEGINstatement to start a transaction. In this case, the automatic commit feature is disabled until theCOMMITorROLLBACKstatement is executed.
Mark a savepoint: Marks a savepoint in a transaction, so that you can roll back to it later. Savepoints are optional, and a transaction can have multiple savepoints.
Commit a transaction: Commits and ends the current transaction, permanently applies all changes made in the transaction, clears all savepoints, and releases the locks held by the transaction.
Roll back a transaction: Rolls back all changes made in a transaction or only the changes made after a specific savepoint, clears all savepoints within the rolled-back portion, and releases the locks held by the transaction.
In the obclient command-line interface, you can issue transaction control commands after the SQL prompt, or modify the autocommit variable at the session level to control whether transactions are automatically committed.
If you set the
autocommitvariable by using theSET autocommitstatement, the change takes effect immediately for the current session and becomes invalid after the session is disconnected.If you set the tenant-level variable by using the
SET GLOBAL autocommitstatement, the change takes effect only after the session is disconnected.Note
If the
autocommitvalue of the current session is0and no transaction is explicitly committed, OceanBase AI Database automatically rolls back the last uncommitted transaction if the program terminates abnormally.