Re: MVCC performance issue

Craig Ringer wrote:
> It sounds like you're describing Oracle-style MVCC, using redo logs.
>
> http://blogs.sybase.com/database/2009/04/mvcc-dispelling-some-oracle-fudunderstanding-the-cost/
>
>
Craig, this is an interesting blog page, making some valid points about
the multiversioning vs. locking. The ATM example, however, is
unrealistic and couldn't have happened the way the author describes.
Oracle has the same write consistency mechanism as Postgres and it
restarts the transaction if the transaction blocks were updated while
the transaction was waiting. In other words, the wife's transaction
would have been restarted before committing, the transaction would get
the balance accurately and there wouldn't be a loss of $250.
Such an example is naive, sheer FUD. If that was the case, no bank in
the whole wide world would be using Oracle, and many of them do, I dare
say many more are using Oracle than Sybase. That means that they're not
losing money if 2 spouses decide to withdraw money from the joint
account simultaneously. Given the number of people in the world, I
imagine that to be a rather common and ordinary situation for the banks.
The example is plain silly. Here is what I have in mind as "write
consistency":
http://www.postgresql.org/docs/9.0/static/transaction-iso.html#XACT-READ-COMMITTED:
" If the first updater rolls back, then its effects are negated and the
second updater can proceed with updating the originally found row. If
the first updater commits, the second updater will ignore the row if the
first updater deleted it, otherwise it will attempt to apply its
operation to the updated version of the row. The search condition of the
command (the WHERE clause) is re-evaluated to see if the updated version
of the row still matches the search condition."

Essentially the same behavior is described here, for Oracle:
http://tkyte.blogspot.com/2005/08/something-different-part-i-of-iii.html
"Obviously, we cannot modify an old version of a block—when we go to
modify a row, we must modify the current version of that block.
Additionally, Oracle cannot just simply skip this row, as that would be
an inconsistent read and unpredictable. What we’ll discover is that in
such cases, Oracle will restart the write modification from scratch."

Postgres re-evaluates the where condition, Oracle restarts the entire
transaction, but neither MVCC mechanism would allow for the silly ATM
example described in the blog. Both databases would have noticed change
in the balance, both databases would have ended with the proper balance
in the account.

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com