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.
