Skip site navigation (1) Skip section navigation (2)

Re: MVCC performance issue

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Cc: Kyriacos Kyriacou <kyriacosk(at)prime-tel(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: MVCC performance issue
Date: 2010-11-13 18:38:50
Message-ID: 4CDEDB3A.2070606@vmsinfo.com (view raw or flat)
Thread:
Lists: pgsql-performance
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 


In response to

Responses

pgsql-performance by date

Next:From: Craig RingerDate: 2010-11-14 00:10:56
Subject: Re: MVCC performance issue
Previous:From: Tom LaneDate: 2010-11-13 18:01:51
Subject: Re: Defaulting wal_sync_method to fdatasync on Linux for 9.1?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group