XA support - Mailing list pgsql-jdbc

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