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.
SYSADM or SYSCTRL authority for the location is required for setting isolation level to EU (exclusive use).
SET ISOLATION { EU | RR | CS | LC | DR | SR }
| 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. |
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
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.
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:
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.