Makes changes to the database permanent, or terminates a user-defined transaction.
Syntax 1COMMIT [ WORK ]
Syntax 2COMMIT TRAN[SACTION] [ transaction-name ]
Parameterstransaction-name An optional name assigned to this transaction. It must be a valid identifier. You should use transaction names only on the outermost pair of nested BEGIN/COMMIT or BEGIN/ROLLBACK statements.
For more information about transaction nesting in Adaptive Server Enterprise and SQL Anywhere, see BEGIN TRANSACTION statement [T-SQL]. For more information about savepoints, see SAVEPOINT statement.
You can use a set of options to control the detailed behavior of the COMMIT statement. See:
You can use the Commit connection property to return the number of commits on the current connection. See Connection properties.
RemarksSyntax 1 The COMMIT statement ends a transaction and makes all changes made during this transaction permanent in the database.
All data definition statements automatically carry out 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 option, the database server delays integrity checking until the COMMIT statement is executed.
Syntax 2 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.
Syntax 2 is a Transact-SQL extension.
In Interactive SQL, you can also execute a COMMIT by:
Pressing CTRL+SHIFT+C.
Choosing SQL » Commit.
In Interactive SQL, executing a COMMIT from the SQL menu or the keyboard shortcut does not modify the contents of the SQL Statements pane; however, the Results tab in the Results pane is cleared.
PermissionsNone.
Side effectsCloses 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.
See also Standards and compatibilitySQL/2008 Syntax 1 is a core feature. Syntax 2 is a Transact-SQL extension.
ExampleThe 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 |
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0 |