Re: XA end then join fix for WebLogic - Mailing list pgsql-jdbc

ludovic orban wrote:
> Heikki,
>
> You wondered why Weblogic calls XAResource.start(TMJOIN) in this post:
> http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00011.php
>
> I'm going to try to explain you why that's happening in this email.
> Please note that this explanation is solely based on my personal
> observations of multiple transaction managers/app servers. I'm writing
> this from the top of my head so I may have done a few mistakes.

Thanks for the explanation.

> The 'on statement creation' one, while more complex to write is more
> flexible (end-user wise) and is generally more performant when used
> with databases fully supporting transaction interleaving. This one
> calls XAResource.start(TMNOFLAGS) when Connection.createStatement() or
> prepareStatement() or prepareCall() is called and
> XAResource.end(TMSUCCESS) when Connection.close() is called.

Can you elaborate how 'on statement creation' is generally more
performant? It seems to me that it leads to more start/end calls. At
least in a straightforward implementation of start(TMJOIN), that means
more round-trips to the database. Have you seen or done any performance
tests on this?

You might be able to get away with a slightly smaller connection pool,
but that's with the cost of more "context switches" in the DBMS as a
connection is repeatedly associated and disassociated with a transaction.

That also makes me a bit worried about possible deadlocks. For example,
imagine this simple example:

1 public void transactionalMethod() {
2     tm.begin();
3
4     Connection c = getXADataSource().getConnection();
5     c.createStatement().executeUpdate("UPDATE ...");
6     c.close();
7
8     tm.commit();
9 }

Now consider running that with a connection pool of just 1 connection:

Thread A: runs begin + getConnection + update + close, down to line 7.
The single Connection is now released back to the pool
Thread B: runs lines 1-5, but the UPDATE blocks because of a lock held
by transaction A.
Thread A: Tries to commit, but blocks there's no connections available
in the pool.

-> deadlock.

This is an extreme example, but the same scenario is possible with a
larger connection pool, just harder to trigger.

> For a good example of the increased flexibility you get with the 'on
> statement creation' policy has been discussed by Dmitri Maximovich:
> http://www.jroller.com/page/maximdim?entry=is_xa_transaction_support_broken
> Basically only implementations using 'on statement creation'
> enlistment policy can support this special case.

Passing a Connection-object in a bean invocation just isn't sane, even
if it was allowed by the spec. What if you pass a ResultSet-object,
should it work in BeanB? What about updates to an updatable ResultSet?

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com