Reference Manual
   CHAPTER 9. SQL Statements     

COMMIT statement

Function 

To make any changes to the database permanent.

Syntax 

COMMIT [ WORK ]

Permissions 

Must be connected to the database.

Side effects 

Closes all cursors except those opened WITH HOLD.

See also 

ROLLBACK statement

PREPARE TO COMMIT statement

CONNECT statement

SET CONNECTION statement

DISCONNECT statement

Description 

The COMMIT statement ends a logical unit of work (transaction) and makes all changes made during this transaction permanent in the database. A transaction is defined as the database work done between successful COMMIT and ROLLBACK statements on a single database connection.

     The COMMIT statement is also used as the second phase of a two-phase commit operation. For more information, see Typical inconsistencies, and PREPARE TO COMMIT statement.

The changes committed are those made by the data manipulation statements: INSERT, UPDATE, and DELETE, as well as the Interactive SQL load statement INPUT.

Data definition statements all do an automatic commit. They are:

• ALTER

• COMMENT

• CREATE

• DROP

• GRANT

• REVOKE

• SET OPTION

The COMMIT statement fails if the database server detects any invalid foreign keys. This makes it impossible to end a transaction with any invalid foreign keys. Usually, foreign key integrity is checked on each data manipulation operation. However, if either the database option WAIT_FOR_COMMIT is set ON or a particular foreign key was defined with a CHECK ON COMMIT clause, the database server will not check integrity until the COMMIT statement is executed. For a two-phase commit operation, these errors will be reported on the first phase (PREPARE TO COMMIT), not on the second phase (COMMIT).

Standards and compatibility 

• SQL/92     Entry-level feature.

• Sybase     Supported by Adaptive Server Enterprise.

Examples 

• The following statement commits the current transaction:

  COMMIT