COMMIT

Purpose

This statement ends the current transaction and makes all changes made 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 query the modified tables to view any changes made in the transaction. However, other users cannot see these changes. After you commit a transaction, the changes will be visible to other users who execute 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 situations:

• Before any syntactically correct Data Definition Language (DDL) statement, even if the statement causes an error;
• After any correctly 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 that was started with 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 OceanBase Database. If you do not explicitly commit a transaction and the program terminates unexpectedly, 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 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