Using CS, RC, and RU Isolation Levels

You can improve concurrency by using the Cursor Stability (CS) or Read Committed (RC) isolation levels with the BEGIN WORK statement. The effect of these options is to release shared locks before the transaction ends, whereas Repeatable Read (RR) holds them until the end of the current transaction. Read Uncommmitted (RU) promotes still greater concurrency by not obtaining any locks on user tables for read operations. The greatest benefit is obtained with RU if all your applications are using it. This allows a minimum of locking in the system, and a minimum of waiting for other locks to be released.

Use appropriate isolation levels for the kind of read/write operations you are performing:

Use Cursor Stability for long serial reads with occasional updates. Cursor Stability will increase concurrency during serial reads. It also improves the throughput for a single writer waiting on multiple readers. When using Cursor Stability in a transaction, row and page level READ locks are released behind you as you move through a table.
Use Read Committed for read-only operations in which it is important to access committed data. Read Committed releases locks as soon as data is read.
Use Read Uncommitted for read-only operations in which it is not important that all the data you read has been committed. Read Uncommitted does not obtain locks, so it permits you to read dirty pages, that is, pages that may be in the process of being updated by some other transaction. RU operations are known as dirty reads.

Users of CS, RC, and RU should be aware of the following:

Regardless of isolation level, write operations (INSERT, UPDATE, DELETE) obtain exclusive locks, which are not released until the end of the transaction.
Despite the choice of a different isolation level, system catalog pages are still locked at the RR level. Therefore, deadlocks and lock waits involving system catalog pages are possible if your applications use DDL (data definition language).

All isolation levels work with sorted query results and with Type 2 INSERT statements. For more information, see the chapter "Concurrency Control Through Locks and Isolation Levels" in the ALLBASE/SQL Reference Manual. Also see the description of the BEGIN WORK statement in the "SQL Statements" chapter of the ALLBASE/SQL Reference Manual.

Using CS, RC, and RU Isolation Levels

Technical documentation

» Table of Contents

» Index