Re: Serializable transactions get rolled back while there are no othertransactions running?!

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> <ctcgag_at_hotmail.com> wrote in message
> news:20040620152317.432$RM_at_newsreader.com...
> >
> > > I think that's what most people would expect of serializable.
> >
> > Not I. I would expect one of the two to get a "cannot serialize"
> > error. The isolation level that Oracle calls "serializable" is not
> > truly serializable. I'm not saying that that level isn't useful, nor
> > am I
> saying
> > that it could be made truly serializable without incurring significant
> > performance penalties. I'm just saying they probably shouldn't have
> > named it "serializable" when it really isn't.
> >
>
> I sympathise with the view-point. Life gets difficult when
> words are given a specific meaning for a technical context
> that doesn't match the 'intuitive' meaning outside that context.
>
> At the time, of course, 'serializable' was described twice in
> the SQL standard: and one of the descriptions was a list of
> the phenomena that qualify a serializable transactions -
> one of which was "repeatable reads". So Oracle put three
> ticks against the list, and said "that's it - we comply to that
> feature".

I would consider the one about "transactions that overlap in time have the same effect as if they had not overlapped in time" to be the definition of the term, and the list of dirty, nonrepeatable, and phantom just to be a (non-exhaustive) list of the ways nonserializability could present itself.

I never cared for the way the list was made, anyway. It seems to focus too much on whether a transaction could detect that it was not isolated solely from within itself, whereas I always ask if a fly-on-the-wall could detect the failure of isolation.

>
> I must read the latest standard some time to see what it
> says on the topic.
>
> Your comment leads to an interesting philosophical point -
> I execute a serializable transaction, but do NOT
> revisit row X, which gets changed and committed
> by another user in the interim.
>
> I execute the same serializable transaction, but DO
> revisit row X, which gets changed and committed
> by another user in the interim. However, there is
> nothing in my transaction that I do differently, whether
> or not I can see the change (which I couldn't in Oracle
> of course).
>
> Should either, neither, or both of those transaction fail
> according to your concept of serializable ?

In both cases it should fail (under serializable mode), in my opinion. Your transaction must depend on the value that was read the first time (otherwise, why bother reading it?) and thus we have to assume that the fact that it has been changed would have changed the outcome of the transaction. Now, that is only a problem if the changed outcome of the this transaction would have changed the outcome of the other transaction (the one that already committed), but I see no way in principle to know whether it could have or not.

>
> I think this may qualify as the "Schrödinger's Cat" of the
> serializable transaction.
>
> (Side-note: my spell-checker wants to change the American
> spelling of serializable into "fertilizable" - does this mean the
> entire discussion is a load of horse-manure ?)

On the contrary, maybe it means this discussion will be productive? :)

I wonder what your spell checker thinks of serializability?

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB