Transaction processing and control

Transaction processing allows multiple users to work on the database simultaneously and ensures that each user sees a consistent data version and that all changes are applied in the correct order.

A transaction is a sequence of one or more SQL statements that OceanBase Database treats as a unit. Either all statements in the transaction are executed, or none of them are.

OceanBase Database automatically locks data structures, allowing different users to write to the same data structures without affecting each other's data. To maximize data availability, OceanBase Database locks the minimum amount of data for the shortest time.

OceanBase Database supports writing additional code to prevent multiple users from accessing data simultaneously and allows manual override of the default locking mechanism.

The following PL/SQL statements are supported for transaction processing and control:

• COMMIT statement

  Ends the current transaction, making its changes permanent and visible to other users.

• ROLLBACK statement

  Ends the current transaction and undoes any changes made during the transaction. If an error occurs, such as deleting the wrong row from a table, ROLLBACK restores the original data. If a transaction cannot be completed due to a failed SQL statement or an exception raised by PL/SQL, ROLLBACK provides a way to correct the issue and restart.

• SAVEPOINT statement

  Names and marks the current point in a transaction. Using savepoints allows you to roll back part of a transaction rather than the entire transaction. Each session can have an unlimited number of active savepoints. When rolling back to a savepoint, all savepoints marked after that point are deleted. A simple rollback or commit deletes all savepoints.

  Savepoint names are undeclared identifiers. Reusing a savepoint name in a transaction moves the savepoint from its previous location to the current point in the transaction.

• SET TRANSACTION statement

  Begins a read-only or read/write transaction, sets the isolation level, or assigns the current transaction to a specified rollback segment.

  Read-only transactions are useful in scenarios where multiple users update the same table and run multiple queries. During a read-only transaction, all queries reference the same database snapshot, providing a consistent view of multiple tables and queries. Other users can continue to query or update data as usual. A commit or rollback ends the transaction.

  The SET TRANSACTION statement must be the first SQL statement in a read-only transaction and can appear only once. If a transaction is set to READ ONLY, subsequent queries will only see changes committed before the transaction started and will not affect other users or transactions.

  Only the SELECT, OPEN, FETCH, CLOSE, LOCK TABLE, COMMIT, and ROLLBACK statements are allowed in read-only transactions. Queries cannot use FOR UPDATE.

Additionally, before running INSERT, UPDATE, DELETE, or MERGE statements, the database marks an implicit savepoint (which is not available). If the statement fails, the database rolls back to the savepoint. Typically, only the failed SQL statement is rolled back, not the entire transaction. If the statement raises an unhandled exception, the host environment determines what to roll back. The database can also roll back a single SQL statement to break a deadlock. The database sends an error signal to participants of the transaction and rolls back the current statement in the transaction. Before running an SQL statement, the database must parse it, checking and ensuring it follows syntax rules and references valid schema objects. Errors detected while running an SQL statement result in a rollback, but errors detected during parsing do not. If an unhandled exception occurs in a stored subprogram, PL/SQL does not assign values to OUT parameters and does not perform any rollbacks.