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, you can commit transactions 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 take effect only for the current session and not for other database sessions. These changes are not persisted and therefore not the final result. You can use the ROLLBACK statement to roll back the changes.

  • After you commit the transaction, your changes take effect for all database sessions. After your changes are persisted, you cannot roll them back with a ROLLBACK statement.

  Note

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

• If transactions are committed implicitly (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, then use INSERT to insert data into the t_insert table, and finally use COMMIT to commit the transaction. Log out from the database and then log on again. Verify that the data is correctly inserted and successfully 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******  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 | 1970-01-01 17:52:31 |
|  2 | US   | 10002 | 1970-01-01 17:52:38 |
|  3 | EN   | 10003 | 1970-01-01 17:52:38 |
|  4 | JP   |  NULL | 1970-01-01 17:53:34 |
+----+------+-------+---------------------+
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******  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 | 1970-01-01 17:52:31 |
|  2 | US   | 10002 | 1970-01-01 17:52:38 |
|  3 | EN   | 10003 | 1970-01-01 17:52:38 |
|  4 | JP   |  NULL | 1970-01-01 17:53:34 |
+----+------+-------+---------------------+
4 rows in set