Re: best practice for web client updates to multiple tables

On 07-Jan-2014 11:48 -0800, Matt Olson wrote:

rob@xxxxxxxxx on Tuesday, January 07, 2014 1:28 PM wrote:

<<SNIP>>

Implications include:
- Abdul updates the item balance on item X to 500 units from 417
units.
- Mohammed updates the item balance on item X to 495 units. (Sold
some perhaps.)
- Abdul issues a ROLLBACK.
What is the balance on item X?

Mohammed is blocked from doing his update until Abdul is done with
his transaction.

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 is an example of why when going from no commitment control to using CmtCtl in just one application can be problematic for the data integrity. That is, everyone needs to play by the ground rules [all applications need to use CmtCtl], or any not using CmtCtl must clearly understand the implications, and thus never perform work which might effect _update_ activity based on /dirty data/ from a /dirty read/. Applications that coded knowingly for optimistic locking compare the row to ensure against this type of problem.... but most code written without any CmtCtl typically /assumes/ the only thing to worry about is a locked row; i.e. not that the locked row also will have dirty changes. The assumption holds true if all update activity is via RLA and *without* commitment control because the updater is either holding the lock pending their change which is [instead of being visible\dirty] is invisible to the other applications because that data is merely in the program's buffer, and the update is atomic irrespective the use of CmtCtl.

Now if Mohammed's program had used an SQL UPDATE SET UNITS=UNITS-5 [even having coded WITH NC], the SQL waits on the record lock before the calculation [pessimistic locking], so the read is not technically a /dirty read/ because the data to evaluate the expression comes from the row itself. Similarly if that program had done a read-with-lock, the dirty row would not have been able to be viewed, because it was locked by the job pending its COMMIT or ROLLBACK [the latter, in the example].