 |
» |
|
|
|
|
|
5.39 Describe the cursor stability isolation level (continued)
Key points (continued)
- If CS is used in a transaction having a non-updatable cursor,
cursor level stability is not guaranteed. For
example, if a temporary table is used to access the rows in the active set,
the following is true:
- A query that involves a sort operation (such as an ORDER BY, GROUP BY,
DISTINCT, or UNION, or a query that uses a sort/merge join to join tables)
may use a temporary table for the query result. When CS is used in such
cases, your cursor actually points to rows in this temporary table, not to
rows in a user table.
- The temporary table is created when the
OPEN statement is issued.
- When CS is used, S locks are obtained and released on the user tables from
which data is retrieved when the temporary table is created. Pages that
might appear to be accessed by the current transaction's cursor are actually
not locked at all, and other transactions are able to modify these pages.
- When you issue each
FETCH statement, ALLBASE/SQL does not re-sort to create a
new temporary table, it simply retrieves another row from the existing
temporary table. If your transaction does not retain locks on the original
user table, other transactions can modify it (or even drop it). Therefore,
your transaction may fetch a row that logically does not exist any more, or it
may see an older version of a row that has since been modified. In such
cases, it is the application developer's responsibility to maintain data
integrity by verifying the current value of a row before updating it or using
it as the basis for updating another table.
- To retain S or SIX locks on a user table in a transaction that includes a
non-updatable cursor, use one of the following:
- Use the RR isolation level.
- Use the
LOCK TABLE command on the table at the start of the transaction.
A table level lock will be obtained and retained until the transaction ends.
- CS provides greater read and write concurrency than RR to other transactions
on data read by updatable cursors:
- Greater read concurrency is achieved because other users can read rows as soon
as a CS transaction moves the cursor. In an RR transaction, users must wait until
the transaction terminates if they need to read rows (or pages) that have
obtained SIX locks by the RR transaction.
- Greater write concurrency is achieved because other users can modify rows as
soon as a CS transaction moves the cursor. Users must wait until
the transaction terminates if they need to modify rows (or pages) that have
obtained S or SIX locks by the RR transaction.
- Use the CS isolation level for transactions that contain updatable cursors
that need to scan through rows of committed data in a table, but may only
update a few. If the cursor is updatable, then CS guarantees that a row will
not change between the time you issue the
FETCH statement and the time you
issue an UPDATE WHERE CURRENT statement in the same transaction.
Page last updated on November 29, 1995
|
|
|
Printable version
