Thread: Transactional support across multiple machines...

Prepared transactions:
http://www.postgresql.org/docs/9.0/static/sql-prepare-transaction.html
---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com

On 17.11.2010 20:40, Maciek Sakrejda wrote:
> Prepared transactions:
> http://www.postgresql.org/docs/9.0/static/sql-prepare-transaction.html

Yeah, two-phase commit is the usual solution. But you shouldn't usually
try to implement that yourself, you should pick an existing Transaction
Manager. Java application servers usually include one, so if you're
using an application server, just configure your application to use XA
connections and let the app server handle the two-phase commit for you.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Once the transactions are prepared, they are serialized to disk and
you will be able to go back and commit them later even if you have a
database crash (assuming safe postgresql.conf settings). All
distributed commit mechanisms will be built on something like this
under the hood--there is no magical "commit everywhere atomically"
functionality (perhaps with quantum computing ;) ). But Heikki is
right--you probably want to go through higher-level APIs like JTA.
Just make sure you understand the recovery guarantees if you fail in
the middle of, e.g., a JTA commit, which could translate into some
committed prepared transactions and some uncommitted ones, or some
rolled back and some prepared.

Everything you need to either commit or roll back (depending on when
the failure occurred) the full set of transactions will be there on
disk, but I don't know whether the JTA implementation will do that for
you. And you definitely don't want prepared transactions sitting
around on disk indefinitely.
---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com