This chapter discusses the following SQL features:
|
|
|
A transaction is one or more SQL statements processed as a single, indivisible database action.
If the transaction contains multiple statements, it is often called a multi-statement transaction (MST). By default, all transactions are multi-statement transactions.
|
|
|
A transaction can be performed by the SQL user, the program, or in some instances, by the DBMS Server itself.
The transaction performs the following actions:
|
|
|
None of the database changes made by a transaction are visible to other users until the transaction is committed. In a multi-user environment, where more than one transaction is open concurrently, this behavior maintains database consistency. The DBMS lock manager ensures that two transactions cannot interfere with one another if both are writing to the same database tables by having one wait until the other is finished. That is, a transaction that writes to the database locks pages in the tables that are affected, thus enforcing database consistency.
|
|
|
A transaction begins with the first statement after connection to the database or the first statement following a commit or rollback (including rollbacks performed by the DBMS). Subsequent statements are part of the transaction until a commit or rollback is executed. By default, an explicit commit or rollback must be issued to close a transaction.
|
|
|
To direct the DBMS to commit each database statement individually, use the set autocommit on statement (this statement cannot be issued in an open transaction). When autocommit is set on, a commit occurs automatically after every statement, except prepare and describe. If autocommit is on and a cursor is opened, the DBMS does not issue a commit until the close cursor statement is executed, because cursors are logically a single statement. A rollback statement can be issued when a cursor is open. To restore the default behavior (and enable multi-statement transactions), issue the set autocommit off statement.
|
|
|
To determine whether you are in a transaction, use the inquire_sql statement.
For information about inquire_sql, see Inquire_sql in the chapter "Using SQL Statements." To find out if autocommit is on or off, use dbmsinfo.
|
|
|
Three primary statements are used to control transactions, they are:
In some circumstances, the DBMS terminates a transaction with a rollback. For details, see Aborting Statements and Transactions.
|
|
|
The commit, rollback, and savepoint statements allow control of the effects of a transaction on the database as follows:
When a commit statement is issued:
|
|
|
In a multi-statement transaction, use rollback together with the savepoint statement to perform a partial transaction rollback. The savepoint statement establishes a marker in the transaction. If a rollback is subsequently issued, specify that the rollback only go back to the savepoint. All changes made prior to the savepoint are left in place; those made after the savepoint are undone. Savepoint does not commit changes or release any locks, it simply establishes stopping points for use in partial rollbacks. For example:
...
insert into emp_table values (ename, edept);
update.....
savepoint first;
insert.....
delete.....
if error on delete
rollback to first;
else if other errors
rollback;
...
commit;
If an error occurs on the delete statement, the rollback to first statement directs the DBMS to back out all changes made after the savepoint was first created, in this case, only the changes made by the second insert statement. Processing resumes with the first statement that follows the rollback to first statement; the transaction is not terminated.
If an error occurs that makes it necessary to abort the entire transaction, the rollback statement that does not specify a savepoint causes the DBMS to back out the entire transaction. Depending on the design of the application, the program can either restart the transaction or continue with the next transaction.
An unlimited number of savepoints can be placed in a transaction. Roll back to the same savepoint is allowed any number of times within a transaction.
For a complete description of these statements, see the chapter "Using SQL Statements."
|
|
|
When an operator interrupt occurs on the currently executing transaction, the Ingres transaction processing system response depends on the operating system used:
Windows: The Ingres transaction processing system recognizes the interrupt signal, Ctrl+C. When the user enters a Ctrl+C through a terminal monitor during transaction processing, the DBMS interrupts the current statement and rolls back any partial results of that statement. Additional use of Ctrl+C is ignored (unless an additional statement is added to the transaction). The transaction remains open until terminated by a commit or rollback statement.
UNIX: The Ingres transaction processing system recognizes the interrupt signal Ctrl+C. When the user enters a Ctrl+C through a terminal monitor during transaction processing, the DBMS interrupts the current statement and rolls back any partial results of that statement. If there is no statement currently executing, Ctrl+C has no effect. Ctrl+C has no effect on the state of the transaction and does not cause any locks to be released.
VMS: The Ingres transaction processing system recognizes two interrupt signals, Ctrl+C and Ctrl+Y, when they are entered through a terminal monitor. When the user enters a Ctrl+C through a terminal monitor during transaction processing, the DBMS interrupts the current statement and rolls back any partial results of that statement. If there is no statement currently executing, Ctrl+C has no effect. Ctrl+C has no effect on the state of the transaction and does not cause any locks to be released.A Ctrl+Y character causes the DBMS to roll back a transaction in progress. The use of Ctrl+Y is strongly discouraged, as is the use of the VMS STOP command.
|
|
|
Transactions and statements can be aborted by any of the following entities:
Applications can abort transactions or statements as a result of the following conditions:
The DBMS aborts statements and transactions as a result of the following conditions:
|
|
|
To direct the DBMS to rollback an entire transaction (or a single statement), use the set session with on error = rollback statement | transaction statement.
For more information, see Session with On_error in the chapter "Using SQL Statements."
Note: Deadlock, Transaction log full, and Lock quota exceeded always rollback the entire transaction regardless of the current on_error setting.
|
|
|
When a statement or transaction is aborted (due to an application or the DBMS itself), the following occurs:
When writing embedded SQL applications, your application must include logic for handling operator interrupts. By default, if the application is aborted during a transaction, the transaction is rolled back. This also applies to Ingres tools. For example, if you abort Query-By-Forms (QBF) while it is performing an update, the update is rolled back.