When to close JDBC-connection for remote User Tx?

[danip]

Hi,

I have a remote client with several threads which all run
(remote) user transactions at a JBoss server.
(The transactions are demarcated at the client side.)
Every transaction invokes serveral remote method calls
of a stateless session bean which in turn
execute SQL-statements of an XA-JDBC-datasource.
The related JDBC connections are handled (properly) by the JBoss-Tx-Manager via XAResources.

However, when I close an aquired connection inside a bean method
it seems that the related JDBC-transaction is implicitly committed,
altough the user transaction is not yet committed (by the remote client).
This causes consistency problems.

On the other hand, if I leave the connection open, the JBoss
CachedConnectionManager complains about not closing the connection
on time (it is only a debug-warning though).

So my question: when and how should I close a related DB connection?
After committing or (right) before committing the User transaction?
Closing after the commit would mean that I needed an extra remote
call just for the connection.close() operation :(

If I make the CachedConnectionManager do the "connection
close job" (which is not clean/standard compliant) the system
seems to quickly run out
of pooled connections. This already happens with only 5 client threads,
which means 5 concurrent user transactions.

Or is there a smart way to configure JBoss and the internal
connection manager for that purpose?

Greetings and thanks for your time,

Daniel

PS: I use JBoss 3.2.3 an MySQL with an older XA-enabled JDBC driver.

[adrian.brock]

Since you session is stateless you must close the connections.
There is no way and you are not allowed to hold connections open for the client
for stateless.

Closing the connection does not commit the transaction. It just suspends the
connection. Further work in the same transaction will use the same connection.

MySQL has no support for XA, so I don't know what you mean by older.
Make sure you are using InnoDB tables. The older MySQL
table types do not support transactions.

Regards,
Adrian

[danip]

Hi adrian,

thanks for your reply!
You are certainly right in respect to closing the connection EVERY TIME.
Indeed, the close does not commit the connection and I should have/
could have found it out myself...
Sorry for bothering this newsgroup.

MySQL XA JDBC driver:
The mysql-connectior-java-2.0.14 distribution DOES
contain an XA-JDBC-Driver.
It is just a fake implementation though since MySQL itself
does not really support 2PC. (Internally the driver does 1PC.)
Later the mysql developers have removed this driver from the
distribution (probably because they felt it did not make much sense).
E.g. in mysql-connectior-java-3.0.10 it is not available anymore.

The old XA driver works fine (at least as a fake driver).

Greetings,

Daniel


PS: Here is a related sample *-ds.xml:

<?xml version="1.0" encoding="UTF-8"?>


<xa-datasource>
<jndi-name>ItemDB</jndi-name>
<xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXaDataSource</xa-datasource-class>
<xa-datasource-property name="ServerName">localhost</xa-datasource-property>
<xa-datasource-property name="DatabaseName">item</xa-datasource-property>
<xa-datasource-property name="Port">3306</xa-datasource-property>
<user-name>item</user-name>
item
<transaction-isolation>TRANSACTION_SERIALIZABLE</transaction-isolation>
<blocking-timeout-millis>5000</blocking-timeout-millis>
</xa-datasource>