 |
» |
|
|
|
|
|
5.50 How does keep cursor affect locking (continued)?
Key points
- Understanding KEEP CURSOR WITH NOLOCKS
- When the
WITH NOLOCKS clause is used, all locks (including those
associated with the position of the kept cursor) are released when you issue
a COMMIT WORK or a ROLLBACK WORK statement.
- Because locks associated with the position of the kept cursor are not
retained, it is possible that tables or indexes on which the cursor depends
might be modified in a way that is catastrophic to the cursor. For example, a
table might be dropped. In general, it is wise to disable data definition
using the SQLUTIL
ALTDBE command before using the KEEP CURSOR WITH NOLOCKS
option.
- If you open a cursor using the KEEP CURSOR WITH
NOLOCKS option in an RR, CS, RC or RU transaction, and the cursor is
updatable, data integrity is guaranteed (rows retrieved
via a
FETCH or a REFETCH statement are guaranteed to be current). It is not
possible for one user to accidentally overwrite changes made by another user,
as long as each transaction reviews rows that are fetched (or refetched) prior
to making updates.
- When you issue a
COMMIT WORK statement, all locks are released. This permits
other transactions to delete or modify rows in the user table. When the first
FETCH statement following a COMMIT WORK statement is issued, ALLBASE/SQL will
reacquire appropriate S or SIX locks (and intention locks). If RC or RU is
used, these locks are immediately released, so the REFETCH statement is
needed to acquire SIX locks prior to making an update. It is possible that
another transaction could have modified the data between the time of the
COMMIT WORK and the time of the FETCH (or REFETCH). As long as your
transaction reviews rows that are fetched (or refetched) prior to making
updates, data integrity will not be affected.
- If you open a cursor using the KEEP CURSOR WITH NOLOCKS option in an RR
transaction and the cursor is not updatable, data integrity is no
longer guaranteed.
- Remember that if a transaction includes a cursor that is not based on an
updatable query, a temporary table might be used for the query result.
When you issue each
FETCH statement on such a cursor, ALLBASE/SQL simply
retrieves the next 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 from the temporary table that logically no longer
exists in the original table, or it may see an older version of a row
that has since been modified.
- Normally, if the RR isolation level is used for such a cursor, data integrity
is guaranteed because locks are retained on the user tables from which the
temporary table was created. However, if the cursor is opened using the KEEP
CURSOR WITH NOLOCKS option, all locks are released when you issue each
COMMIT WORK statement, and other transactions can modify rows in the
original table. 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.
Page last updated on November 29, 1995
|
|
|
Printable version
