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
