|
|
The SET SESSION statement sets one or more transaction attributes for
the duration of a session to be applied to the next and subsequent
transactions. These attributes include: isolation level, priority,
user label, constraint checking mode, DML atomicity level, timeout rollback,
user timeout, termination level, and fill options.
ISQL or Application Programs
SET SESSION {ISOLATION LEVEL {RR
CS
RC
RU
REPEATABLE READ
SERIALIZABLE
CURSOR STABILITY
READ COMMITTED
READ UNCOMMITTED
:HostVariable1 }
PRIORITY {Priority
:HostVariable2}
LABEL {'LabelString'
:HostVariable3}
ConstraintType [,...] CONSTRAINTS {DEFERRED
IMMEDIATE}
DML ATOMICITY AT {STATEMENT
ROW }LEVEL
ON {TIMEOUT
DEADLOCK}ROLLBACK {QUERY
TRANSACTION}
USER TIMEOUT [TO] {DEFAULT
MAXIMUM
TimeoutValue[{SECONDS
MINUTES} ]
:HostVariable4[{SECONDS
MINUTES} ] }
TERMINATION AT {SESSION
TRANSACTION
QUERY
RESTRICTED }LEVEL
[{PARALLEL
NO }] FILL}[,...]
- RR
Repeatable Read. Means that the transaction uses locking strategies to
guarantee repeatable reads.
RR is the default isolation level.
- CS
Cursor Stability. Means that your transaction uses locking strategies
to assure cursor-level stability only.
- RC
Read Committed. Means that your transaction uses locking strategies to
ensure that you retrieve only rows that have been committed by some
transaction.
Read Uncommitted. Means that the transaction reads data without obtaining
additional locks.
Use the RU isolation level in applications in which the reading of
uncommitted data is not of concern.
- REPEATABLE READ
Same as RR.
- SERIALIZABLE
Same as RR.
- CURSOR STABILITY
Same as CS.
- READ COMMITTED
Same as RC.
- READ UNCOMMITTED
Same as RU.
- HostVariable1
is a string host variable containing one of the
isolation level specifications above.
- Priority
is an integer from 0 to 255 specifying the priority
of the transaction. Priority 127 is the default. ALLBASE/SQL uses
the priority to resolve a deadlock. The transaction with the largest
priority number is aborted to remove the deadlock.
For example, if a priority-0 transaction and a priority-1
transaction are deadlocked, the priority-1 transaction is aborted.
If two transactions involved in a deadlock have the same priority,
the deadlock is resolved by aborting the newer transaction (the
last transaction begun, either implicitly or with a BEGIN WORK
statement).
- HostVariable2
is an integer host variable containing the priority
specification.
- LabelString
is a user defined character string of up to 8 characters. The default
is a blank string.
The label is visible in the SYSTEM.TRANSACTION pseudo-table and also in
SQLMON. Transaction labels can be useful for troubleshooting and
performance tuning. Each transaction in an application program can be
marked uniquely, allowing the DBA to easily identify the transaction
being executed by any user at any moment.
- HostVariable3
is a string host variable containing the
LabelString.
- ConstraintType
identifies the types of constraints that are affected by the DEFERRED
and IMMEDIATE options. Each ConstraintType can be one of
the following:
UNIQUE
REFERENTIAL
CHECK
- DEFERRED
specifies that constraint errors are not checked until the constraint
checking mode is reset to IMMEDIATE or the current transaction
ends.
- IMMEDIATE
specifies that constraint errors are checked when
a statement executes. This is the default.
- STATEMENT
specifies that error checking occurs at the statement
level. This is the default.
- ROW
specifies that error checking occurs at the row level.
- QUERY
sets the action for timeouts or deadlocks to rollback
the statement or query.
- TRANSACTION
sets the action for timeouts or deadlocks to rollback
the transaction.
- DEFAULT
specifies to use the default timeout duration for
the DBE specified in the START DBE statement.
- MAXIMUM
specifies to use the maximum timeout duration for
the DBE specified in the START DBE statement.
- TimeoutValue
specifies the timeout duration to use in seconds or minutes.
- :HostVariable4
is an integer host variable specifying the timeout
duration to use in seconds or minutes.
- SESSION
specifies self-termination at the session level,
and allows external termination at the session level only.
- TRANSACTION
specifies self-termination at the transaction level, and allows
external termination at the session or transaction level.
- QUERY
specifies self-termination at the query level, and allows external
termination at the session, transaction, or query level.
- RESTRICTED
specifies no self-termination, and allows external termination at the
session level only. This is the default.
- FILL
is used to optimize I/O performance when loading
data and creating indexes.
- PARALLEL FILL
is used to optimize I/O performance for multiple, concurrent loads to
the same table. The PARALLEL FILL option must be in effect for each
load.
- NO FILL
turns off the FILL or PARALLEL FILL option for the duration of the
session. This is the default fill option.
Detailed information about isolation levels is presented in the
"Concurrency Control through Locks and Isolation Levels"
chapter.
You can issue the SET SESSION statement at any point in an
application or ISQL session. Whether issued within or outside of a
transaction, the attributes specified in a SET SESSION
statement apply to the next and subsequent transactions.
Any attribute specified in a SET SESSION statement remains
in effect until the session terminates unless reset by another
statement. See the "Using ALLBASE/SQL" chapter, "Scoping of
Transaction and Session Attributes" section for information about
statements used to set transaction attributes.
When using RC or RU, you should verify the existence of a row
before you issue an UPDATE statement. In application programs
that employ cursors, you can use the 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.
If the FILL or PARALLEL FILL option has already been set for the
session with a SET SESSION statement, and you do not want
either of these options in effect for a given transaction, specify NO
FILL in the transaction's BEGIN WORK statement.
As with the SET CONSTRAINTS statement, the SET
SESSION 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
SESSION 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
nth 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.
In contrast to the SET TRANSACTION statement, transaction
attributes set within a transaction by a SET SESSION statement
are not sensitive to savepoints. That is, if you
establish a savepoint, then issue the SET SESSION statement to
change attribute(s )for the session, and then roll back to the
savepoint, the transaction attribute(s) set after the savepoint are
not undone. In this case, the attribute(s) would go
into effect for the next and subsequent transactions, just as if no
rollback to savepoint had occurred. See Chapter 2
"Using ALLBASE/SQL" "Scoping of Transaction and Session
Attributes" section for information about statements used to set
transaction attributes.
The SET SESSION statement is not allowed within a stored
procedure.
When ON TIMEOUT ROLLBACK or DEADLOCK ROLLBACK is set to
TRANSACTION, the whole transaction is aborted as a result of a
timeout or deadlock.
When ON TIMEOUT ROLLBACK or 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 started on behalf of the user inherits the most
recent transaction attributes of the old transaction. The KEEP
cursor(s) are an exception; they inherit the isolation level
attribute of the old transaction at the time the cursor(s) were
opened. Note, however, that session isolation level is
not used for keep cursor transactions. Session
isolation level does not take effect until KEEP cursors are closed,
the transaction is committed, and the next transaction is begun. For
example:
.
.
.
BEGIN WORK RC
.
.
.
OPEN C1 KEEP CURSOR
.
.
.
SET TRANSACTION ISOLATION LEVEL CS
.
.
.
OPEN C2 KEEP CURSOR
.
.
.
SET TRANSACTION ISOLATION LEVEL RU
.
.
.
SET SESSION ISOLATION LEVEL CS
.
.
.
COMMIT WORK
.
.
.
OPEN C3 Session isolation level does not take effect.
.
.
.
CLOSE C1
CLOSE C2
CLOSE C3
.
.
.
COMMIT WORK
BEGIN WORK Session isolation level CS takes effect.
.
.
.
In the above example, the new transaction started on behalf of the
user after the first COMMIT WORK has isolation level RU;
cursor C1 has isolation RC; cursor C2 has isolation level CS; and
cursor C3 has isolation level RU.
You do not need authorization to use the SET SESSION statement.
The following example illustrates setting session level deferred constraint
checking, DML atomicity, and the FILL option to enhance load performance within
ISQL.
COMMIT WORK;
SET LOAD_BUFFER 65536;
SET AUTOSAVE 3000
SET LOAD_ECHO AT_COMMIT;
SET AUTOCOMMIT ON;
SET AUTOLOCK ON;
SET SESSION UNIQUE, REFERENTIAL, CHECK CONSTRAINTS DEFERRED,
DML ATOMICITY AT ROW LEVEL,
FILL;
.
.
.
BEGIN WORK;
LOAD FROM EXTERNAL Price TO PurchDB.SupplyPrice;
LOAD FROM EXTERNAL Parts TO PurchDB.Parts;
.
.
.
COMMIT WORK;
In the above example, a COMMIT WORK is automatically performed when
3000 rows have been loaded from external files into the database tables. A new
transaction is started on behalf of the user to continue to load remaining
rows. Each new transaction uses the default session isolation level (RR).
|