Isolation Levels

The isolation level in effect determines the manner in which locks are acquired and released by the system. The XDB Server provides the following isolation levels:

• Level 1: Exclusive Use (EU). At Level 1 the system locks an entire location for the exclusive use of the current user. In essence, the system is operating in single user mode. This level of isolation should be used when performing such tasks as compacting the location, backing up the location, and recovering the location.
• Level 2: Repeatable Read (RR). Level 2 guarantees that records read will not have been changed by another transaction from the time a lock is acquired on an object until the end of the transaction; that is, at this level of isolation all locks are held until the transaction is either committed or rolled back. This level provides a high degree of consistency and a relatively low degree of concurrency.
• Level 3: Cursor Stability (CS). Cursor Stability is the default server isolation level. Under Level 3 exclusive locks are retained until commit or roll back, but record level share locks are released when the cursor leaves the record. The system automatically handles the release of all record locks types. This level of isolation provides less consistency and more concurrency than the RR level of isolation.

  The CS level of isolation is often used in combination with autocommit off by interactive applications where users may browse through records one at a time. In such an environment it is only necessary to maintain a lock on a record if the record is modified in some way. After the user moves to the next record, locks on unchanged records are released.

  Level 4: Lock Current (LC). At Level 4 the system acquires a lock when a record is referenced, but holds the lock only until the cursor moves to the next record. Note that exclusive locks are also released when the cursor moves to the next record; therefore this level should be used for read-only transactions, or used only when autocommit is turned on. Autocommit generates a commit command after each update takes place.

  Level 5: Dirty Read (DR). At Level 5 the system acquires no locks on a referenced record. The transaction operates in read-only mode and cannot make changes to database objects. This isolation level can be used to generate reports without delays while the system attempts to lock records. Records read at this isolation level may reflect changes made, but not committed. Note that these changes may be subsequently rolled back.

  Level 6: Snapshot Read (SR). At Level 6 a read-only snapshot of the data is made at the time of execution. No locks are held on the data, so other users can access and make changes to the data without waiting for the transaction working at SR to complete. The transaction working at SR isolation level is assured that the data it is working with will remain the same for the duration of the transaction. The SR transaction cannot make changes to the data.

The locks described above are automatically acquired by the system. At any isolation level it is also possible to explicitly request and release table level locks using the LOCK and UNLOCK commands. Table level locks may also be automatically acquired by the system if a command that affects the table definition is issued (for example, ALTER, RENAME, CREATE INDEX). Table level locks that are held at a commit point are automatically released, therefore it is not necessary to explicitly release a table level lock.

The default level of isolation for the XDB Server is CS (Cursor Stability). In order to ensure a different level of isolation it is necessary to issue a command to reset the isolation level. The startup isolation level is set through the Options Utility. You can also temporarily change the isolation level by using the SET ISOLATION command (described in this manual) and by using the WITH clause (available in DB2 4.x and beyond) on certain SQL statements.

It is important to consider how different users' isolation level settings impact the integrity of the database. The database administrator must coordinate the isolation level requirements for all users, and define those cases in which exceptions will be permitted.