If you are interested in distributed transactions checkout the talk Demystifying Java Technology Transaction Processing by Jon Maron, Mark Little and Greg Pavlik. The talk was given at JavaOne 2004 and it available online for the Sun Developer Network members (you can be a member with a free registration).

Mike Spille has also written a series of excellent articles on his blog about XA transactions, that provide many details on how distributed transactions work in a J2EE environment. I really like the information on failures and failure recovery and I believe that every J2EE developer should know this stuff.

From the open source database servers, if I am correct, only MySQL 5 and Apache Derby support the XA protocol, so that they can be enlisted in a 2PC transaction, and unfortunately MySQL has some very serious limitations.