Micro Focus Enterprise Developer 3.0 for Visual Studio 2013 > Programming > Mainframe Programming > SQL Option for DB2 > Reference > SQL Reference > SQL Statements > SET ISOLATION

SET ISOLATION

The SET ISOLATION command is used to set the isolation level for locking in multi-user versions.

Invocation

SET ISOLATION is an executable XDB Server utility command that can be issued interactively or embedded in a host language. SET ISOLATION can be dynamically prepared.

Authorization

SYSADM or SYSCTRL authority for the location is required for setting isolation level to EU (exclusive use).

Syntax

SET ISOLATION { EU | RR | CS | LC | DR | SR }

Parameters:

`EU (Exclusive Use)`	Locks the database for the exclusive use of the user issuing the command.
`RR (Repeatable Read)`	Guarantees identical results if the user issues the same SQL query more than once in a transaction. This isolation level provides minimum concurrency and maximum consistency.
`CS (Cursor Stability)`	Creates a lock on a record that allows the record to be read but not modified. This lock is held only while the record is the current record. Locks on modified records are held until the transaction is committed. Shared table locks can be unlocked before the end of a transaction using the UNLOCK TABLE command. The CS isolation level provides more concurrency and less consistency than RR.
`LC (Lock Current)`	Holds a lock on a record only while it is the current record. The LC isolation level provides a high degree of concurrency, and a very low degree of consistency, unless used for read-only operations, or with autocommit on.
`DR (Dirty Read) or UR (Uncommitted Read)`	Does not acquire locks on records, but reads records regardless of the locks held by others. This isolation level is be used strictly for read-only operations.
`SR (Snapshot Read)`	Causes a snapshot of the data to be made at the time of execution. Other users can make changes to the data without waiting for the transaction to complete, and the user is ensured that the data will be the same for the life of the transaction. The transaction is limited to read-only operations.

Description

The XDB Server permits you to set an isolation level to control the concurrency and consistency within a multi-user environment.

As described above, six isolation levels are supported. Both Repeatable-Read (RR) and Cursor Stability (CS) provide the following data isolation from other users:

An active transaction cannot modify or read any data which other active transactions have modified.
A transaction cannot modify a record currently being read by another transaction.

In addition, under Repeatable-Read (RR) no active transaction can modify a record which has been read by another active transaction specifying an RR level of isolation.

Cursor Stability (CS) is used if your application will generate or encounter many lock conflicts.

Exclusive Use (EU) locks are used for those procedures that require that the database be in a quiet state.

Lock Current (LC) locks are used for read-only operations or with autocommit set on. With autocommit on, this level of isolation is very useful for interactive sessions.

Example:

Some sample SET ISOLATION commands appear below:

SET ISOLATION EU
SET ISOLATION RR
SET ISOLATION CS
SET ISOLATION LC
SET ISOLATION DR
SET ISOLATION SR

Note:

If the isolation level is set to any setting other than Repeatable Read, for optimization the isolation level is changed to Dirty Read whenever an ORDER BY or GROUP BY is performed. In future versions of the XDB Server, the isolation level optimization will be configurable by the user.