Oracle transaction control

Start a transaction

Method

The Oracle mode of OceanBase Database provides the following methods to start a transaction:

• Start a transaction automatically

  If you execute a data manipulation language (DML) statement (excluding SELECT), data control language (DCL) statement, or data definition language (DDL) statement in a session that has no active transactions, a transaction is automatically started by default.

• Start a transaction manually

  In addition to the methods compatible with Oracle, OceanBase Database allows you to start a transaction manually. The syntax is as follows:

  START TRANSACTION
      [READ ONLY | READ WRITE];
  BEGIN [WORK] ;
  

Execution effect

After a transaction is started, the system initializes the transaction information as specified by the user or based on the default setting.

1. Set user information.

2. Set the isolation level.

   The default isolation level of a transaction is READ COMMITTED. You can execute the SET TRANSACTION statement to specify the isolation level.

3. Set the transaction-level snapshot version for a transaction at the SERIALIZABLE isolation level.

Commit a transaction

The syntax to commit a transaction is as follows:

obclient> COMMIT [WORK];

After you enter the COMMIT statement, changes made by the transaction are committed and the transaction ends. In OceanBase Database, a transaction is typically committed by using the two-phase commit protocol. For more information about two-phase commit, see Two-phase commit.

Roll back a transaction

The syntax to roll back a transaction is as follows:

ROLLBACK [WORK];

After you enter the ROLLBACK statement, changes made by the transaction are cleared and the transaction ends.

The rollback process is described as follows:

1. Each participant clears its own changes in the MemTable, releases row locks, and then clears the corresponding participant context.

2. After the context of a participant is cleared, a message is sent to the coordinator. After the coordinator receives messages of all participants, it clears the coordinator context.

Read-only transactions

Optimized read-only transactions

To optimize read-only statements, you are allowed to enter the path for read-only transaction optimizations after executing a SELECT statement when no active transactions exist in the current session. After optimizations, procedures such as creating the transaction context are skipped, and the transaction reads only required information and returns the information to users.

The method to obtain the snapshot time of an optimized read-only transaction is determined by the isolation level of the current session.

Here is an example of an optimized read-only transaction:

SELECT * FROM t1;// Optimize the read-only transaction.
SELECT * FROM t2;// Optimize the read-only transaction.
INSERT INTO t1 VALUES(1);// Start the transaction.

Read-only transactions

The syntax to start a read-only transaction is as follows:

obclient> START TRANSACTION READ ONLY;

A read-only transaction cannot change data and is easier to optimize compared with a read/write transaction. A read-only transaction does not create the transaction context and requires no clearance when rolled back.