Up to this point, 2PC is something I've only ever heard about. I have never seen it implemented. I've seen it faked many times. My goal is to try and implement an actual 2PC commit, and say how it works.

The standard implementation of that protocol for two phase commits is called DTP, which is a part of a standards package XA, which is run by the X/Open consortium. The Java Transaction API relies on XA behind the scenes. So the first question you should ask is "Does my database support XA?"

After that, you have to make sure you have a JDBC driver for your database. The driver has to implement JDBC 2.0 or later, as XA support wasn't included in the JDBC API before this point. Also note that some drivers support the 2.0 API, but really don't support XA anyway.

I had trouble even finding a database that supported XA. Oracle, Sybase, DB2, Informix support XA, but they're a pain to install and get working. Solid doesn't support XA. The Postgresql JDBC driver says it supports XA, but is really faking it, so that's no good.

In addition, when looking at the support for XA, I came up against some pretty interesting bug lists for Oracle. Check this out. You'd think they'd have worked this out by now...

The only free software solution I've found that supports XA is Firebird. Apparently it's been doing this since 1996. The JDBC syntax is pretty wierd, but it's far better documented than most other drivers. Together with IBExpert, and IBDataPump it seems like a much better database solution than even Solid. (Note that DataPump CAN create tables, but you have to click the "Build SQL Script" button for it to generate the CREATE TABLE script. Much better than an SQL converter...)

However, the JDBC driver for JayBird doesn't support XA through JDBC. It supports JDBC through JCA, the J2EE Connector Architecture. Believe me when I say "huh?"

The only thing I remember about JCA is that Tibco and WebMethods both use it, so I'm going to have to bone up on another API before I go back to this.

EDIT: There's a very good series of lectures on Transaction Processing here.