 |
» |
|
|
|
|
|
5.36 Describe the repeatable read isolation level (continued)
Key points (continued)
- A cursor is a pointer that you advance through a set of rows
associated with a
SELECT statement. The FETCH statement is used to
move the cursor and retrieve one or multiple rows in the active set.
- Assume that a cursor is used in an RR transaction. For each table involved in
the query associated with the cursor, the following locks are obtained by
default on rows in a PUBLICROW table or on pages in a PUBLIC table when rows
are retrieved using the
FETCH statement:
- If the
FOR UPDATE clause is not used in the DECLARE CURSOR statement,
the rows are simply read:
- If the optimizer chooses an index, hash, or TID scan to obtain the rows in the
active set, S locks are obtained on the rows (or pages). Appropriate
intention locks are also obtained.
- If the optimizer chooses a serial scan to obtain the rows in the active set,
a single S lock is obtained on the table.
- If an S lock is obtained at the row, page, or table level, other
transactions can read all rows (or pages) in the table that the
DECLARE CURSOR
transaction has read, but has not modified. In this case, concurrency with
other read transactions is not reduced.
- If the
FOR UPDATE clause is used in the DECLARE CURSOR statement,
some rows in a table are read with the intention of writing to them:
- The access plan of any query can consist of a complex sequence of
subqueries. If the access plan for the query on which the cursor is defined
includes a subquery that simply reads data, locks for the data in that
subquery will be obtained as described above. (In other words, S locks can be
acquired by a cursor defined with a
DECLARE CURSOR FOR UPDATE statement).
- If the table is being updated, the following is true:
- If the optimizer chooses an index, hash, or TID scan to access data in the
table, SIX locks are obtained on the rows (or pages) that qualify.
Appropriate intention locks are also obtained. When an SIX lock is
obtained at the row or page level, other transactions cannot read any of
the data that the SIX transaction has read but not modified.
- If the optimizer chooses a serial scan to access data in the table, a single
SIX lock is obtained on the table. Another transaction can read rows (or
pages) in the table that the SIX transaction has read but not modified;
however, the other transaction cannot read the rows with the intention of
writing to them, and it cannot read all of the rows in the table.
- If a transaction has obtained an SIX lock at the row, page, or table level,
another transaction that wishes to read all of the rows in the table must
wait until the SIX lock (and intention locks) are released.
- In an RR transaction, SIX locks are held until the transaction terminates.
Concurrency with other read or read with an intention to write
transactions can be reduced when RR is used in transactions that contain
updatable cursors.
Page last updated on November 29, 1995
|
|
|
Printable version
