Show TOC

COMMIT StatementLocate this document in the navigation structure

Makes changes to the database permanent, or terminates a user-defined transaction.

Quick Links:

Go to Examples

Go to Usage

Go to Standards

Go to Permissions

Syntax

Syntax 1 – To end a transaction and makes all changes permanent

COMMIT [ WORK ]

Syntax 2 – To construct nested transactions

COMMIT TRAN[SACTION ] [ <transaction-name> ]
Examples

(back to top)

  • Example 1 commits the current transaction:
    COMMIT
  • Example 2 this Transact-SQL batch reports successive values of @@trancount as 0, 1, 2, 1, 0:
    PRINT @@trancount
BEGIN TRANSACTION
PRINT @@trancount
BEGIN TRANSACTION
PRINT @@trancount
COMMIT TRANSACTION
PRINT @@trancount
COMMIT TRANSACTION
PRINT @@trancount
go
Usage

(back to top)

  • Syntax 1

    Data definition statements carry out commits automatically. For information, see the Side effects listing for each SQL statement.

    COMMIT 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 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 delays integrity checking until the COMMIT statement is executed.
  • Syntax 2 Nested transactions are similar to savepoints. When executed as the outermost of a set of nested transactions, the statement makes changes to the database permanent. When executed inside a transaction, COMMIT TRANSACTION decreases the nesting level of transactions by one. When transactions are nested, only the outermost COMMIT makes the changes to the database permanent.

    The optional parameter <transaction-name> is the name assigned to this transaction. It must be a valid identifier. Use transaction names only on the outermost pair of nested BEGIN/COMMIT or BEGIN/ROLLBACK statements.

    You can use a set of options to control the detailed behavior of the COMMIT statement. See COOPERATIVE_COMMIT_TIMEOUT Option, COOPERATIVE_COMMITS Option, DELAYED_COMMITS Option, and DELAYED_COMMIT_TIMEOUT Option. You can use the Commit connection property to return the number of commits on the current connection.
Side effects:
  • Closes all cursors except those opened WITH HOLD.
  • Deletes all rows of declared temporary tables on this connection, unless they were declared using ON COMMIT PRESERVE ROWS.
Standards

(back to top)

  • SQL—ISO/ANSI SQL compliant.
  • SAP Database products—Supported by SAP ASE. Syntax 2 is a Transact-SQL extension to ISO/ANSI SQL grammar.
Permissions

(back to top)

Must be connected to the database.

Related Information