Committing a transaction will persist all changes made in the transaction, delete the savepoints, and release all locks held by the transaction.
Commit a transaction
In OceanBase Database in Oracle mode, 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 the system variable 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
BEGINstatement to start a new transaction, you must use theCOMMITstatement 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
ROLLBACKstatement 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
ROLLBACKstatement.
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, namely, when
autocommitis set to1, 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 aROLLBACKstatement.
Example: Commit a transaction explicitly
Use BEGIN to start a transaction, use INSERT to insert data into the ordr table, and then use COMMIT to commit the transaction.
obclient [SYS]> SELECT * FROM ordr;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10001 | 03-NOV-22 |
| 2 | US | 10002 | 03-NOV-22 |
| 3 | EN | 10003 | 03-NOV-22 |
+----+------+-------+------------+
3 rows in set
obclient [SYS]> BEGIN;
Query OK, 0 rows affected
obclient [SYS]> INSERT INTO ordr(id,name) VALUES(4,'JP');
Query OK, 1 row affected
obclient [SYS]> COMMIT;
Query OK, 0 rows affected
Close and then reconnect the session. You will find that the data is inserted and saved.
obclient [SYS]> exit;
$obclient -h192.168.0.0 -utpcc@obbmsql#obdemo -P2883 -p******
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221487661
Server version: OceanBase 4.0.0.0 (r100000252022102910-df01cef074936b9c9f177697500fad1dc304056f) (Built Oct 29 2022 10:27:50)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [SYS]> SELECT * FROM ordr;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10001 | 03-NOV-22 |
| 2 | US | 10002 | 03-NOV-22 |
| 3 | EN | 10003 | 03-NOV-22 |
| 4 | JP | NULL | 03-NOV-22 |
+----+------+-------+------------+
4 rows in set
Example: Commit a transaction implicitly
Set the autocommit variable to 1 to enable the autocommit mode.
obclient [SYS]> SELECT * FROM ordr;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10001 | 03-NOV-22 |
| 2 | US | 10002 | 03-NOV-22 |
| 3 | EN | 10003 | 03-NOV-22 |
+----+------+-------+------------+
4 rows in set
obclient> SET autocommit=1;
Query OK, 1 row affected
INSERT INTO ordr(id,name) VALUES(4,'JP');
Query OK, 1 row affected
Close and then reconnect the session. You will find that the data is inserted and saved.
obclient [SYS]> exit;
$obclient -h192.168.0.0 -utpcc@obbmsql#obdemo -P2883 -p******
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221487664
Server version: OceanBase 4.0.0.0 (r100000252022102910-df01cef074936b9c9f177697500fad1dc304056f) (Built Oct 29 2022 10:27:50)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [SYS]> SELECT * FROM ordr;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10001 | 03-NOV-22 |
| 2 | US | 10002 | 03-NOV-22 |
| 3 | EN | 10003 | 03-NOV-22 |
| 4 | JP | NULL | 03-NOV-22 |
+----+------+-------+------------+
4 rows in set