 |
» |
|
|
|
|
|
5.44 When should I use each isolation level if my cursor is updatable?
Key points
- If the transaction you are using has an updatable cursor, the
following can help you select the appropriate isolation level.
- If the transaction needs to retain all locks until the end of the transaction
(it needs to repeatably read rows and be guaranteed that the
rows have not changed from the first time that they were read), use the
RR isolation level.
- If the transaction uses a
BULK FETCH statement to manipulate the cursor and
uses the UPDATE statement to modify the rows that were fetched, use the
RR isolation level.
- If the transaction needs to scan through rows of committed data in a table
and it will update all or most of the rows in the active set, use the CS
isolation level:
- If all of the rows in the table are in the active set and all rows are
updated, CS will behave like RR because all row (or page) locks will be
X locks, and X locks are held until the end of the transaction.
- If the active set does not include all rows in the table, or if some rows (or
pages) are not updated, CS is better than RR because some S or SIX locks
will be released prior to the end of the CS transaction.
- If all or most of the rows in the table are in the active set, you may also
want to issue the
LOCK TABLE IN EXCLUSIVE MODE statement to minimize shared
memory needs.
- If the transaction needs to scan through rows of committed data in a table,
but it will only update a few of the rows in the active set, use either of
the following isolation levels.
- Use the RC isolation level for maximum concurrency, especially when
- A relatively large amount of time elapses between fetches and updates, or
- The number of users that will access the table concurrently is large
(the cost of holding an SIX lock is high).
- Use the CS isolation level for maximum lock efficiency in ALLBASE/SQL,
especially when either of the following situations is true.
- A small amount of time elapses between fetches and updates.
- The number of users that will access the table concurrently is small
(the cost of holding an SIX lock is low). If you use CS, there is
no need to use the
REFETCH statement. This improves performance and also
reduces the number of sections in the DBEnvironment.
- If the transaction can scan through rows of uncommitted
data in addition to rows of committed data, use the RU isolation level
to update a few of the rows. Use CS
if the transaction will update all or most of the rows.
Page last updated on November 29, 1995
|
|
|
Printable version
