Re: Avoiding any locks in SQL Servers - read and understand....its magic.

From: Ed Prochak <ed.prochak_at_magicinterface.com>
Date: Thu, 29 Jan 2004 18:05:31 -0500
Message-ID: <RYfSb.45$rK6.32@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?

  It's an interesting feature for a bank to provide (I haven't seen it offered).

>
> Formally, the transaction history, R1(X0,70) R2(X0,70) R1(Y0,80) R2(Y0,80)
> W1(X1,-30) C1 W2(Y2,-40) C2,
> is not serializable.

And by "is not serializable" I take it you mean that C2, the second commit, completes without returning an error.

If there was a way to define the constraint, then I'd be surprised that C2 did go through.
>
>

>>>MySQL/InnoDB has a similar MVCC concurrency control method as Oracle
>>>and PostgreSQL. But for MySQL/InnoDB, the SERIALIZABLE level really
>>>is serializable, because InnoDB in that case converts all plain
>>>SELECTs to use next-key locks on index records.
>>
>>An integrity advantage for MySQL over Oracle.  Shows that
>>preconceptions can be wrong :-).

>
>
> If I understand the MYSQL behaviour description correctly, it's hardly an
> advantage because, in fact, all the transactions become SERIAL being
> queued on the single resource. The same behaviour is easily achievable in
> Oracle by each transaction's locking the entire table for write access. I
> doubt many customers would like to sacrifice concurrency in this manner.
>
>
> Rgds.
>
> VC

That would certainly be overkill.

-- 
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 Thu Jan 29 2004 - 17:05:31 CST