RE: Record locking (was: WRKOBJLCK automated API ?)

I know the problem, Buck.  Remember, I was the Manager of Architecture over
at System Software Associates, so we dealt with this issue constantly.
Those of you running code from 1985, if it's from SSA, may well be running
code that I wrote.  That is, in fact, why the AS/SET product was designed
with image checking as part of the base generated code.  Record locking
really has no place in today's design, unless you're really lazy.  If your
busy flag is by job ID, you can easily clean up the record locks when a job
terminates, normally or abnormally, in a number of ways.  It's work, but
that's our job as programmers, to make the system solid, not to put out the
largest amount of code possible regardless of architectural integrity.

Your scenario (which we called "he who hits enter last wins") has a number
of programmatic outs.  By far the most elegant is to redisplay the changed
data AND the user changes, but that's a lot of programming.  However, in all
cases, you need to address the fact that a user walking away from their desk
with an editable screen of data is a BAD idea.  If you encourage this
behavior, you deserve all the consequences.

Finally, the ODBC access issue is one which I find to be the primary reason
NOT to allow ODBC access.  ODBC should NEVER be used to update data, period
end of paragraph.  Anything you can do with ODBC can be done with a call to
a server program or an SQL stored procedure.  ODBC update of data is a VERY
bad programming model, and nobody has ever been able to convince me that
there was a single reason to use it other than it "was easier".  Which is a
darned poor reason.

I especially have a problem with the phrase, "Do you add those business
rules to every client who wants to update your database?".  The answer to
this is an emphatic NO!  But that's because there should be NO business
rules on ANY client!  Ever!  This is simply the worst architectural design
possible, and has no place in today's systems.  As we design more and more
complex systems, we must ensure that all database integrity (i.e. business
rules) reside in and are adminstrated by a central authority.  Moving
business rules to the client opens up the distinct possibility that two
clients will have different versions of the business rules, thereby
destroying the integrity of the database.

There is no architecture in which that is an acceptable solution.

Joe



> -----Original Message-----
> From: owner-midrange-l@midrange.com
> [mailto:owner-midrange-l@midrange.com]On Behalf Of Buck Calabro
> Sent: Tuesday, June 05, 2001 1:18 PM
> To: MIDRANGE-L@midrange.com
> Subject: Record locking (was: WRKOBJLCK automated API ?)
>
>
> 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

+---
| 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
+---