Thread: PGSQL Locking vs. Oracle's MVCC
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.
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
> 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
> 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
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
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/