 |
» |
|
|
|
|
|
5.38 Describe the cursor stability isolation level
Key points
- Cursor Stability (CS) -- S locks are released before the transaction ends
(the application program has some control over when they are released).
- The primary use of Cursor Stability is to improve the concurrency of
transactions that contain updatable cursors. When you use CS in a transaction
with an updatable cursor on a PUBLICROW or PUBLIC TABLE, the following is true
for that cursor:
- During any
FETCH statement, S or SIX locks might be obtained and released on
rows (or pages) when locating the next row of the active set. Appropriate
intention locks would also be obtained and released. In the example above,
if a serial scan was used instead of an index scan, the
following would occur to locate the first row in the active set:
- The first row in the table would be locked with an SIX lock, and the row would be
examined to see if it qualifies for the query associated with the cursor.
- If it does not qualify, the SIX lock would be released, and the next
row in the table would be locked with an SIX lock and examined.
- This process repeats until a row is found that qualifies for the query. At
the end of the the
FETCH, only this last row remains locked with an SIX lock.
- At the end of the
FETCH statement, only a subset of rows in the query result
remain locked:
- The row (or page) that was just fetched has an SIX lock. This row is
stable (that is, it cannot be changed by another transaction) as long as
the cursor points to it (that is, until another
FETCH statement is issued).
An IX intention lock for this row also exists at the page level if the table
is PUBLICROW.
- If the CS transaction itself modifies this row, the existing SIX lock is
converted to an X lock.
- An IX intention lock exists at the table level, regardless of whether the
optimizer has chosen a serial scan or an index, hash, or TID scan (using RR,
an SIX lock is obtained on the table if a serial scan is chosen). An IX lock
at the table level provides more concurrency than an SIX lock, especially to
other similar transactions: IX is compatible with IX, but SIX is not
compatible with SIX.
- By default under any isolation level, X locks and IX intention locks on pages
containing modified rows are retained until the transaction ends.
- When the next
FETCH is performed and the cursor moves, the following locks are
released:
- SIX locks held on rows (or pages) that were not updated.
- IX page locks for pages on which rows were not modified. If the
cursor moves to another row on the same page as the last row that was fetched,
the IX page lock is retained (instead of being released and reacquired).
Page last updated on November 29, 1995
|
|
|
Printable version
