HP 3000 Manuals HP 3000 Manuals
Defining Isolation Levels between Transactions
Isolation level is the degree to which a transaction is separated from
all other concurrent transactions. Four levels are possible, shown here
in order from most to least restrictive:
* Repeatable read (RR)--the default
* Cursor stability (CS)
* Read committed (RC)
* Read uncommitted (RU)
In general, you should choose the least restrictive possible isolation
level for your needs in order to achieve the most concurrency. You
select an isolation level in the BEGIN WORK statement, as in the
following example:
isql=> BEGIN WORK CS;
An isolation level can also be specified with either the SET TRANSACTION
or SET SESSION statement.
Repeatable Read (RR)
By default, transactions have the Repeatable Read (RR) isolation level,
which means that within the transaction, you can access the same data as
often as you wish with the certainty that it has not been modified by
other transactions. In other words, other transactions are not allowed
to modify any data pages that have been read by your transaction until
you issue a COMMIT WORK or ROLLBACK WORK statement. This is the most
restrictive level, allowing the least concurrency.
All the examples of transactions shown so far use the RR (repeatable
read) isolation level. At the RR level, all locks are held until the
transaction ends with a COMMIT WORK or ROLLBACK WORK statement. This
option causes each data row or page read to be locked with a share lock,
which forces any other user trying to update the data on the same row or
page to wait until the current transaction completes. However, other
transactions may read the data on the same row or page. For PUBLICROW
tables, if you update a row during a transaction, the row receives an
exclusive lock, which forces other transactions to wait for both reading
or writing that row until your transaction ends. For PUBLIC tables, if
you update a data page during a transaction, the page receives an
exclusive lock, which forces other transactions to wait for both reading
or writing until your transaction ends. Repeatable Read should be used
if you must read the same data more than once in the current transaction
with assurance of seeing the same data on successive reads.
Cursor Stability (CS)
The Cursor Stability (CS) isolation level guarantees the stability of the
data your cursor points to. However, this isolation level permits other
transactions to modify rows of data you have already read, provided you
have not updated them and provided they are not still in the tuple
buffer. CS also permits other transactions to update rows in the active
set which your transaction has not yet read into the tuple buffer. With
cursor stability, if you move your cursor and then try to reread data you
read earlier in the transaction, that data may have been modified by
another transaction. At the CS level, share locks on data (whether at
the row or page level) are released as soon as the associated rows are no
longer in the tuple buffer. Exclusive locks are held until the
transaction ends with a COMMIT WORK or ROLLBACK WORK statement. The
following describes what using CS means:
* No other transactions can modify the row on which the transaction
has a cursor positioned.
* A shared lock is kept on the row or page that the cursor is
currently pointing to. When the cursor is advanced to the next
page of data and nothing has been updated on the previous page,
the lock on that previous page is released.
* If an update is done on a data page, the exclusive lock on that
page is retained until the transaction ends with a COMMIT WORK or
ROLLBACK WORK statement.
Use the CS isolation level for transactions in which you need to scan
through large portions of a database to locate rows that need to be
updated immediately. CS lets you do this without preventing other
transactions from updating data pages that you have already passed by
without updating. CS guarantees that a row of data will not be changed
between the time you issue the FETCH statement and the time you issue an
UPDATE WHERE CURRENT in the same transaction.
NOTE When you use CS for a query that involves a sort operation, such as
an ORDER BY, DISTINCT, GROUP BY, or UNION, or when a sort/merge
join is used to join tables for the query, the sort may use a
temporary table for the query result. In such cases, your cursor
actually points to rows in this temporary table, not to rows in the
tuple buffer. Therefore, when sorting is involved, the locks held
on data pages or rows are released before you manipulate the
cursor. In other words, no locks are held at the cursor position
for sorted scans at the CS isolation level. If it is important to
retain locks in this situation, use the RR isolation level.
If you are updating a row based on the information in a sorted
query result, use a simple SELECT statement to verify the continued
existence of the data before doing the update operation. In this
case, it is good practice to include the TID as part of the
original SELECT, and then to use the TID in the WHERE clause of the
SELECT that verifies the data.
Read Committed (RC)
With Read Committed, you are sure of reading consistent data with a high
degree of concurrency. However, you are not guaranteed the ability to
reread the data your cursor points to, because other transactions can
modify that data as soon as it has been read into your application's
tuple buffer. Also, you cannot read rows or pages from the data buffer
that have been modified by another transaction unless that other
transaction has issued a COMMIT WORK statement. At the RC level, share
locks on data are released as soon as the data has been read into your
buffer. Exclusive locks are held until the transaction ends with a
COMMIT WORK or ROLLBACK WORK statement.
The following describes what using RC means:
* 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.