Re: computational model of transactions

Erwin wrote:
> > A slight correction here, Marshall.
> >
> > I believe what Erwin is describing is a situation where there are multiple
> > transactions running concurrently, and some of them are read-write while
> > others are read-only. MVCC can be used to isolate the read only
> > transactions from the updaters, while some other mechanism (locking?) can
> > be used to protect the updaters from each other.
> >
> > I may be reading the above into what Erwin wrote, because that's what I'm
> > familiar with: a DB server where the reader can be fed consistent data from
> > superceded versions of data, while updaters queue up for certain resources.
>
> That's indeed what I meant. The MVCC read philosophy is good for the
> purpose of guaranteeing that any read-only transaction can always
> complete without having to wait for some pending update to commit. It
> becomes problematic when several update transactions come at the stage
> of constraint checking : then certain reads in other parts of the
> database must be done for evaluating the constraint expressions, but it
> is unsafe to use the MVCC read philiosophy for those reads.

Why is it unsafe ? Assuming that a multiversion scheduler admits only serializable schedules (not necessarily serial), there should be no problems with integrity constraints.

>
> I posted the following example already to Marshall in private, thinking
> it was not all that groundbreaking or even interesting, but here goes
> again in public :
>
> Transaction T1 updates database object O1 from value O1V1 to value
> O1V2.
> Transaction T2 updates database object O2 from value O2V1 to value
> O2V2.
> There is some database constraint DC involving both O1 and O2.
> It happens to be the case that all of the value combinations
> (O1V1,O2V1), (O1V2,O2V1) and (O1V1,O2V2) are valid, but (O1V2,O2V2) is
> invalid.
> When any of these transactions come at the point of constraint
> checking, they must do some read to evaluate (the expression defining)
> DC. What value does T1 get when reading O2, if the MVCC read
> philosophy is applied ? What value does T2 get when reading O1, if the
> MVCC read philosophy is applied ? What value does the database have if
> both T1 and T2 pass the constraint checking, believing everything is ok
> ?

Assuming a multiversion timestamp ordering scheduler, the outcome (which is guaranteed to be correct regardless of the execution history) really depends on timing. For example, T1: read1(O1); read1(O2) to check for possible constraint violation in anticipation of the possible write; write1(O1). T2: read2(O2); read2(O1);write2(O2).

If the execution history is like this: read1(O1); read2(O2);read1(O2);write1(O1);read2(O1);write2(O2), the last write will 'fail' due to a constraint violation (O1V2, O2V2) determined by read2(O2) and read2(O1).

If the execution history is like this: read1(O1); read1(O2); read2(O2); read2(O1); write1(O1);write2(O2), the first transaction will abort due to serializability violation (write1(O1)) and will be restarted with a new timestamp and the second transaction will complete assuming that (O1V1, O2V2) is acceptable.

Now, Oracle does not implement true SERIALIZABLE, but instead offers other means (SELECT FOR UPDATE) to ensure correctness by reducing some histories' concurrency which might have been higher under an alternative MV scheduler at the expense of possible restarts. However,  due to multiversioning, other non-conflicting transactions will have the same degree of concurrency because their reads (SELECTS) won't be affected by SELECT FOR UPDATE. Received on Tue Aug 08 2006 - 08:34:43 CDT