SET SESSION

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.

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.