XA support

Now that we have support for two-phase commit in the backend, it's time to 
add XA support to the JDBC driver.

The first issue we have to solve is the way we associate transactions with 
connections.

Consider the following example:

public void testMultiplex() throws Exception {
     Xid xid1 = new CustomXid(1);
     Xid xid2 = new CustomXid(2);
     Xid xid3 = new CustomXid(3);

     xaRes.start(xid1, XAResource.TMNOFLAGS);
     conn.createStatement().executeUpdate("UPDATE foobar1 SET foo = 'aa'");
     xaRes.end(xid1, XAResource.TMSUCCESS);

     xaRes.start(xid2, XAResource.TMNOFLAGS);
     conn.createStatement().executeUpdate("UPDATE foobar2 SET foo = 'bb'");
     xaRes.end(xid2, XAResource.TMSUCCESS);

     xaRes.start(xid3, XAResource.TMNOFLAGS);
     conn.createStatement().executeUpdate("UPDATE foobar3 SET foo = 'cc'");
     xaRes.end(xid3, XAResource.TMSUCCESS);

     xaRes.commit(xid1, true);
     xaRes.commit(xid2, true);
     xaRes.commit(xid3, true);
}

According to the JTA spec, this should be supported. One connection can be 
used to initiate a new transaction, without committing, preparing or 
rolling back the previous one.

We don't have backend support for this kind of operation. A connection can 
be associated with only transaction at a time. Changing that behaviour 
would require a major overhaul of the way the backend handles transactions 
and connections, AFAICS.

I can see three alternative ways to handle this in the driver:

A. When the second transaction starts, a new physical connection is opened 
behind the scenes. The second transaction is associated with the new 
physical connection and the first transaction remains associated with the 
original connection.

The application server is usually responsible for connection pooling, so 
opening new connnections behind the scenes will mess with the pool limits 
and possible prepared statement cache. We would have to do some kind of 
connection pooling ourselves, regardless of the application server pool.

B. When the second transaction starts, the first transaction is prepared 
behind the scenes, freeing the connection for the new transaction.

This makes it impossible to implement the transaction suspend/resume 
functionality, since the first transaction cannot be used to issue any 
more statements after the implicit prepare. It also imposes the additional 
overhead of two-phase commit for transactions that could otherwise use 
regular one-phase commit. It will also cause trouble if the connection is 
broken after the implicit prepare, because the transaction manager 
might not recognize the implicitly prepared transaction, so it will stay 
in the system holding locks etc. until it's manually rolled back by the 
administrator.

C. The second start call blocks, until the first transaction is committed, 
prepared, or rolled back.

This might cause tricky deadlocks, depending on the threading model of the 
application server and the transaction manager.

I experimented with some other DBMS' to find out how they handle this.

Apache Derby (IBM Cloudscape):
Supports XA only in embedded mode. Playing with temporary tables lead me 
to think that it does something similar to A. Connection management is 
probably quite lightweight in embedded mode. I didn't bother to look at 
the source code, though.

DB2 (Type 4 driver):
Does B, and suffers from the problems described above. "LIST INDOUBT 
TRANSACTIONS" shows the implicitly prepared transactions, suspend/resume 
doesn't work as it should.

Oracle:
The server natively supports having many active transactions in one connection.

I tried to install Firebird and Sybase as well, but couldn't get them 
installed properly. Anyone want to test them, or some other DBMS?

Which approach should we take?

- Heikki