3 Replies Latest reply on Dec 4, 2001 2:10 PM by David Jencks

    XAConnection Pooling

    Bani Greyling Newbie

      Hi,

      I am in a situation where I must update two databases (SQLServer2000)in the same transaction; enter XADataSources.

      To configure my datasource I first used a certified XA driver (JSQLConnect.jar http://www.j-netdirect.com).

      com.jnetdirect.jsql.JSQLXADataSource

      This worked very nice untill I encounter a dirty/dead connection in the pool. I can live with the fact that the transaction fail, but what I cannot live with is the dead connection being returned back to the pool. So I changed my configuration.

      org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl

      Now this work nice in terms of the dead connection being destroyed, but my two phase commit will throw an HueristicRollbackException if a commit or rollback fail. If I force the last statement to fail (db trigger) my distributed transaction fail completely (which is good). No I am not sure why it fail completely. I would have thought that some part of the transaction will succeed while some part will fail (beacuase I am using the XA wrapper). Or will it only corrupt my data integrity if I have multiple JBoss servers and multiple databases in the same transaction?

      I figured the reason why my dead connection is not removed. When the xa wrapper encounter an error it inform the pool of the event and the connection get removed later on (when the transaction is finnished). With a 'real' XAConnection there is no way the pool is informed of exception, becuase the third party driver will not know about this ability of the pool. Therefore I am considering to extend org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl, org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl, etc to wrap my 'real' xa datasource. What I would like to know is I am on the right track or if I am completely of the track? Is there maybe someone else with the same problem wo already have an elegant solution?

      Regards
      Bani

        • 1. Re: XAConnection Pooling
          David Jencks Master

          I'm assuming that from your other post you are using jboss 3, although it would be nice if you said. I would be reluctant to spend a lot of time improving XADatasource stuff in 2.4 since it is removed from 3.0.

          XADataSourceImpl will not work as a xa driver in the jca-xa wrapper, if that is what you are proposing.

          I think the solution (in jboss 3) is to
          1. extend the jca-xa wrapper to wrap the Connection as well as the XAConnection.

          2. Catch and filter SQLExceptions. I think this needs to be in a db specific manner, since as far as I can tell there is no standardization in the codes returned. Anyway, if the SQLException can be determined to mean the connection is dead, a ConnectionError event should be broadcast which will remove the ManagedConnection from the pool.

          • 2. Re: XAConnection Pooling
            Bani Greyling Newbie

            My other (http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t=forums/ post was for 3.0. This thread refer to JBoss 2.4.3

            Do you think your proposal will make it into 3.0?

            I don't think it is necesary to filter SQLExceptions for dead connections. Being conservative and broadcast all SQLException occurances will sufice. One must just be able to turn connection removal on or off, because it could result in some performance issues (especially if your business processes generate lots off FK constraints errors and stuff).

            Post http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t=forums/ was the result of my attempt to test just this what we are discussing. I wanted to see what happen with dead connections in the jca-xa wrapper.

            Thanx
            Bani

            • 3. Re: XAConnection Pooling
              David Jencks Master

              I don't think broadcasting all sql exceptions will go over very well. Killing connections for fk constraints violations....no thanks. Having dead connections remain in the pool... also no thanks.

              We could do driver specific wrapper versions also, although I think a configuration element with some kind of list of fatal exceptions might be easier.

              I don't know if this is likely to get into 3.0. I have a lot to do right now... I am very tempted to start from the lgpl version of the code before jca/external minerva/x license and rewrite most of this stuff (into lgpl), including making a simpler and better working pool.