Not sure this is True.
In a well-written app, there ARE NO record locks in force during screen I/O.
So when Abdul reads itemX, in a well-written app, it will not lock the record until he submits a change; then the record is re-read and updated only if the two reads match each other.
In a well-written app, after all screen I/O is complete, disk I/O starts and finishes prior to any additional screen I/O. This implies a commit.
Mohammed then does his double reads (a read with no-lock, some screen I/O, then a read with lock) and the update IF the two reads match. And a commit prior to any additional screen i/o.
When Abdul issues a rollback, it doesn't rollback as far back as the question implies. It only rolls back to the prior commit, which was another persons update (which effects no database changes).
Think Ebay or American Airlines. If they locked records over user's screen I/O, planes would stop flying due to frozen databases.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Tuesday, January 07, 2014 3:39 PM
To: midrange-l@xxxxxxxxxxxx
Subject: 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].
midrange.com
