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

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;

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.

> >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

> Ed Avis <ed_at_membled.com>
Received on Sat Jan 24 2004 - 11:16:16 CST