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, 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
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 are visible only to 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 are visible to 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. For more information about transaction isolation levels, see Transaction isolation levels.
If transactions are started implicitly, namely,
autocommitis set to1to enable the autocommit mode, each SQL statement is a transaction. In this case, you do not need to execute theCOMMITstatement to commit the SQL statements as one transaction. After the SQL statements are executed, your changes are persisted, and you cannot roll them back with aROLLBACKstatement.
Example of explicit transaction commit
The following example starts a transaction with BEGIN, inserts data into the ordr table using INSERT, and then commits with COMMIT.
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
After the command succeeds, exit the session and reconnect. You will find that the table data has been correctly 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 of implicit transaction commit
The following example enables the autocommit mode by setting autocommit=1.
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
After the command succeeds, exit the session and reconnect. You will find that the table data has been correctly 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