Recently discussion of distributed transactions came up in the context
of the 4Suite repository redesign discussions. We will probably need to
move to XA-style two-phase
commits (2PC). The problem is that Oracle supports 2PC, but not so many
other back ends with 4Suite drivers. Luckily I've found that my
favorite DBMS will soon support 2PC. The patch is in for
PostgreSQL and became available in the
8.1beta4 release. It should be in production for PG 8.1 (currently in
release candidate). Thanks
to Tom, Heikki and Alvaro for this wonderful addition. For some
interesting discussion of the 2PC patch, see this important
thread. One of the well-known issues brought up is that 2PC in almost all implementations is much slower than straight commit. I think we'll have to make our transaction manager smart enough to use direct commit if it detects that all resources are in the same database instance. I've written transaction managers before (specifically in implementing CORBA CosTransactions in Python on top of OmniORB) and I remember such tricks all too well.
Even once PG 8.1 is available we'll still have to deal with non XA-aware
back ends. All we can do is fall back to the Last Resource Commit
technique in the transaction manager. I found a useful discussion of
this technique in "What to do if your database doesn't support XA
transactions?", by Dmitri Maximovich. You actually risk losing both atomicity and isolation with this technique, not just atomicity, as Dmitri says, but if you choose minimally competent back ends, such failures should be very unlikely.
[Uche Ogbuji]