Record locking (was: WRKOBJLCK automated API ?)

[Buck Calabro <Buck.Calabro@xxxxxxxxxxxx>]

Joe said:

>Why in the world is a record locked for
>anything more than a few milliseconds (the time 
>to read and update the record)?  There are 
>two perfectly good alternate architectures for data
>access - busy flags and image checking - neither 
>of which lock the record for any appreciable 
>length of time, and so never run into the 
>situation you are facing.

Those of us with running code from the 1985 time frame have many programs
written the simple way (i.e. avoid "technically slick" coding): CHAIN for
update, EXFMT, UPDAT.  No rant, just reality.

I'll keep this in the MIDRANGE-L list because the concepts of record locking
go beyond specific RPG code...  

Typical "old" flow:
Customer's A/P manager calls to have her phone number updated
Agent 1 pulls up customer record and locks it
Agent 1 types the changes on the display and hangs up
Agent 1 gets a call from The Boss and goes to her office before updating the
record
Customer's Purchasing manager calls to have his "mail to" address updated
Agent 2 pulls up customer record and bzzzzt.  "Record in use" error.
  Agent 2 takes down customer info, hangs up
  Agent 2 puts request in the "to do" pile and tries again after
lunch/break/tomorrow, etc.
Agent 1 updates customer A/P Contact phone number in customer record

Not pretty.

Alternative 1 - Image compare:
Customer's A/P manager calls to have her phone number updated
Agent 1 pulls up customer record and saves the image
Agent 1 types the changes on the display and hangs up
Agent 1 gets a call from The Boss and goes to her office before updating the
record
Customer's Purchasing manager calls to have his "mail to" address updated
Agent 2 pulls up customer record and saves the image
Agent 2 types changes, updates the record
Agent 1 comes back from the Boss' office and tries to update
  Image compare finds a difference and pops up a window (new) telling the
agent that the record has changed.
  Agent 1 must try to remember what changed on the screen
  Agent 1 presses "refresh" (new) and loses her changes
  Agent 1 types the changes on the display and updates the record

Informing a user of a record lock "after the fact" can be very discouraging,
which brings us to 
Alternative 2 - Busy flags
Customer's A/P manager calls to have her phone number updated
Agent 1 pulls up customer record and sets the "busy" flag
Agent 1 types the changes on the display and hangs up
Agent 1 gets a call from The Boss and goes to her office before updating the
record
Customer's Purchasing manager calls to have his "mail to" address updated
Agent 2 pulls up customer record and sees that the "busy" flag is set
  Agent 2 takes down customer info, hangs up
  Agent 2 puts request in the "to do" pile and tries again after
lunch/break/tomorrow, etc.
Agent 1 comes back from the Boss' office and updates the record, releasing
the "busy" flag

Knowing about the lock in advance is easier on the users, I think.  But what
if there's a Windows problem that kills Agent 1's session and leaves the
busy flag set?  We get to write a "clear the busy flag" program and add it
to the nightly cleanup routine.

The busy flag issue can be avoided by actually locking the record and using
appropriate error handling to detect the error and report it to Agent 2 as
he tries to get the record.  The program status feedback has the complete
message.  Same principle as the busy flag, but no dangling cleanup problems.

Many of these issues evaporate when the database is normalised properly, but
there is an interesting question about how to keep ODBC access out of your
pseudo-locked record (busy flag, image compare).  Do you add those business
rules to every client who wants to update your database?  How do you enforce
THAT?  Stored procedure access restricted by native security?

Are any of these THE right way?  Nope.  Each company and each application
needs to be looked at to see what is the best fit.

I believe we had a long discussion in the OPENERP list about this subject,
and if I recall, MIDRANGE-L has had a few as well.  It is a timeless topic.

Buck Calabro
Commsoft; Albany, NY
"Love truth but pardon error"
  -- Voltaire
Visit the Midrange archives at http://www.midrange.com
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---