|
|
|
Valid in: SQL, ESQL, DBProc
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. For detailed information about rules and database procedures, see the chapter "Understanding Database Procedures, Sessions, and Events."
Note: This statement has additional considerations when used in a distributed environment. For more information, see the Ingres Star User Guide.
|
|
|
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.
|
|
|
In addition to terminating the current transaction, an embedded Commit statement:
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.
|
|
|
All users are permitted to use this statement.
|
|
|
All locks acquired during the transaction are released in the Close statement.
|
|
|
Issuing multiple updates inside a single transaction is generally faster than committing each update individually.
|
|
|
|
|
|
The following embedded example issues two updates, each in its own transaction:
exec sql connect 'personnel';
exec sql update employee
set salary = salary * 1.1
where rating = 'Good';
exec sql commit;
exec sql update employee
set salary = salary * 0.9
where rating = 'Bad';
exec sql disconnect;
/* Implicit commit issued on disconnect */