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

Hi Heikki,

2006/11/7, Heikki Linnakangas <heikki@enterprisedb.com>:
> Well, if the server has the resources to do more concurrent work than
> the pool size allows, just increase the pool. If it doesn't, then it
> doesn't no matter what you do.
>
> Did you try increasing the pool size instead of interleaving normal work
> and commits?

No, I didn't try with different pool size. I just checked that with 10
connections you could do 25% more work with interleaved transactions.

I guess I could achieve the same performance by increasing the pool
size but wouldn't that  add some overhead in memory utilization on the
DB engine ? I mean you can do less with the same amount of resources
when using XA compared to local transactions.


> I guess it depends on the RM, but let's assume a typical client/server
> scenario where one XAConnection has one TCP connection to the server.
> The commit() will certainly have to submit the commit message to the
> server somehow, and unless it uses carrier pigeons or some other
> out-of-band channel, it has to use the TCP connection it has. If that
> connection is busy, waiting for another query to finish, the commit will
> have to wait.
>
> In fact, because the commit() call will have to wait for the current
> statement to finish, that might lead to a longer response time.
> Especially if you have a mix of long and short transactions.
>
> I just tried this with Firebird (1.5.3) and JayBird (2.1.0) that you did
> your performance test with. It *does* seem to suffer from the scenario I
> described. See attached test case.

I disagree. The reason why commit blocks in your use case is because
of transaction isolation (ie: update locks) in the DB engine and
nothing else. You could reproduce this exact scenario with two local
transactions.

If the two transactions you run concurrently aren't put to sleep
because of locked data access, they'd both run in parallel without a
hitch.

To convince yourself, edit your test and change
 conn.createStatement().executeUpdate("UPDATE foo SET bar = 0000");
into
 conn.createStatement().executeUpdate("INSERT INTO foo VALUES(0000)");

Then add the missing
 xares.end(blockxid, XAResource.TMSUCCESS);
after
 System.out.println("Second update committed");

and finally change this incorrect call
 conn.commit();
into
 xares.commit(blockxid, true);



> > 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.
>
> Umm, sub-transaction?

A badly chosen term to indicate the transaction that runs between
tm.suspend() and tm.resume() in the example.

With 'on connection acquirement' UPDATE B is committed because it runs
on a resource that has not been enlisted in the current transaction.
This means a default auto-committed local transaction is silently used
instead.


> I can feel your pain :). Some years ago, we found out the hard way that
> a FixPack to WebSphere Application Server 3.5.X (can't remember exactly)
> introduced a bug that made it ignore setRollbackOnly-calls if an EJB
> throwed an exception. That lead to corruption in our production
> database, and the customer was not happy.

This is exactly my point: I can accept that a database, JMS server or
transaction manager has some limitations but *NOT* inconsistencies.
Especially *NOT* when it comes to distributed transactions where the
whole point is to secure data coherence, even at a high cost. I could
accept an exception being raised when the TM or the RM detects an
unsupported call but silently messing the consistency is not
acceptable.

Ludovic