The FETCH locks the row either according to the isolation level of
the statement [which could be from a with-isolation-clause or SET OPTION
or an equivalent parameter] or according to the implicit update
capability of the SELECT [when missing a FOR READ ONLY clause] or its
explicit update-clause specification [i.e. FOR UPDATE <OF ...>].
Unlike with RPG where a row update operation causes the releases lock
to be released, with the SQL it is not the UPDATE statement that
releases the lock when there is no commitment control [i.e. isolation
WITH NC]. The CLOSE of the cursor will drop the UPDATE-lock held on the
row when the row was updated with NO COMMIT. When run under isolation
however, it is the COMMIT that will cause the update lock to be dropped.
Regards, Chuck
On 20 Mar 2013 12:28, Steve Richter wrote:
ok. good. For some reason I was thinking the FETCH would not lock
the record. Which does not make sense.
so I just have to declare the cursor to select a single record from
the file. The FETCH locks it. The UPDATE releases the lock.
On Wed, Mar 20, 2013 at 3:22 PM, DeLong, Eric wrote:
I think all you need to do is add the "for update of ... " clause.
As I recall, this forces the fetch to lock the current row, pending
the "UPDATE ... WHERE CURRENT OF myCursor".
