COMMIT

Purpose

This statement ends the current transaction and makes all changes executed in the transaction permanent. This statement also clears all savepoints in the transaction and releases transaction locks.

Limitations and considerations

Before you commit a transaction, you can view any changes made in the transaction by querying the modified tables, but other users cannot see these changes. After you commit the transaction, the changes will be visible to other users executing statements after the commit.

You can use the ROLLBACK statement to roll back (undo) any changes made in the transaction.

OceanBase Database automatically performs an implicit commit in the following cases:

• Before any syntactically correct Data Definition Language (DDL) statement, even if the statement causes an error;
• After any successfully completed Data Definition Language (DDL) statement.

You can also use this statement:

• To manually commit a distributed transaction that is in an indeterminate state;
• To terminate a read-only transaction started by the SET TRANSACTION statement.

OceanBase Database recommends that you explicitly use the COMMIT or ROLLBACK statement to end each transaction, including the last one, before disconnecting from the OceanBase Database. If you do not explicitly commit a transaction and the program terminates abnormally, the uncommitted transaction will be automatically rolled back.

Most OceanBase Database tools and utilities automatically commit the current transaction when they exit normally. However, the OceanBase Database precompiler does not commit the transaction upon normal exit; instead, it relies on the OceanBase Database to roll back the current transaction.

Privilege requirements

You do not need any privileges to execute the COMMIT statement. For more information about privileges in OceanBase Database, see Privilege classification in Oracle mode.

Syntax

COMMIT [ WORK ] [ COMMENT 'string' ];

Parameters

Examples

• First, create a table named tbl1:

  obclient> CREATE TABLE tbl1 (
      id INT PRIMARY KEY,
      name VARCHAR(50),
      value INT
  );
  Query OK, 0 rows affected
  

• Insert a row of data into the tbl1 table and commit the change:

  obclient> INSERT INTO tbl1 VALUES (1, 'a', 10);
  Query OK, 1 row affected
  obclient> COMMIT;
  Query OK, 0 rows affected
  

• Commit the current transaction and associate a comment with it:

  obclient> COMMIT COMMENT 'Transaction code 001 in question, Please contact Zhang XX';
  Query OK, 0 rows affected