6 Replies Latest reply on Apr 30, 2003 11:24 AM by Mark Rees

    Multiple DB connections within an Appserver TX

    Mark Rees Newbie

      I have question about the DB connections returned from multiple calls to DataSource.getConnection within a single appserver transaction. (Jboss-3.0.6 / JSQLConnect / SQLServer 2000 ). My understanding is that within an appserver transaction, a datasource should always return the same physical connection to a particular client. This was the behaviour in Jboss-2.4.1a, however, it does not appear to be occuring in JBoss-3.0.6. For example, consider:

      tx.begin()
      ..
      connection1 = MyDataSource.getConnection();
      databaseWork1( connection1 )
      ...
      connection2 = MyDataSource.getConnection();
      databaseWork2( connection2 )
      ...
      tx.commit()

      If this code is executing with JBoss-3.0.6, it appears to the database that 'databaseWork1' and 'databaseWork2' are coming from two different clients, so 'databaseWork2' is forced to wait until the locks held by 'databaseWork1' are released, thus it deadlocks

      Could this be a misconfiguration on our part?

      Mark

        • 1. Re: Multiple DB connections within an Appserver TX
          David Jencks Master

          This is apt to happen in 3.0.6 if you are using an xa driver. It should not happen if you are using a local tx driver or if you use xa with a later jboss version (3.0.7 which is currently == cvs, 3.2 RC3, or 4)

          It would also happen if you are using a no-tx datasource or your ejb has not jta transaction.

          You should also be able to verify what is happening if you set the log level to trace for org.jboss.resource and study them carefully. If you are not using xa, please provide such a log excerpt showing different managed connections getting handed out in one tx.

          thanks

          • 2. Re: Multiple DB connections within an Appserver TX
            Mark Rees Newbie

            David,

            I have tried running the same piece of code that highlighted this problem in jboss-3.0.6 on jboss-3.0.7, and have encountered the same problem. Is there any setting that I need to enabled to ensure that the same physical connection is always returned in a XA transaction - SpecCompliant ??? or that to return the same connection across transactions ( if you were crazy enough to need that )

            Also, I found that format for the XADatasourceProperties has changed form being name1=value1;name2=value2
            to being name1=value1
            name2=value2

            Would you like to me to post an update jsql-xa-service.xml?

            Thanks Heaps,
            Mark

            Mark

            • 3. Re: Multiple DB connections within an Appserver TX
              Mark Rees Newbie

              David,

              I have tried running the same piece of code that highlighted this problem in jboss-3.0.6 on jboss-3.0.7, and have encountered the same problem. Is there any setting that I need to enabled to ensure that the same physical connection is always returned in a XA transaction - SpecCompliant ??? or that to return the same connection across transactions ( if you were crazy enough to need that )

              Also, I found that format for the XADatasourceProperties has changed form being name1=value1;name2=value2
              to being name1=value1
              name2=value2

              Would you like to me to post an update jsql-xa-service.xml?

              Thanks Heaps,
              Mark

              • 4. Re: Multiple DB connections within an Appserver TX
                Mark Rees Newbie

                Okay, I checked in the 3.2 branch, it seems that the code for ensuring that the same physical transaction is always returned in an XA TX is present there. Are you planning to back port it to the 3.0 branch? Do you need a hand doing this?

                Mark

                • 5. Re: Multiple DB connections within an Appserver TX
                  David Jencks Master

                  I'd appreciate the updated jsql-xa-service.xml.

                  I have no plans to backport anything into 3.0:-) I guess I'd try to find the time to apply a patch. I implemented this in 3.2 with a fairly extensive refactoring which I don't really want to backport.

                  Can you use 3.2 instead?

                  Thanks
                  david jencks

                  • 6. Re: Multiple DB connections within an Appserver TX
                    Mark Rees Newbie

                    Okay - We will try JBoss 3.2.

                    Here is an updated version of the jsql-xa-service.xml for jboss-3.0.7.

                    Cheers,
                    Mark