Mmmm, good question. MVCC blocks reading processes when data is modified. using autocommit implies that each modification statement is an atomic operation.
on a massive readonly table, where no data is altered, MVCC shouldn't have any effect (but this is only an assumption) basing on http://en.wikipedia.org/wiki/Mvcc using rowlevel locks with write access should make most of the mostly available to reading-only sessions, but this is an assumption only, too. maybe the community knows a little more ;-) regards, marcus -----Ursprüngliche Nachricht----- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Auftrag von Grega Bremec Gesendet: Dienstag, 20. Dezember 2005 12:41 An: PostgreSQL Performance List Betreff: Re: [PERFORM] Read only transactions - Commit or Rollback -----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 Nörder-Tuitje wrote: |> We have a database containing PostGIS MAP data, it is accessed |> mainly via JDBC. There are multiple simultaneous read-only |> connections taken from the JBoss connection pooling, and there |> usually are no active writers. We use connection.setReadOnly(true). |> |> Now my question is what is best performance-wise, if it does make |> any difference at all: |> |> Having autocommit on or off? (I presume "off") |> |> Using commit or rollback? |> |> Committing / rolling back occasionally (e. G. when returning the |> connection to the pool) or not at all (until the pool closes the |> connection)? |> | afaik, this should be completely neglectable. | | starting a transaction implies write access. if there is none, You do | not need to think about transactions, because there are none. | | postgres needs to schedule the writing transactions with the reading | ones, anyway. | | But I am not that performance profession anyway ;-) Hello, Marcus, Nörder, list. What about isolation? For several dependent calculations, MVCC doesn't happen a bit with autocommit turned on, right? Cheers, - -- ~ Grega Bremec ~ gregab at p0f dot net -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (GNU/Linux) iD8DBQFDp+2afu4IwuB3+XoRA6j3AJ0Ri0/NrJtHg4xBNcFsVFFW0XvCoQCfereo aX6ThZIlPL0RhETJK9IcqtU= =xalw -----END PGP SIGNATURE----- ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
