Re: equivalent of lock and update in SQL

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