The result set of rows for a cursor may be a static set of rows copied to a temporary table before any rows are returned to your program, or the rows may be retrieved dynamically as you FETCH the rows. You may use isolation levels C or R to isolate your transactions from other concurrent transactions, but this does not isolate your cursor from changes your transaction is making while you are FETCHing rows from a cursor that is dynamically retrieving rows. For example, if you update a row that has been FETCHed with a separate searched UPDATE statement, the same row may be returned in a subsequent FETCH if the updated values place it in the result set ahead of the dynamic retrieval process.
Note: If you use an UPDATE where current of <-cursorName>, CA Datacom/DB insures that the row is not returned again (at the expense of building a temporary index of updated URIs).
To isolate your cursor from changes made by other statements in your program while the cursor is open, you can use an ORDER BY, that cannot be satisfied by any key, to force a temporary table to be built. However, if such a key is added in the future, this new key may be used to eliminate building a temporary table. Also, even if you specified isolation level C, the current row of the cursor may not be locked, because a block of rows is returned to your program and the cursor stability lock on the row has already been released.
To ensure the current row of the cursor is locked, you can specify an UPDATE or DELETE where current of <-cursorName> for the cursor. Even if you never execute these statements, blocking is not used, and the current row of the cursor is held by an exclusive lock until you FETCH the next row.
|
Copyright © 2015 CA Technologies.
All rights reserved.
|
|