MySQL transaction control

Start a transaction

Transaction control statements in OceanBase Database are compatible with those in MySQL. You can use one of the following methods to start a transaction:

• Execute a START TRANSACTION statement.

• Execute a BEGIN statement.

• Execute SET autocommit = 0 and then the first statement.

The syntax is as follows:

START TRANSACTION
    [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic: {
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY
}

BEGIN [WORK]

SET autocommit = {0 | 1}

Commit a transaction

You can use a COMMIT statement to commit a transaction. The syntax is as follows:

COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

With autocommit = 0, you can execute a statement that starts a transaction to implicitly commit the transaction.

Roll back a transaction

You can use a ROLLBACK statement to roll back a transaction.

The syntax is as follows:

ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

Automatically commit a transaction

If you set the autocommit variable to 1, OceanBase Database automatically commits the corresponding transaction after each statement is executed. In this case, each statement is a transaction.

Implicitly commit a transaction

In an implicit commit process, OceanBase Database automatically commits an active transaction without receiving a transaction ending statement such as a COMMIT or ROLLBACK statement.

A transaction may be implicitly committed in the following scenarios:

• A statement that is used to start a transaction is executed.

• A DDL statement is executed.

Automatically roll back a transaction

In an automatic rollback process, OceanBase Database rolls back a transaction without receiving a ROLLBACK instruction from users. A transaction may be automatically rolled back in the following scenarios:

• A session is disconnected.

• The transaction execution times out.

• No statement is executed for a session in an active transaction within the idle timeout period.

  Note

  • ob_trx_timeout specifies the transaction timeout duration in microseconds.

  • ob_trx_idle_timeout specifies the idle timeout duration in microseconds in which no transactions are executed. A timeout occurs when the execution interval between two statements exceeds the value of this parameter.

For more information about variables, see Overview of parameters and system variables.

In the preceding scenarios, OceanBase Database automatically rolls back the transaction. If you execute an SQL statement in the current session that is still connected, the system prompts that the transaction is interrupted and needs to be rolled back. Then, you must execute a ROLLBACK statement to end the current transaction.

Roll back an interrupted transaction

If an internal error occurs during the execution of a transaction due to a node failure or other reasons, the transaction is interrupted. In this case, the transaction cannot execute a statement and must be rolled back.

If you execute an SQL statement in this case, the message “transaction need rollback” appears. You must execute a ROLLBACK statement to end the transaction.