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.