VC wrote:
> Hello Ed,
>
> "Ed Prochak" <ed.prochak_at_magicinterface.com> wrote in message
> news:RYfSb.45$rK6.32_at_fe02.usenetserver.com...
>
>>VC wrote:
>>
>>>Hello Ed,
>>>
>>>"Ed Avis" <ed_at_membled.com> wrote in message
>>>news:l1isj1l2ym.fsf_at_budvar.future-i.net...
>>>
>>>
>>>>"Heikki Tuuri" <Heikki.Tuuri_at_innodb.com> writes:
>>>>Is there an example of plain SQL (not using 'if' or other PL/SQL
>>>>constructs) which is not serializable in SERIALIZABLE mode?
>>>
>>>
>>>It's a rather well-known fact that Oracle's SERIALIZABLE IL is a
>
> misnomer.
>
>>>What Oracle calls SERIALIZABLE is, in fact, the SNAPSHOT isolation
>
> level.
>
>>>As such, it exhibits, for example, the write-skew anomaly:
>>>
>>>Let's assume a bank has two linked accounts for married couple with a
>>>constarint X+Y >0 (the total balance should be positive).
>>>
>>>Further, X0 = 70 and Y0 = 80.
>>>
>>>Transaction T1 running in the SIL subtracts 100 from X: update x set
>>>amount=amount - 100;
>>>Transaction T2 running in the SIL subtracts 120 from Y (because it sees
>>>X0+Y0 = 150): update y set amount = amount - 120;
>>>Both transactions commit and the constarint X+Y > 0 is violated;
>>
>>I'd be curious how this constraint is implemented, can a constraint refer
>
> to
>
>>two tables?
>
>
> Please re-read the thread. Oracle does not currently support database-level
> constraints(assertions). There is a round-about way to describe such a
> constraint via a checked view (see my response to Lee Fesperman) but Oracle
> silently ignores it. I used a condition in the update statement in another
> version of my bank example in order to emultae such a constraint.
>
>
>>And by "is not serializable" I take it you mean that C2, the second
>
> commit,
>
>>completes without returning an error.
>
>
> Informal definitions: When two or more transactions are executing
> concurrently, the relative sequence of the read and write operations in the
> two transactions constitutes a SCHEDULE. The SERIAL schedule means
> executing one transaction in its entirety, then the other, etc. A schedule
> is SERIALIZABLE if its effect is logically equivalent to some SERIAL
> schedule. Since, in my bank example, the concurrent schedule is not
> equivalent to any SERIAL execution of T1 and T2, it's not SERILAIZABLE
> (although it was executed in the Oracle "SERILIZABLE" IL.
>
> The issue of constraint implementation is secondary in my test.. What it
> demonstrated was that a schedule implemented using just SQL (as one poster
> requested) is not SERIALIZABLE in Oracles's SIL.
>
>
> Rgds.
>
> VC
>
>
Thanks. I did post before reading other part of the discussion. I'll rejoin
where the current action is.
--
Ed Prochak
running http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost
Received on Fri Jan 30 2004 - 06:35:10 CST
