8 Replies Latest reply on Aug 17, 2006 2:05 AM by Harish Pandia

    Results caching in Connection Pool

    Harish Pandia Newbie

      We are using JBoss 4.0.2 with MySQL 5.0. We are using Connection Pooling of JBoss with MySQL database. The records updated in one query in not getting reflected in another query though the records are committed and is updated in database.

      For ex: A connection X is feteched from connection pool (using datasource & JNDI mapping). Some fields of a record are updated with this "X" connection and transaction is committed. The connection is then closed. Immediately after this, a new connection "Y" is fetched from connection pool (this returns an different connection from pool) and same record is fetched. However it shows up data prior to the updation!!! But the same data is reflected in database. After sometime (1 min or idle-timeout-in-minutes value), if the records are fetched again, it shows up the new/updated data. I assume there is some caching of results at the Jboss connection pool level.

      Please let me know if there is some problem with configuration.

      Following is the mysql-ds.xml for the same
      ********************************************************

      <local-tx-datasource>
      <jndi-name>MySqlDS</jndi-name>
      <connection-url>jdbc:mysql://172.17.182.102:3306/uiia</connection-url>
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <user-name>x</user-name>
      y
      <min-pool-size>1</min-pool-size>
      <max-pool-size>20</max-pool-size>
      <idle-timeout-minutes>1</idle-timeout-minutes>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>

      <type-mapping>mySQL</type-mapping>

      </local-tx-datasource>


      *****************************************************

        • 1. Re: Results caching in Connection Pool
          Weston M. Price Master

          There is no such thing as 'caching results' at the JBoss connection pool level.

          The problem is in your undertanding of transactions. Use of a local-transaction-datasource implies that you want JBoss to manage transactions for you. When you close a connection, this does not mean that JBoss commits the transaction.

          For example consider the following pseudo code:

          Connection c = get C Connection
          c.doJDBC
          c.close()

          //NO Txn has been committed yet
          Connectiond = get D Connection
          d.doJDBC
          d.close()

          //Still no commit

          Connection d still will see the old records of c because the container has not committed your transaction. Depending upon where this is executed (EJB, Servlet) the transaction will be committed at different times and each has different semantics. I suggest some basic reading on JTA, LocalTransactions and JDBC.


          • 2. Re: Results caching in Connection Pool
            Harish Pandia Newbie

            Thanks Weston.
            Well, just to add to your example, i'm doing an explicit commit from my application.
            ///
            Like
            Connection c = getConnection.
            c.doJDBC;
            c.commit;
            c.close;
            ////
            and then d = getConnection;
            d.doJDBC;
            d.commit;
            d.close;

            Since the updated data is reflected in database, i assume commit is working fine. As per database transaction rules, if i commit in database, it should be available to anyone querying after this.

            Please let me know if i'm grossly mis-understanding something.

            • 3. Re: Results caching in Connection Pool
              Weston M. Price Master

              If you are explicitly comitting, they you don't need to use <local-txn-datasource>, you would use <no-txn-datasource>.


              A quick question before we go any further, in what context are you using the datasource? EJB? Servlet?


              • 4. Re: Results caching in Connection Pool
                Harish Pandia Newbie

                we are calling this from POJO's.
                However i did try to put
                transaction-isolation=TRANSACTION_READ_COMMITTED and this has solved the problem. But i would surely like to know the best and ofcourse the correct approach. Meanwhile, also trying to brush my JTA knowledge.
                Would try using <no-tx-datasource>

                • 5. Re: Results caching in Connection Pool
                  Weston M. Price Master

                  But POJO's called from where?

                  Inside an EJB? Inside a Servlet? I would imagine that this is a Servlet or you are in an EJB where you have specified no transaction. Otherwise JBoss would not let you do an explict commit using this type of datasource.

                  Could you please be more specific about the exact environment in which you are running?


                  • 6. Re: Results caching in Connection Pool
                    Harish Pandia Newbie

                    Sorry weston.
                    We are calling this POJO's (which gets the connection from JBoss Connection Pool using datasource) from Servlet. There is no EJB involved.
                    Also, tried with <no-tx-transaction> and that seems to work.
                    So, what is the correct approach ?

                    Thanks.

                    • 7. Re: Results caching in Connection Pool
                      Weston M. Price Master

                      Ok thanks, that makes a bit more sense.

                      You basically have 3 options:

                      a)Use the javax.transaction.UserTransaction interface to demarcate transaction boundaries in your Servlet. Doing this will enable you to remove the explicit commits from your code, as well as use the default transaction isolation level of the driver.

                      b)Manage the transactions explicitly yourself. This is effectively what you are doing now, just with the wrong datasource, <local-tx-datasource>. This is why it worked when you switched to the <no-tx-datasource>.

                      c)Wrap your POJO's in an EJB and allow the container to manage the transactions for you.

                      Generally I prefer the first approach because it keeps your POJO's free of transaction code that can be cumbersome to read, and error prone. Further, try to leverage as much of the application server facilities as you can, that's why it is there.



                      • 8. Re: Results caching in Connection Pool
                        Harish Pandia Newbie

                        Thanks Weston, that gives a brief picture of different ways of handling transactions.
                        Can you guide me to any resource on web which gives more understanding of 1st option. (I searched google and read some articles but it seems to be ambigous).
                        Thanks for all the suggestions.