3 Replies Latest reply on Nov 15, 2019 8:20 AM by Martien van den Akker

    WebLogic to database WITHOUT  connection pool?

    ejohnallen

      We have a very unusual situation in which we may want to run an app in WebLogic Server yet avoid using a database connection pool. We think this would mean that each user had a dedicated database connection.

       

      Is this possible? It may sound strange, but we've never run into this before and we don't know enough about WebLogic Server ;-)

       

      The reason we want to do this is that we have a temporary transaction table that relies upon user sessions. We think that calls to the database through a connection pool may appear as a different user for each connection in the connection pool, 'confusing' the database.

       

      thanks in advance for any thoughts !!

        • 1. Re: WebLogic to database WITHOUT  connection pool?
          Martien van den Akker

          Hi,

           

          Could you elaborate on what you mean by 'temporary transaction table'?

          The database connections aren't tight to a user session. But in your application you should get a connection from the datasource. Perform your UI transaction on that connection and then 'close' it, which means that you give it back to the pool.

           

          I think you (or your developers) should rethink what you try to accomplish and then build it accordingly.

          For instance, if you would program this as a standalone application. What would you do? Create a connection, perform your transaction(s) and then close your connection?

          Then translate that to the use within Weblogic. And probably you'll find that you could do this pretty much 1:1 in the same way.

           

          Kind regards,
          Martien

          • 2. Re: WebLogic to database WITHOUT  connection pool?
            ejohnallen

            Thank you for your reply, Martien.

             

            We are using SOA Suite. We want to make several calls to Oracle SOA Suite APIs and contain all of those calls in an XA transaction. We need to do this in order to commit or rollback all of the calls at once rather than commit one call at a time.

             

            It turns out that, in that situation, SOA Suite uses a temporary transaction table to store some transaction information. From time to time we get an error when calling one of the APIs contained in our XA transaction. The error is an ORA-14450, "attempt to access a transactional temporary table already in use".

             

            We speculate that this might be caused by the fact that a single user can use several actual database connections if we use a connection pool. That's why we wan to try a run without using a connection pool. We're not sure that will help us, unfortunately ... but that is why we want to do this.

             

            Hope this helps !!

             

            regards,

             

            -ethan

            • 3. Re: WebLogic to database WITHOUT  connection pool?
              Martien van den Akker

              Hi,

               

              I don't think that will help. I'd check the database driver settings (XA driver, LLR settings, COmmit settings, etc.)

               

              What you also could try is to create a separate datasource expecially for this purpose and check the pin to thread check box.

              Next create a workmanager that has a max thread constraint based on the datasource https://docs.oracle.com/middleware/1221/wls/WLACH/pagehelp/J2EEappworkmaxthreadsconstraintconfigtitle.html . Create a new folder in SOASuite and set that workmanager on that folder.

              Then deploy your composite to that folder.

               

              It will make sure that there are no more threads running that composite then defined in the maxthread constraint that is based on the datasource. In essence the number of threads is determined from the max connections in the datasource. Each thread gets it's own database connections and keeps it. Therefor it is important to have a work manager that has no more threads than there are connections in the datasource. That will make sure that the code is running in a thread will use the same connection from the datasource.

               

              This only works when there are no dehydrations. Which, will by the way, break your transaction anyway.

               

              Kind regards,
              Martien