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

Hi again,

> 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?

It is more performant because it allows greater concurrency for a
fixed amount of connections in the pool. This of course requires that
the DB fully supports transaction interleaving or else the wrapper has
to use a mix of both techniques and you loose that advantage. I
measured an average 25% speed improvement with FirebirdSQL on a highly
concurrent test (250+ XA transactions per second).

The reason why you get this speed improvement is because in the first
technique you have to keep the connection out of the pool until after
the full prepare/commit cycle executed. This can take a relatively
long amount of time (0.25s is not uncommon for a transaction with lots
of enlisted resources) during which your connections are not usable.


> 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.

Thread A won't block since commit() does not require a connection, it
just needs to use the reference it kept on the enlisted XAResource to
be able to execute the 2PC protocol. Refer to the JTA spec chapter
3.4.6, Resource Sharing for details.


> 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?

I agree this isn't proper design but as you can read from the comments
that's not the point. There are other ways to get the same kind of
unexpected bahavior when playing with suspend/resume. Consider this
example:

tm.begin();

c = getXADataSource().getConnection();
c.createStatement().executeUpdate("UPDATE A");

tx = tm.suspend();

tm.begin();
c.createStatement().executeUpdate("UPDATE B");
tm.rollback();

tm.resume(tx);

c.close();

tm.commit();

What do you think should happen to UPDATE A and UPDATE B ? What do you
think will happen with 'on statement creation' enlistment and with 'on
connection acquirement' ? In my opinion, update A should be committed
and update B rolled back. What will actually happen depends on both
the resource and the enlistment policy implementations.

With 'on statement creation' what I expect will happen: update A
committed and update B rolled back while with 'on connection
acquirement' both will get committed because update B is executed in a
local transaction context since it has not been enlisted in the
sub-transaction's context.

While I totally agree that those examples are corner-case and not good
examples of how things should be written, those are nevertheless traps
that application programmers can easily fall on during the
implementation process. I guess you can imagine how insane it is when
you have to troubleshoot some code where you ask the TM to rollback
and you actually see the update committed to the DB.

Cheers,
Ludovic