Commit a transaction

When you commit a transaction, you persist all changes performed in the transaction, delete the savepoints, and release all transaction locks.

To explicitly commit a transaction, use the COMMIT statement or click the Commit button in a graphical user interface (GUI) client. Note

OceanBase Database issues an implicit COMMIT statement before and after a data definition language (DDL) statement, which also commits a transaction.

MySQL mode

MySQL mode

• 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 modifications are only visible to the current session. The other sessions cannot see them.

  • Your modifications are not permanent, and therefore not final. You can use the ROLLBACK statement to roll back these modifications.

  After you commit a transaction:

  • Your changes are visible to all database sessions.

  • Your modifications are permanent. You cannot use the ROLLBACK statement to roll back these modifications anymore.

• If you do not use BEGIN to start a transaction, an SQL statement is a transaction on its own, and you do not need to commit the transaction. After an SQL statement is executed, the modifications that it makes become permanent. You cannot use the ROLLBACK statement to roll back these modifications.

Example:

obclient> BEGIN;
Query OK, 0 rows affected (0.00 sec)

obclient> INSERT INTO t_insert(id,name) VALUES(4,'JP');
Query OK, 1 row affected (0.01 sec)

obclient> COMMIT;
Query OK, 0 rows affected (0.00 sec)

obclient> ^Bye

[qing.meiq@h07g12088.sqa.eu95 /home/qing.meiq/bmsql]
$obclient -h10.10.10.1 -u******@obbmsql#obdemo -P2883 -p**1*** 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 | 2020-04-02 17:52:31 |
|  2 | US   | 10002 | 2020-04-02 17:52:38 |
|  3 | EN   | 10003 | 2020-04-02 17:52:38 |
|  4 | JP   |  NULL | 2020-04-02 17:53:34 |
+----+------+-------+---------------------+
4 rows in set (0.00 sec)

Oracle mode

Oracle mode

• Before you commit a transaction:

  • Your modifications are only visible to the current session. The other sessions cannot see them.

  • Your modifications are not permanent, and therefore not final. You can use the ROLLBACK statement to roll back these modifications.

• After you commit a transaction:

  • Your changes are visible to all database sessions.

  • Your modifications are permanent. You cannot use the ROLLBACK statement to roll back these modifications anymore.

Example:

obclient> INSERT INTO t_insert(id,name) VALUES(4,'JP');
Query OK, 1 row affected (0.01 sec)

obclient> COMMIT;
Query OK, 0 rows affected (0.00 sec)

obclient> ^Bye

[qing.meiq@h07g12088.sqa.eu95 /home/qing.meiq/bmsql]
$obclient -h10.10.10.1 -u******@obbmsql#obdemo -P2883 -p**1***  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 | 2020-04-02 17:52:31 |
|  2 | US   | 10002 | 2020-04-02 17:52:38 |
|  3 | EN   | 10003 | 2020-04-02 17:52:38 |
|  4 | JP   |  NULL | 2020-04-02 17:53:34 |
+----+------+-------+---------------------+
4 rows in set (0.00 sec)