SET TRANSACTION ISOLATION LEVEL statement

Syntax

SET TRANSACTION ISOLATION LEVEL level

Description

Use the SET TRANSACTION ISOLATION LEVEL statement to set the default transaction isolation level you need for your program.

Note: The isolation level you set with this statement remains in effect until another such statement is issued. This affects all activities in the session, including InfoSphere® DataStage® commands and SQL transactions.

The SET TRANSACTION ISOLATION LEVEL statement cannot be executed while a transaction exists. Attempting to do so results in a run-time error message, program failure, and the rolling back of all uncommitted transactions started in the execution environment.

level has the following syntax:

{n | keyword | expression}

level is an expression that evaluates to 0 through 4, or one of the following keywords:

Table 1. Effects of ISOLATION LEVELs on Transactions
Integer	Keyword	Effect on This Transaction
0	NO.ISOLATION	Prevents lost updates.¹
1	READ.UNCOMMITTED	Prevents lost updates.
2	READ.COMMITTED	Prevents lost updates and dirty reads.
3	REPEATABLE.READ	Prevents lost updates, dirty reads, and nonrepeatable reads.
4	SERIALIZABLE	Prevents lost updates, dirty reads, nonrepeatable reads, and phantom writes.

¹^{Lost
updates are prevented if the ISOMODE configurable parameter is set to 1 or
2.}

Examples

The following example sets the default isolation level to 3 then starts a transaction at isolation level 4. The isolation level is reset to 3 after the transaction finishes.

SET TRANSACTION ISOLATION LEVEL REPEATABLE.READ
PRINT "We are at isolation level 3."
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
         PRINT "We are at isolation level 4."
         COMMIT WORK
END TRANSACTION
PRINT "We are at isolation level 3"

The next example uses an expression to set the transaction level:

PRINT "Enter desired transaction isolation level:":
INPUT TL
   SET TRANSACTION LEVEL TL
   BEGIN TRANSACTION
      .
      .
      .
   END TRANSACTION