Transaction processing and control

The transaction processing feature enables multiple users to concurrently work in a database, and ensures that the database version is the same for all users and that all changes are applied in a correct order.

A transaction is a sequence of one or more SQL statements. OceanBase Database considers a transaction as a unit and executes either all the statements in it or executes none at all.

OceanBase Database automatically locks data structures. Therefore, different users can write data into the same data structures without corrupting the data of each other. To maximize the data availability, OceanBase Database locks the smallest amount of data for the shortest time.

OceanBase Database allows you to write additional code to prevent multiple users from concurrently accessing data, and allows you to manually override the default locking mechanism of OceanBase Database.

PL supports the following transaction processing and control statements:

• COMMIT statement

  You can use this statement to end the current transaction, to make the changes during the transaction permanently valid and visible to other users.

• ROLLBACK statement

  You can use this statement to end the current transaction and revoke all changes made during this transaction. In the case of misoperations, for example, rows are mistakenly dropped from a table, the ROLLBACK statement can restore the original data. If a transaction cannot be completed due to an execution failure of an SQL statement or an exception raised by PL, the ROLLBACK statement provides a correction measure to start over again.

• SAVEPOINT statement

  You can use this statement to name and tag the current point during the processing of a transaction. A savepoint allows you to roll back a part instead of the entirety of a transaction. The number of savepoints in a session is unlimited. After you roll back to a savepoint, all savepoints tagged after it will be dropped. A simple rollback or commit operation will drop all savepoints.

  The name of a savepoint is an undeclared identifier. Reusing a savepoint name in a transaction will move the savepoint from its original position to the current point in the transaction.

• SET TRANSACTION statement

  You can use this statement to start a read-only or read-write transaction, set up an isolation level, assign the current transaction to a specified rollback segment.

  A read-only transaction is useful when multiple users update the same table and run multiple queries. During a read-only transaction, all queries reference the same snapshot of the database, so as to provide a multi-table, multi-query, and read-consistent view. Other users can still query or update data. A commit or rollback operation will end the corresponding transaction.

  The SET TRANSACTION statement must be the first SQL statement in a read-only transaction, and appear only once in the transaction. If you set a transaction to READ ONLY, only the changes committed before the transaction starts are visible to subsequent queries, and other users or transactions are not affected.

  Read-only transactions support only the SELECT, OPEN, FETCH, CLOSE, LOCK TABLE, COMMIT, and ROLLBACK statements. You cannot use the FOR UPDATE statement for queries.

Before the database runs the INSERT, UPDATE, DELETE, or MERGE statement, it will tag an implicit savepoint, which is unavailable. If the execution of the statement fails, the database will roll back to the implicit savepoint. Generally, only the failed SQL statement is rolled back, not the entire transaction. If this statement causes an exception and the exception is not handled, the host environment will determine the content to be rolled back. The database can also roll back a single SQL statement to break the deadlock. The database sends an error signal to the participating transaction and rolls back the current SQL statement in the transaction.

Before an SQL statement is run, it must be parsed by the database. To be specific, the database checks the SQL statement to make sure that it complies with the syntax rules and references valid schema objects. An error detected during the execution of an SQL statement will cause a rollback, but an error detected during the parsing of the statement will not cause a rollback. If you exit a stored subprogram with an unhandled exception, PL will not assign a value to the OUT parameter or perform any rollback.