All Products
Search
Document Center

TRANSACTION

Last Updated: Jun 18, 2021

Description

The TRANSACTION statement starts a transaction.

A database transaction refers to a series of operations that are executed as a single logical unit of work. Transaction processing can be used to maintain the integrity of databases because it ensures that SQL operations in a batch are all executed or are not executed at all.

Explicit transactions are user-defined or user-specified transactions. The transactions explicitly start with a BEGIN TRANSACTION statement or BEGIN and BEGIN WORK statements (supported as the aliases of START TRANSACTION), and explicitly end with a COMMIT or ROLLBACK statement.

Syntax

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

Parameter description

Parameter

Description

START TRANSACTION [READ ONLY | READ WRITE]

The statement that is used to start a transaction. After the transaction is started, the subsequent SQL data manipulation language (DML) statements, such as INSERT, UPDATE, and DELETE, take effect only when the transaction is explicitly committed.

The READ ONLY clause indicates that the transaction is started in READ ONLY mode. You cannot perform modifications within a transaction.

The READ WRITE clause indicates that the transaction is started in READ WRITE mode. It is the default mode.

BEGIN

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

COMMIT

Commits the current transaction.

ROLLBACK

Rolls back the current transaction.

SET TRANSACTION {READ ONLY | READ WRITE}

Sets the mode of the current transaction to READ ONLY or READ WRITE.

Examples

Assume that the following table a is available.

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 to execute the transaction, change the name of the row whose id is 3 to c, and insert a row of sales records of 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 query information about table a:

SELECT * FROM a;

The following result is returned:

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 the transaction is committed, you can check whether the operations in the current transaction have taken effect. For example, you can insert SELECT * FROM a; before the COMMIT keyword. The latest result can be retrieved for the access from the session of the current transaction. For the access from outside the session of the current transaction, the result does not take effect. Before the transaction is committed, your previous operations are invisible except the connection to the current transaction. To roll back the transaction, replace COMMIT with ROLLBACK.