COMMIT
Valid in: SQL, ESQL, DBProc, OpenAPI, ODBC, JDBC, .NET
The COMMIT statement commits the current transaction.
The COMMIT statement has the following format:
[EXEC SQL] COMMIT [WORK]
Note: The optional keyword WORK is included for compliance with the ISO and ANSI standards for SQL.
The COMMIT statement terminates the current transaction. Once committed, the transaction cannot be aborted, and all changes it made become visible to all users through any statement that manipulates that data.
Note: If READLOCK=NOLOCK is set, the effect of the transaction is visible before it is committed. This is also true when the transaction isolation level is set to read uncommitted.
The COMMIT statement can be used inside a database procedure if the procedure is executed directly, using the execute procedure statement. However, database procedures that are invoked by a rule cannot issue a COMMIT statement: the commit prematurely terminates the transaction that fired the rule. If a database procedure invoked by a rule issues a COMMIT statement, the DBMS Server returns a runtime error. Similarly a database procedure called from another database procedure must not issue a COMMIT because that leaves the calling procedure outside the scope of a transaction.
Note: This statement has additional considerations when used in a distributed environment. For more information, see the Star User Guide.
Embedded Usage
In addition to terminating the current transaction, an embedded COMMIT statement:
• Closes all open cursors.
• Discards all statements prepared (with the PREPARE statement) during the current transaction.
When a program issues the DISCONNECT statement, an implicit COMMIT is also issued. Any pending updates are submitted. To roll back pending updates before terminating the program, issue a ROLLBACK statement.
Usage in OpenAPI, ODBC, JDBC, .NET
While applications can send a COMMIT query to the DBMS, we recommend that they instead use the interface-specific mechanism for commit in OpenAPI, ODBC, JDBC, and .NET.
Permissions
This statement is available to all users.
Locking
All locks acquired during the transaction are released in the CLOSE statement.
Performance
Issuing multiple updates inside a single transaction is generally faster than committing each update individually.
Related Statements
ROLLBACK
SAVEPOINT
SET
Last modified date: 04/26/2024