Updating an existing row in a database table provides more opportunity for
concurrency and consistency errors than inserting a new row. Use techniques shown in this
section to help minimize the errors.
Using a work record
A work record and a local record, both identical to the program record, are defined to allow the program to compare the values.
SELECT ... FOR UPDATE
To explicitly lock a database row prior to updating, a SELECT...FOR UPDATE statement may be used to instruct the database server to lock the row that was selected. SELECT ... FOR UPDATE cannot be used outside of an explicit transaction. The locks are held until the end of the transaction.
SCROLL CURSOR WITH HOLD
Like many programs that perform database maintenance, the Query program uses a SCROLL CURSOR to move through an SQL result set, updating or deleting the rows as needed. BDL cursors are automatically closed by the database interface when a COMMIT WORK or ROLLBACK WORK statement is performed. To allow the user to continue to scroll through the result set, the SCROLL CURSOR can be declared WITH HOLD, keeping it open across multiple transactions.