Re: user record updates via a cobol program: how to deal with record locks and data changes

On Aug 5, 2016, at 3:03 PM, Mark S Waterbury <mark.s.waterbury@xxxxxxxxxxxxx> wrote:

Joel:
*
*Do a google search for "os/400 avoiding record locks in cobol" (without quotes) and you should find some good info.

Also, a google search for "os/400 lock-free r ecord i/o in rpg" will give you some good web pages... even though talking about RPG, many of the same techniques can be used in COBOL.

Here are a few ideas for how you can avoid having to hold record locks for long periods of time in your interactive applications:

_METHOD A -- checksums or hash code_

1. when you first read the record, (with no lock), compute a checksum
or "hash" of the contents of the entire record buffer.
2. present the data on-screen for the user to (possibly) update.
3. when the user has updated some fields, re-read the original
database record again, with record lock this time, into another
buffer and re-compute the check-sum or hash code. If the hash or
checksum is still t he same then no one else has updated the record
in the meantime, so it is now "safe" to update that record with
the changes made by the user. (The update will release the record
lock automatically.)
4. if the check-sum or hash does not "match" you need to release the record lock and inform the user that another person has updtaed the same record -- present the (newly updated) data again, and ask
the user to re-enter the changes. (... lather, rinse, repeat ...
as needed until the update is successful or the user presses
F12=Cancel ...)

_METHOD B -- a similar but simpler approach_

A simpler, low-cost variation on the above, relies on adding a single "UPDATEID" field to each file you want to update "lock-free" -- it can be a binary integer or a packed numeric field, if binary, you can use a 2-byte or 4-byte integer, or packed with at least 5 or more digits. Proceed as follows:

* whenever a record is first added to the file, initialize this
UPDATEID field to zero. (Could be handled by an INSERT trigger)
* whenever any application program updates any record in this file,
you must ensure that the UPDATEID value is always incremented by 1

The algorithm for updating is similar to the above, with these changes. Instead of computing a checksum, you just remember theUPDATEID value ... and compare it , as follows:

1. when you first read the record, (with no lock), save the UPDATEID
value for use later.
2. present the data on-screen for the user to possibly update.
3. when the user has updated some fields, re-read the original record
again, with a record lock this time, into another buffer and If the
UPDATEID is still t he same as the saved value, then no one else
has updated the record in the meantime, so it is now "safe" to
update that record with the changes made by the user. Increment the
value of UPDATEID in the record buffer about to be updated (This
update I/O will also release the record lock automatically.) (An
UPDATE trigger could handle incrementing the UPDATEID field.)
4. if the UPDATEID does not "match" you need to release the record lock
and inform the user, that the record has been updated by someone
else, and present the (newly updated) data again, and ask the user
to re-enter any changes. (... lather, rinse, repeat ... as needed
until the update is successful or the user presses F12=Cancel ...)

The main "down side" to METHOD B is that _everyone must follow these rules_ to ensure that the UPDATEID is always incremented whenever a record is updated. (The "increment" logic could be put in a trigger to make that part easier.) But, you must still add logic in your applications to save the UPDATEID on the first READ and compare it again, just prior to any update ... -- if anyone forgets orignores these rules, your users will suffer "lost changes" like what happens if you use no record locking at all.

Hope that helps,

Mark S.Waterbury

On 8/5/2016 12:45 PM, Stone, Joel wrote:
What is a good way to handle record locks when a user changes a DB record via a cobol pgm?

Also how to avoid one user's changes from overlaying another user's recent changes.

Is the best way to read with no lock, wait until user enters data into screen, then re-read with a record lock?

And only update the record if there were no changes between the two reads by comparing the entire record strings?


Any other methods that work well?

Maybe read the journal to ensure no one else has changed that particular RRN during the users input?

Thanks!

Joel

On 8/5/2016 12:45 PM, Stone, Joel wrote:
What is a good way to handle record locks when a user changes a DB record via a cobol pgm?

Also how to avoid one user's changes from overlaying another user's recent changes.

Is the best way to read with no lock, wait until user enters data into screen, then re-read with a record lock?

And only update the record if there were no changes between the two reads by comparing the entire record strings?


Any other methods that work well?

Maybe read the journal to ensure no one else has changed that particular RRN during the users input?

Thanks!

Joel

--
This is the COBOL Programming on the IBM i (AS/400 and iSeries) (COBOL400-L) mailing list
To post a message email: COBOL400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/cobol400-l
or email: COBOL400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/cobol400-l.