This topic introduces the definition of transactions and basic transaction control statements.
A transaction is a series of SQL statements that OceanBase Database processes as a whole. This means that the execution of these statements is either all successful or all failed with no in-betweens.
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 session-level
autocommitvariable is set to off, transactions are manually committed, and you do not need to explicitly run this command. If session-levelautocommitis set to on, transactions are automatically committed, and each SQL statement is considered a transaction on its own. If you want a transaction that includes more than one statement, you must explicitly run the BEGIN command.SAVEPOINT: marks a point in the transaction, so that after the transaction is executed, you can roll back the statements after the savepoint. Savepoints are optional. A transaction can have multiple savepoints.
COMMIT: ends the current transaction, permanently applies all the modifications in the transaction, deletes all the savepoints, and releases the locks that the transaction holds.
ROLLBACK: rolls back all the modifications in a transaction or the modifications after a specific savepoint. Deletes all the savepoints in the statements that are rolled back and releases the locks that the transaction holds.
If you use OBClient, you can run a transaction control command after an SQL prompt. You can also control whether transactions are manually or automatically committed by setting the session-level autocommit variable. The modification of the session-level autocommit variable takes effect after you disconnect from a session and then reconnects.
If you use a graphical user interface GUI tool, such as DBeaver, you can run transaction control commands in its SQL editing window, or click the Commit or Rollback icon in the toolbar. Note
If the session-level autocommit variable is set to off and a transaction is not explicitly committed, when the program exits abnormally, OceanBase Database automatically rolls back the last committed transaction. We recommend that you explicitly commit and roll back transactions.