All Products
Search
Document Center

TRANSACTION

Last Updated: Jun 18, 2021

Description

You can execute the TRANSACTION statement to start a transaction.

A database transaction is a single logical unit of work that consists of a collection of operations. Transaction processing ensures that SQL operations in a batch are all executed or are not executed at all. You can use transactions to maintain the data integrity of databases.

An explicit transaction is a user-defined or user-specified transaction. An explicit transaction is a transaction that starts with the BEGIN TRANSACTION, BEGIN, or BEGIN WORK statement and ends with the COMMIT or ROLLBACK statement. BEGIN and BEGIN WORK are supported as aliases of the START TRANSACTION statement.

Syntax

transaction_stmt:
      START TRANSACTION [READ ONLY | READ WRITE];
    | BEGIN [WORK];
    | COMMIT [WORK];
    | ROLLBACK [WORK];
    | SET TRANSACTION {READ ONLY | READ WRITE};

Parameters

Parameter

Description

START TRANSACTION [READ ONLY | READ WRITE]

Start a transaction. After a transaction is started, the SQL statements that follow the START TRANSACTION statement, such as INSERT, UPDATE, and DELETE, take effect only when the transaction is explicitly committed.

The READ ONLY clause specifies that the transaction is started in read-only mode. This prevents you from modifying the transaction after the transaction is started.

The READ WRITE clause specifies that the transaction is started in read/write mode. By default, the read/write mode is used.

BEGIN

BEGIN and BEGIN WORK are supported as aliases of the START TRANSACTION statement.

COMMIT

Commit the current transaction.

ROLLBACK

Roll back the current transaction.

SET TRANSACTION {READ ONLY | READ WRITE}

Set the current transaction mode. Valid values: READ ONLY and READ WRITE.

Examples

Assume that table a is created, as shown in the following table.

id

name

num

sell_date

1

a

100

2013-06-21 10:06:43

2

b

200

2013-06-21 13:07:21

3

a

50

2013-06-21 13:08:15

  1. Run the following commands in sequence to start a transaction. The transaction completes the following operations: Find the row where the value for the id column is 3, change the value for the name column to c, and then insert a row that contains a sale record of product a.
OceanBase(admin@test)> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

OceanBase(admin@test)> UPDATE a SET name = 'c' WHERE id = 3;
Query OK, 1 rows affected (0.00 sec)

OceanBase(admin@test)> INSERT INTO a VALUES (4, 'a', 30, '2013-06-21 16:09:13');
Query OK, 1 rows affected (0.00 sec)

OceanBase(admin@test)> COMMIT;
Query OK, 0 rows affected (0.00 sec)
  1. After you commit the transaction, run the following command to view table a:
SELECT * FROM a;

Return result:

id

name

num

sell_date

1

a

100

2013-06-21 10:06:43

2

b

200

2013-06-21 13:07:21

3

c

50

2013-06-21 13:08:15

4

a

30

2013-06-21 16:09:13

Notice

Before you commit a transaction, you can check whether the operations in the transaction have taken effect. For example, you can insert SELECT * FROM a; before you execute the COMMIT statement. The session within which this transaction is executed can read the updated result. A session outside this transaction cannot read the updated result. Before the transaction is committed, your previous operations are invisible outside the transaction session. To roll back a transaction, replace COMMIT with ROLLBACK.