Committing a transaction persists all changes made in the transaction, clears all savepoints, and releases all locks held by the transaction.
Commit a transaction
In OceanBase AI Database, you can commit a transaction explicitly or implicitly. To explicitly commit a transaction, use the COMMIT statement or click the commit button in a graphical client tool. Implicit commit requires no manual action. When the value of the autocommit variable is 1, OceanBase AI Database automatically commits the transaction that contains each executed statement. In this case, each statement is a transaction.
Note
OceanBase AI Database implicitly issues an COMMIT statement before and after DDL statements, which also commits the transaction.
If you start a transaction with
BEGIN, you need to useCOMMITto commit the transaction after executing DML statements.Before you commit a transaction, your modifications are visible only to the current session and invisible to other database sessions. Your modifications are not persisted and are not the final result. You can roll back these modifications using the
ROLLBACKstatement.After you commit a transaction, your modifications are visible to all database sessions. The modification results are successfully persisted and cannot be rolled back using the
ROLLBACKstatement.
Note
If the transaction isolation level is set to Repeatable Read, a session that has already started a transaction cannot query newly committed data. For more information about transaction isolation levels, see Transaction isolation levels.
If you use transactions implicitly (that is, automatic commit is enabled with
autocommit=1), each SQL statement is a transaction and does not need to be committed explicitly. After the SQL statement is executed, your changes are persisted and cannot be rolled back using theROLLBACKstatement.
Explicitly commit a transaction
Use BEGIN to start a transaction, use INSERT to insert data into the ordr table, and then use COMMIT to explicitly commit the transaction.
obclient [test]> SELECT * FROM ordr;
+----+------+-------+---------------------+
| id | name | value | gmt_create |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2022-10-19 14:51:12 |
| 2 | US | 10002 | 2022-10-19 14:51:12 |
| 3 | EN | 10003 | 2022-10-19 14:51:12 |
+----+------+-------+---------------------+
3 rows in set
obclient [test]> BEGIN;
Query OK, 0 rows affected
obclient [test]> INSERT INTO ordr(id,name) VALUES(4,'JP');
Query OK, 1 row affected
obclient [test]> COMMIT;
Query OK, 0 rows affected
// Close and then reconnect the session. You will find that the data is inserted and saved.
obclient [test]> exit;
$obclient -h192.168.0.0 -ut***@obbmsql#obdemo -P2883 -p****** test
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221709778
Server version: OceanBase 4.6.0.0 AI 4.6.2.0 (r200000092026070710-4a13c0add342eba8c7b887f0fa08a7378c9cc72e) (Built Jul 7 2026 13:38:31)
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 [test]> SELECT * FROM ordr;
+----+------+-------+---------------------+
| id | name | value | gmt_create |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2022-10-19 14:51:12 |
| 2 | US | 10002 | 2022-10-19 14:51:12 |
| 3 | EN | 10003 | 2022-10-19 14:51:12 |
| 4 | JP | NULL | 2022-10-19 14:51:44 |
+----+------+-------+---------------------+
4 rows in set
Implicit transaction commit
Enable automatic commit by setting the autocommit variable to 1.
obclient [test]> SELECT * FROM ordr;
+----+------+-------+---------------------+
| id | name | value | gmt_create |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2022-10-19 14:51:12 |
| 2 | US | 10002 | 2022-10-19 14:51:12 |
| 3 | EN | 10003 | 2022-10-19 14:51:12 |
| 4 | JP | NULL | 2022-10-19 14:51:44 |
+----+------+-------+---------------------+
4 rows in set
obclient [test]> SET autocommit=1;
obclient [test]> INSERT INTO ordr(id,name) VALUES(5,'CN');
Query OK, 1 row affected
Close and then reconnect the session. You will find that the data is inserted and saved.
obclient [test]> exit;
$obclient -h192.168.0.0 -utpcc@obbmsql#obdemo -P2883 -p****** test
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221709778
Server version: OceanBase 4.6.0.0 AI 4.6.2.0 (r200000092026070710-4a13c0add342eba8c7b887f0fa08a7378c9cc72e) (Built Jul 7 2026 13:38:31)
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 [test]> SELECT * FROM ordr;
+----+------+-------+---------------------+
| id | name | value | gmt_create |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2022-10-19 14:51:12 |
| 2 | US | 10002 | 2022-10-19 14:51:12 |
| 3 | EN | 10003 | 2022-10-19 14:51:12 |
| 4 | JP | NULL | 2022-10-19 14:51:44 |
| 5 | CN | NULL | 2022-10-19 14:53:56 |
+----+------+-------+---------------------+
5 rows in set