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.
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 reestablish 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
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