On 07-Jan-2014 20:29 -0800, Ken Sims wrote:
On Tue, 07 Jan 2014 13:39:23 -0800, CRPence wrote:
True, but that ignores the subtle\unstated implication that
Mohammed's _read_ was per use of effective WITH NC [no isolation],
thus effecting a /dirty read/ of Abdul's update using isolation. As
such, although Mohammed's request to effect the change was
_momentarily_ /blocked/ awaiting the record lock on ITEM='X' [per
WAITRCD() setting], still he would have been able to set the value
of UNITS=UnitsRead-5 within the program, to reflect that he had
sold five units, thus reducing the total number of units that was
just read into his program; i.e. 500=>(500-5)=>495. Then his update
operation completes [with data set from the buffer; i.e. UNITS=495]
after the ROLLBACK releases the lock; of course, if not exceeding
the wait-time for the record lock. So unfortunately for their
failure to both participate with isolation to prevent dirty reads,
the number of units of ITEM='X' henceforth would reflect a value of
83 more than actually exist... perhaps arising from Abdul realizing
that the shipment of new items were not ITEM='X' irrespective of
markings, and having since decided they will be returned to the
manufacturer, he rolls back his change [just before Mohammed made
and updated the effect of his sale].
This isn't specific to commitment control. The same thing can happen
if Mohammed reads a record with no lock and some other program
actually does an update of the record before Mohammed reads the
record again for update.
Correct. I had suggested that the typical code running without
commitment control active in their environment /assumes/ only record
locks need to be handled. For that coding, their /dirty reads/ will not
come from changes performed under isolation, but from changes that were
since atomically performed [without first being visible pending change
under cmtctl]. As pending changes without CmtCtl, they are invisible,
because they are only within the program rather than manifest within the
row. A new application added that implements CmtCtl in that environment
potentially\likely exacerbates the opportunity to see the dirty data
from the no-commit application.
The scenario as presented by Rob had Abdul using CmtCtl, but Mohammed
could have been doing anything, although his read was implied to be from
the change performed under isolation; i.e. a dirty-read of a pending
change. Any example of what Mohammed could do with that to cause a data
integrity issue with regard to the number of units for item='X' is
noteworthy; i.e. as an answer to "What is the balance on item X?", that
would be perceived as an undesirable effect.
If a program reads a record without a lock with the intent of doing
an update to that record later, it's the responsibility of the
program to handle any intervening changes to the record image,
regardless of how said changes occurred.
Agreed. Coded as optimistic-locking, thus with the validation that
the since-locked row was not changed [from the image obtained without a
lock], ensures the problem does not occur.
But if performed in planned "isolation to prevent dirty reads", the
reader implicitly operates with pessimistic locking; i.e. they are
prevented from reading the record, because it is locked in work that
conflicts with the ability to read, inherently preventing the problem.
Yet when coded neither as pessimistic-locking nor as
optimistic-locking whereby the code runs with no-commit and just
/assumes/ the update is correct, the larger window of visibility of
pending changes in isolation gives increased opportunity for a
dirty-read; i.e. both the visibility to the pending-change between
update and commit as well as the visibility to any actual-change to the
row data, whereas if the other code were without isolation, only the
latter is a potential issue.