ALLBASE/SQL Reference Manual (36216-90216)

Chapter 12 545
SQL Statements S - Z
SET TRANSACTION
REFETCH statement prior to updating. REFETCH is not available in ISQL. Therefore, you
should use caution in employing RC and RU in ISQL if you are doing updates.
• Within a transaction, different isolation levels can be set for different DML statements.
For example, a cursor opened following a SET TRANSACTION statement is opened with
the specified isolation level, but any cursor opened prior to this SET TRANSACTION
statement maintains the isolation level with which it was opened.
• As with the SET CONSTRAINTS statement, the SET TRANSACTION statement allows
you to set the UNIQUE, REFERENTIAL or CHECK constraint error checking mode. If
the constraint checking mode is deferred, checking of constraints is deferred until the
end of a transaction or until the constraint mode is set back to immediate. If the
constraint mode is immediate, integrity constraints are checked following processing of
each SQL statement (if statement level atomicity is in effect) or each row (if row level
atomicity is in effect). Refer to the SET DML ATOMICITY statement in this chapter for
further information on statement and row level error checking. The following
paragraph assumes that statement level atomicity is in effect.
When constraint checking is deferred, a COMMIT WORK, or SET CONSTRAINTS
IMMEDIATE statement executes if zero constraint violations exist at that time,
otherwise a constraint error is reported. When constraint checking is immediate (the
default), zero constraint violations must exist when an SQL statement executes,
otherwise a constraint error is reported and the statement is rolled back. The SET
CONSTRAINTS statement in this chapter gives further detail about constraint checking.
• As with the SET DML ATOMICITY statement, the SET TRANSACTION statement allows
you to set the general error checking level in data manipulation statements. General
error checking refers to any errors, for example, arithmetic overflows or constraint
violation errors.
Setting ROW LEVEL atomicity guarantees that internal savepoints are not generated.
For example, if an error occurs on the
n th row of a bulk statement such as LOAD, BULK
INSERT, or Type2 INSERT, the row is not processed, statement execution terminates,
and any previously processed rows are
not rolled back. In contrast, STATEMENT
LEVEL atomicity guarantees that the entire statement is rolled back if it does not
execute without error. STATEMENT LEVEL atomicity is the default. Refer to the SET
DML ATOMICITY statement in this chapter for further information on statement and row
level error checking.
• All transaction attributes are sensitive to savepoints. That is, if you establish a
savepoint, then change the transaction attribute(s) by issuing a SET TRANSACTION
statement, and then roll back to the savepoint, the transaction attribute(s) set after the
savepoint are undone.
• When ON TIMEOUT ROLLBACK or ON DEADLOCK ROLLBACK is set to
TRANSACTION, the whole transaction is aborted as a result of a timeout or deadlock.
• When ON TIMEOUT ROLLBACK or ON DEADLOCK ROLLBACK is set to QUERY,
only the SQL statement which has timed out will be rolled back. This means rolling
back results of statements that modify the database and closing cursor for the
cursor-related statements. (Cursor-related statements change the cursor position, and
are not statements like UPDATE or DELETE WHERE CURRENT.)
• In general, if a transaction with KEEP cursor(s) is committed, the new transaction