HP 3000 Manuals

ALLBASE/SQL Performance Guidelines

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.

MPE/iX 5.0 Documentation