HP 3000 Manuals HP 3000 Manuals
SET SESSION
[REV BEG]
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.[REV END]
Scope
ISQL or Application Programs
SQL Syntax
[REV BEG]
{ {RR } }
{ {CS } }
{ {RC } }
{ {RU } }
{ISOLATION LEVEL {REPEATABLE READ } }
{ {SERIALIZABLE } }
{ {CURSOR STABILITY} }
{ {READ COMMITTED } }
{ {READ UNCOMMITTED} }
{ {:HostVariable1 } }
{ }
{PRIORITY {Priority } }
{ {:HostVariable2} }
{ }
{LABEL {'LabelString' } }
{ {:HostVariable3} }
{ }
{ConstraintType [,...] CONSTRAINTS {DEFERRED }}
{ {IMMEDIATE}}
{ }
SET SESSION {DML ATOMICITY AT {STATEMENT} LEVEL } [,...]
{ {ROW } }
{ }
{ON {TIMEOUT } ROLLBACK {QUERY } }
{ {DEADLOCK} {TRANSACTION} }
{ }
{ {DEFAULT } }
{ {MAXIMUM } }
{ {TimeoutValue [{SECONDS}] } }
{USER TIMEOUT [TO]{ [{MINUTES}] } }
{ { } }
{ {:HostVariable4[{SECONDS}]} }
{ { [{MINUTES}]} }
{ }
{ {SESSION } }
{TERMINATION AT {TRANSACTION} LEVEL }
{ {QUERY } }
{ {RESTRICTED } }
{ }
{[{PARALLEL}] FILL }
{[{NO }] }
[REV END]
Parameters
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.
RU 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.
[REV BEG]
HostVariable1 is a string host variable containing one of the
isolation level specifications above.[REV END]
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).
[REV BEG]
HostVariable2 is an integer host variable containing the
priority specification.[REV END]
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.[REV BEG]
HostVariable3 is a string host variable containing the
LabelString.[REV END]
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.[REV BEG]
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.[REV END]
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.
Description
* 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 the "Using ALLBASE/SQL" chapter,
"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.[REV BEG]
* 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.)[REV END]
* 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.
Authorization
You do not need authorization to use the SET SESSION statement.
Example
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).