 |
» |
|
|
|
|
|
5.37 Describe the repeatable read isolation level (continued)
Key points (continued)
- The example above shows the locks that are obtained by a
non-updatable cursor in an RR transaction, if an index scan is chosen
to access the rows in the active set.
- If a serial scan is chosen instead of an index scan,
a single S lock is obtained at the table level when the first
fetch is made. Individual S locks at the row level are not obtained.
- If the cursor is updatable and an index scan is chosen to access the rows
in the active set, SIX row locks are obtained instead of
S row locks.
- If the cursor is updatable, and a serial scan is chosen,
a single SIX lock is obtained at the table level when the first
fetch is made.
- Use the RR isolation level for a transaction when you want to
retain all locks until the transaction terminates. This is especially
necessary when you need 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 for a transaction that contains a non-updatable
cursor if you need to view a consistent snapshot of the data at a single point
in time, and especially if you need to make data modifications based on the
values returned by the cursor.
- If you make data modifications, you must use SQL statements other than the
UPDATE WHERE CURRENT or the DELETE WHERE CURRENT statements to accomplish your
changes, because the data is not updatable through the cursor.
- Use the RR isolation level for a transaction in which you perform a
BULK FETCH
and then use the UPDATE statement (not UPDATE WHERE CURRENT) to modify the
rows that were fetched.
- Remember, do not use either the
UPDATE WHERE CURRENT or the DELETE
WHERE CURRENT statements when you perform a BULK FETCH. The cursor can only
point to a single row at a time. When FETCH is used to retrieve multiple
rows, the cursor sequentially moves to each row that is fetched during
internal processing. At the end of the BULK FETCH, the cursor points to the
last row that was returned. If an isolation level other than RR is used, the
S or SIX locks that may have been obtained on the other rows in the BULK FETCH
are released. To ensure data integrity (that is, to ensure that you do not
accidentally overwrite changes made by another user), you must use the RR
isolation level to retain locks on these rows to guarantee that they are not
modified by another transaction before your UPDATE statement is executed.
Page last updated on November 29, 1995
|
|
|
Printable version
