A transaction is a sequence of one or more SQL statements that OceanBase Database treats as a unit. Either all of the statements are executed, or none of them are.
For example, when a customer places an order for a batch of products, a few rows that record the purchase information and prices are inserted into the order table, and the inventory quantity of these products is updated in the inventory table (the inventory is reduced). To model this process in an application, you must group the INSERT and UPDATE statements into a single transaction.
Generally, the SQL statements in a transaction include both data manipulation language (DML) statements and query statements. A transaction that contains only query statements is generally called a read-only transaction.
The basic transaction control statements include:
BEGIN: explicitly starts a transaction. This command is optional. If the AUTOCOMMIT parameter for a tenant session is set to Off (disables autocommit), you do not need to explicitly issue this command. If the AUTOCOMMIT parameter is set to On (enables autocommit), each individual SQL statement is treated as a transaction. To group multiple SQL statements into a transaction, you need to explicitly issue a BEGIN command.
SAVEPOINT: marks a savepoint during a transaction, to which you can later roll back. Savepoints are optional. A transaction can have multiple savepoints.
COMMIT: ends the current transaction, makes its changes permanent, erases its savepoints, and releases its locks.
ROLLBACK: rolls back either the entire transaction or only the changes made after the specified savepoint, erases all savepoints included in the section that is rolled back, and then releases the locks held by the transaction.
In the command-line (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. If you modify the autocommit parameter at the tenant level, you must disconnect and reconnect the session for the modification to take effect.
In a graphic user interface (GUI) tool such as OceanBase Developer Center (ODC), you can run a command in the SQL Editor window or click the Commit and Rollback buttons on the toolbar. Note
If the autocommit parameter of a session is set to off and no transaction is to be explicitly committed, when the program terminates abnormally, OceanBase automatically rolls back the last uncommitted transaction. We recommend that you explicitly commit or roll back transactions.