Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements

COMMIT statement

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

Syntax
  • Committing work
    COMMIT [ WORK ]
  • Committing at a transaction level
    COMMIT TRAN[SACTION] [ transaction-name ]
Parameters
  • transaction-name

    An optional 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.

    The following options control the behavior of the COMMIT statement.

    • cooperative_commit_timeout option
    • cooperative_commits option
    • delayed_commits option
    • delayed_commit_timeout option

    You can use the Commit connection property to return the number of commits on the current connection.

Remarks
  • Committing work

    The COMMIT statement ends a transaction and makes all changes made during this transaction permanent in the database.

    All data definition statements automatically perform a commit. For information, see the Side effects listing for each SQL statement.

    The COMMIT statement fails if the database server detects any invalid foreign keys. This behavior 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.

    The use of COMMIT alone is equivalent to COMMIT WORK.

  • Committing at a transaction level

    You can use BEGIN TRANSACTION and COMMIT TRANSACTION statements in pairs to construct nested transactions. 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, the COMMIT TRANSACTION statement decreases the nesting level of transactions by one. When transactions are nested, only the outermost COMMIT makes the changes to the database permanent.

    Committing at a transaction level is a Transact-SQL extension.

Privileges

None.

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.

If the database is not using a transaction log, each COMMIT operation causes an implicit checkpoint.

Standards
  • ANSI/ISO SQL Standard

    Committing work is a Core Feature. Committing at a transaction level is a Transact-SQL extension.

Example

The following statement commits the current transaction:

COMMIT;

The following 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