Re: best practice for web client updates to multiple tables

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.