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 query the modified tables to view any changes made in the transaction, but other users cannot see these changes. After you commit a 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 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 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 when it exits normally. 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-compatible mode.

Syntax

COMMIT [ WORK ] [ COMMENT 'string' ];

Parameters

Examples

• First, create the tbl1 table:

  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 the comment with it:

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