Re: equivalent of lock and update in SQL

[CRPence <CRPbottle@xxxxxxxxx>]

  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".