Commit a transaction

Committing a transaction will persist all changes made in the transaction, delete the savepoints, and release all locks held by the transaction.

Syntax

In OceanBase Database, transactions can be committed explicitly or implicitly. To explicitly commit a transaction, use the COMMIT statement or the commit button on a GUI-based client. To implicitly commit a transaction, you do not need to proactively commit it. When autocommit is set to 1, after each statement is executed, OceanBase Database will automatically commit the transaction where this statement is executed. A statement is a transaction.

Note

OceanBase Database issues an implicit COMMIT statement before and after a DDL statement, which also commits a transaction.

• If you use the BEGIN statement to start a new transaction, you must use the COMMIT statement to commit the transaction after you execute a DML statement.

  • Before you commit a transaction:

    • Your changes are visible only to the current session.

    • Your changes are not final and you can roll them back with a ROLLBACK statement.

  • After you commit a transaction:

    • Your changes are visible to all database sessions.

      Note

      If the repeatable read isolation level is set for the transaction, sessions where the transaction is started cannot query newly committed data.

    • Your changes are persisted and you cannot roll them back with a ROLLBACK statement.

• If transactions are committed implicitly (with autocommit set to 1), each SQL statement is a transaction. In this case, you do not need to proactively commit the entire transaction. After the SQL statement is executed, your changes are persisted, and you cannot roll them back with a ROLLBACK statement.

Examples

Commit a transaction explicitly

Use BEGIN to start a transaction, use INSERT to insert data into the t_insert table, and then use COMMIT to commit the transaction. Log off from the database and then log on again. Verify that the data is correctly inserted and saved.

obclient> BEGIN;
Query OK, 0 rows affected 

obclient> INSERT INTO t_insert(id,name) VALUES(4,'JP');
Query OK, 1 row affected 

obclient> COMMIT;
Query OK, 0 rows affected 

obclient> exit;

$obclient -h192.168.0.0 -utpcc@obbmsql#obdemo -P2883 -p**1***  TPCC
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
|  1 | CN   | 10001 | 01-JAN-70  |
|  2 | US   | 10002 | 01-JAN-70  |
|  3 | EN   | 10003 | 01-JAN-70  |
|  4 | JP   |  NULL | 01-JAN-70  |
+----+------+-------+------------+
4 rows in set

Commit a transaction implicitly

Set autocommit to 1 to enable the autocommit mode.

obclient> SET autocommit=1;

obclient> INSERT INTO t_insert(id,name) VALUES(4,'JP');
Query OK, 1 row affected 

obclient> exit;

$obclient -h192.168.0.0 -utpcc@obbmsql#obdemo -P2883 -p**1***  TPCC
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
|  1 | CN   | 10001 | 01-JAN-70  |
|  2 | US   | 10002 | 01-JAN-70  |
|  3 | EN   | 10003 | 01-JAN-70  |
|  4 | JP   |  NULL | 01-JAN-70  |
+----+------+-------+------------+
4 rows in set