ALLBASE/SQL Reference Manual (36216-90216)

176 Chapter5
Concurrency Control through Locks and Isolation Levels
Defining Isolation Levels between Transactions
• You can retrieve only rows that have been committed by some transaction or modified
by your own transaction.
• Other transactions
can write on the page on which the transaction has a cursor
positioned, because locks are released as soon as data is read.
• If an update is done on a page, the lock is retained until the transaction ends with a
COMMIT WORK or ROLLBACK WORK statement.
Use the RC isolation level for improved concurrency, especially in transactions which
include a long duration of time between fetches. When you must update following a FETCH
statement using the RC isolation level, use the REFETCH statement first, which obtains and
holds locks on the current page, thus letting you verify the continued existence of the data
you are interested in.
Read Uncommitted (RU)
The Read Uncommitted (RU) isolation level lets you read anything that is in the data
buffer, whether or not it has been committed, in addition to pages read in from disk. For
example, someone else's transaction might perform an update on a page, which you can
then read; then the other transaction issues a ROLLBACK WORK statement which cancels the
update. Your transaction has thus seen transitory data which was not committed to the
database. At the RU level,
no share locks are obtained on user data. Exclusive locks
obtained during updates are held until the transaction ends with a COMMIT WORK or
ROLLBACK WORK statement.
The following describes what using RU means:
• The transaction does not obtain any locks on user data when reading, and therefore
may read uncommitted data.
• The transaction does not have to wait on locks on user data, so deadlocks are
considerably reduced. However, transactions may still have to wait for system catalog
locks to be released.
• If an update is done on a page, the transaction obtains an exclusive lock, which is
retained until the transaction ends with a COMMIT WORK or ROLLBACK WORK
statement.
RU is ideal for reporting and similar applications where the reading of uncommitted data is
not of major importance. If you must update following a FETCH statement using the RU
isolation level, use the REFETCH statement first, which obtains and holds the appropriate
locks, letting you verify that you are not updating a row based on uncommitted data.