On 19-Dec-2013 10:41 -0800, Henrik Rützou wrote:
Dieter
First of all you are outnumbered since 99% of RPGLE code still uses
RLA.
Yet Row Level Access (RLA) can use CmtCtl just as does the SQL. The
Commitment Control was available for RLA before there ever was even an
SQL available on the platform [although there was a query engine and
much SQL-like capability, aside from the language]; the latter using the
term Isolation Level, implemented with the same Commitment Control that
was available with RLA.
Besides that RLA (cleverly done) can do stuff that COMMIT never can
do since you can lock a record while doing some processing before you
update it and if you have a set of transactions you can lock their
master record while the transactions are being processed.
Thereby RLA doesn’t need any ROLLBACK functionality since it by the
master record can own a transaction set during an update.
Holding out activity on a /master record/ presumes that every bit of
code respects the rules or plays nicely; i.e. there are no rogue updates
nor any accidents\bugs. This is little different than having only some
programs using Commitment Control, such that some programs are not
following the same ground-rules with regard to data access. In either
case, all can function quite well, if the programs for their data
accesses are robust; mostly, with regard to handling record locks.
Regardless, that technique by itself, is no assist to prevent a
partial update by the program implementing it. Although holding the
master record, supposedly preventing others from doing any update
activity to the relations to that row, if an update occurs to seven of
twelve records that require updates to complete the transaction, but the
program crashes at that point, then the transaction is a SNAFU; i.e.
seven permanent changes to the data, five permanent changes not made to
the data, and no built-in ROLLBACK capability to /undo/ the mess. With
that program using CmtCtl, the termination of the program need only
effect [and it may even be implicit] ROLLBACK to automatically back-out
the seven changes so the net effect is zero changes; i.e. zero changes
is a reset to the same data prior to the program, which is typically the
more desirable result than a partial change.... even if there are only
five more rows that need to be updated to move forward, stepping back is
still more likely to be preferable.
While exit-handlers could be used to effect the same as what the
ROLLBACK would [except in system crashes or forced\abnormal job
terminations], there would be great difficulty in arguing against the
ease of using CmtCtl to do so instead. And the journaling and
commitment control should even prevent partial updates in those
crash-induced failures for which a cancel-handler\termination-handler
might not be able to run... thus the typical [programmed] /cleanup/
activity from a prior SNAFU, for which the CmtCtl would avoid
requirement to create some of those /cleanup/ features -- obviously no
help from bugs putting incorrect data into files, but the journaling as
a pre-requisite to CmtCtl provides yet another form of relief.
What neither RLA nor COMMIT handles is what I call data in transit,
that is, redundant data that resides in one or several buffers or in
browsers where you have to insure that the record/row hasn’t been
updated by others in the meantime if a user tries to update the
record/row.
Yet if designed using a database protocol with Isolation support,
then the Commitment Control does indeed protect that record from anyone
else running under isolation, as well as protecting the row from updates
by anyone running without isolation. That is true for both RLA and SQL,
but there are more likely to be SQL interfaces [e.g. ODBC, JDBC, DRDA]
with a database protocol available to clients [than RLA interfaces; e.g.
DDM].
The most cost effective way to insure that is to have a record
update counter that is passed with the redundant data and then
checked against the actual value of the record/row before the
update.
That is a worthwhile technique irrespective of isolation, because
that technique enables limiting the potential for conflicts in locking
the same row. Some applications will benefit, others not so much. The
term used to describe this is /optimistic/ vs /pessimistic/ because the
operating assumption is that most likely nobody else will have changed
the row, so when that row is re-retrieved with locking, there is no
conflicts with the data to be resolved. Some applications are
inherently more appropriate for pessimistic design due to an inherent
lack of conflicts with the same [key] row data.
And don’t try to argue against that technique, I have never
experienced this technique to fail in more than 100 systems in 30
years! And BTW it works both on RLA and SQL.
And CmtCtl works fine too. I will choose CmtCtl when it fits, rather
than trying to figure out how to handle data conflicts when they occur;
i.e. handle when somebody else changed the row I wanted to update. If I
may allow them to be prevented from changing the row, that is IMO the
better choice. The /coffee break/ scenario is often the argument
against pessimistic locking, but [especially from clients] the option
exists to cancel the request due to a timeout, and per isolation any
partial transaction is merely rolled back rather than being left
partially complete such that the data is left effectively corrupted.
The bottom line is that RPGLE based systems has both techniques,
RLA gives you much more control than SQL so why not use the best
technique available for the job?
The same techniques are used in the SQL too. SELECT using a WITH NC
clause while isolation is in effect, allows reading data without
obtaining a lock, thus enables coding /optimistic/ just as described can
be done with RLA. There is even a SKIPPED LOCKED DATA clause to avoid
waiting when the application performing under isolation deems
appropriate. A cursor can be used to lock a specific row. In those
regards, there is nothing superior with non-SQL, and I can still use
RPG, though SQLRPGLE vs just RPGLE.
I have nothing against either RLA or SQL. Doing whatever gives the
best benefits with regard to meeting requirements within time
constraints is what matters. That is to say, willingness to set aside
ideology, irrespective of the /camp/ in which one sits. This
conversation seems to have one camp espousing that CmtCtl is mandatory
as the only valid solution and another camp espousing that effectively
there is no need for CmtCtl. I argue that neither camp is plainly
correct... that instead, one must consider that there are good reasons
to choose what is best for any particular situation rather than what
matches some ideology. And unlike what I seem to infer from what the
opposite camps are alluding... Adding CmtCtl is not nearly impossible,
nor is adding CmtCtl always as simple as merely adding COMMIT(*the_lvl)
or the COMIT kwd to the file(s) in a RPG program. Like all programming,
what is done should be by-design and be what has been tested to be
functional in conjunction with the other features of the application...
and according to most management, also be in-budget.
midrange.com
