Обсуждение: PGSQL Locking vs. Oracle's MVCC

Поиск
Список
Период
Сортировка

PGSQL Locking vs. Oracle's MVCC

От
RPK
Дата:
How is PGSQL Locking compared with Oracle's MVCC? How PGSQL handles
concurreny and how it differs with Oracle's Multi-Version Concurrency
Control (MVCC)?
--
View this message in context: http://www.nabble.com/PGSQL-Locking-vs.-Oracle%27s-MVCC-tf3277425.html#a9114584
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: PGSQL Locking vs. Oracle's MVCC

От
Richard Huxton
Дата:
RPK wrote:
> How is PGSQL Locking compared with Oracle's MVCC? How PGSQL handles
> concurreny and how it differs with Oracle's Multi-Version Concurrency
> Control (MVCC)?

The manuals are good for this type of thing:
   http://www.postgresql.org/docs/8.2/static/mvcc.html

--
   Richard Huxton
   Archonet Ltd

Re: PGSQL Locking vs. Oracle's MVCC

От
"Albe Laurenz"
Дата:
> How is PGSQL Locking compared with Oracle's MVCC? How PGSQL handles
> concurreny and how it differs with Oracle's Multi-Version Concurrency
> Control (MVCC)?

In PostgreSQL, old rows remain in the table until the table is vacuumed.
In Oracle, old rows are kept in the 'undo table space' until - well,
until
the undo table space runs out and they are recycled. Depends.
I have never heard this referred to as 'MVCC'.

Locking and concurrency work pretty similar in both - at least as far
as the behaviour is concerned.

Yours,
Laurenz Albe

Re: PGSQL Locking vs. Oracle's MVCC

От
Tomas Vondra
Дата:
> How is PGSQL Locking compared with Oracle's MVCC? How PGSQL handles
> concurreny and how it differs with Oracle's Multi-Version Concurrency
> Control (MVCC)?
>
Well, I'm currently working on this comparison as we will need to port
and support some currently
Postgresql-only applications on several other rdbms in the near future,
and Oracle is among them.
I still don't have a final report, but from the point of a developer the
databases are almost the same,
especially in the field of locking, i.e.

   - writer never blocks reader (and vice versa, the only exception is
distributed transaction in-doubt)
   - there is 'SELECT ... FOR UPDATE' if you need to lock
   - there are some 'application locks' (advisory locks in PostgreSQL)
   - all the queries are consistent with respect to the beginning of the
query
   - the default transaction level is READ COMMITED, it's possible to
use SERIALIZABLE

Sure, there are many differences when it comes to internals (Albe
Laurenz already pointed out the
most obvious one), as well as the Oracle is superior in many areas
(partitioning, some features in
PL/SQL, etc.).

This generally means that if you have an application architecture for
PostgreSQL, then it will usually
work fine Oracle. If the constraint is correctly enforced in PostgreSQL,
then it will be enforced in
Oracle etc.

There is no exact definition of MVCC, especially when it comes to
implementation - there are many
ways to do that, PostgreSQL uses one of them, Oracle uses another one.
MVCC generally means
that the DB is able to serve various versions of the same row (block).
PostgreSQL does not overwrite
the updated rows, Oracle uses undo log.

Tomas

Re: PGSQL Locking vs. Oracle's MVCC

От
"Merlin Moncure"
Дата:
On 2/23/07, Tomas Vondra <tv@fuzzy.cz> wrote:
> Sure, there are many differences when it comes to internals (Albe
> Laurenz already pointed out the
> most obvious one), as well as the Oracle is superior in many areas
> (partitioning, some features in
> PL/SQL, etc.).
>
> This generally means that if you have an application architecture for
> PostgreSQL, then it will usually
> work fine Oracle. If the constraint is correctly enforced in PostgreSQL,
> then it will be enforced in
> Oracle etc.


be careful with that statement....some of the internals are better and
some are worse.  all postgresql ddl is transactional for example.  how
easily the code ports is going to depend on how tweaky the developers
were...it's very easy to fall in love with postgresql-specific
features and write unportable code.

merlin

Re: PGSQL Locking vs. Oracle's MVCC

От
"Joshua D. Drake"
Дата:
RPK wrote:
> How is PGSQL Locking compared with Oracle's MVCC? How PGSQL handles
> concurreny and how it differs with Oracle's Multi-Version Concurrency
> Control (MVCC)?

PostgreSQL uses MVCC.

http://www.postgresql.org/docs/8.2/static/mvcc.html


Joshua D. Drake



--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/