 |
» |
|
|
|
|
|
5.40 Describe the read committed isolation level
Key points
- Read Committed (RC) -- S locks are automatically released by ALLBASE/SQL
immediately after they are obtained.
- RC has the same characteristics as CS, except that locks are released
immediately after each read, instead of waiting until the cursor moves (that
is, until the next
FETCH is issued). ALLBASE/SQL acquires (and releases)
appropriate S locks (and intention locks) when each FETCH statement is issued.
S locks are always obtained for both updatable and non-updatable cursors
(SIX locks are not obtained and released for updatable cursors).
- During any
FETCH statement, S 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 (the internal processing is similar
to CS processing). At the end of the FETCH, the retrieved row is not locked
and none of the other examined rows are locked.
- With RC, as with RR and CS, you can only retrieve rows that have been
committed by some other transaction. In other words, you cannot read rows (or
pages) that have been modified (locked with an X lock) or are in the
process of being modified (locked with an SIX lock) by some other
transaction that has not yet terminated.
- If you need to update or delete a row using a cursor in an RC transaction,
you must use the
REFETCH statement to verify that the row has not changed
between the FETCH statement and the UPDATE WHERE CURRENT or the DELETE WHERE
CURRENT statement. The S lock is released immediately after the
FETCH, so another transaction may have modified the row after the FETCH
was issued. To ensure that your transaction does not accidentally overwrite
changes made by some other transaction, use the REFETCH statement to retrieve
the row for a second time and examine its current value before updating or
deleting it. A row cannot be changed by another transaction between the time
you issue the REFETCH statement and the time you issue an UPDATE WHERE CURRENT
statement in the same transaction, because the REFETCH statement obtains an
SIX lock (at the row level on a PUBLICROW table and at the page level on a
PUBLIC table).
- If another transaction has modified (or is modifying) the row (or page) but
has not yet committed the change (that is, has acquired an X or SIX lock on
the data), the
REFETCH transaction will wait (if another transaction is
reading the data, the REFETCH transaction will also wait). After the SIX lock
has been granted, the REFETCH transaction can determine whether or not other
changes were made to the row between the time of the FETCH and the time of the
REFETCH, and then modify the row appropriately.
- The
UPDATE WHERE CURRENT and DELETE WHERE CURRENT statements will obtain an
X lock at the row level on a PUBLICROW table and at the page level on a PUBLIC
table. Appropriate intention locks are also acquired.
- By default under any isolation level, X locks and IX intention locks on pages
containing modified rows are retained until the transaction ends. SIX locks
obtained by the
REFETCH command are also retained until the transaction ends,
if they are not converted to X locks.
Page last updated on November 29, 1995
|
|
|
Printable version
