2 Replies Latest reply on Apr 7, 2004 2:41 AM by danip

    When to close JDBC-connection for remote User Tx?

    danip Newbie

      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.

        • 1. Re: When to close JDBC-connection for remote User Tx?
          Adrian Brock Master

          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

          • 2. Re: When to close JDBC-connection for remote User Tx?
            danip Newbie

            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>