Re: XA support

I see option A below as the lesser of three evils. Is it reasonable
to believe that people using XA would
know enough to allocate enough connections ?

Optionally we could have two implementations and choose between A and B

Dave
On 29-Jun-05, at 3:49 PM, Heikki Linnakangas wrote:

> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
>