1 2 Previous Next 16 Replies Latest reply on Nov 24, 2003 7:30 PM by Adrian Brock

    different db connections for the same transaction

    aris Newbie

      We have a single transaction that is making multiple EJB calls. It seems that the database calls go over
      multiple database connections thus resulting in database lockups since Oracle thinks it is different users trying to access the same rows.
      Oracle Application server (and others) guarantee
      that calls that belong to the same transaction will
      use the same database connection.
      Is there a way to resolve this with jBoss?

        • 1. Re: different db connections for the same transaction
          Darran Lofthouse Master

          Are you talking about Entity bean access to the database or are you executing JDBC calls from your code?

          • 2. Re: different db connections for the same transaction
            jamesstrachan Newbie

            I assume that you are making method calls on entity beans. There are two possibilities :-

            a) Two or more different entity beans map to the same database table and row. This is bad object to database mapping which will cause trouble sooner or later. So presumably that is not the problem.

            b) Your transaction makes several calls to the same EJB, each of which changes data mapped to the database. The J2EE specification mandates that each method call will be followed by a call to ejbStore(). So the ejbStore() following the first method call may update the database and lock rows pending a commit.

            A second method call, followed by ejbStore(), will presumably get another database connection from the pool, with the results that you describe.

            Assuming that the problem is (b), there are two solutions that may help :-

            b1) Make sure that read only method calls on the EJB do not produce a database update. Simplest method is to use a dirty flag that is set true only when an update is needed. If the dirty flag is not set, ejbStore() does nothing. This will prevent read only methods writing to and locking the DB. You should do this anyway to prevent unnecessary writes.

            b2) If you have multiple methods that modify the entity bean, wrap them in a single method. Instead of :-

            doA()
            doB()
            doC()

            use doABC()

            which calls doA() etc. within the bean.

            ejbStore() will then be called once only after doABC() has completed.

            Hope this helps.

            James Strachan

            • 3. Re: different db connections for the same transaction
              aris Newbie

              We are using a Stateless Session bean that contains JDBC code.
              The calling application is calling the same bean twice
              in the same transaction.
              The first time it inserts a row and the second time it
              updates the row (I'm simplifying it here but this is the
              general idea)
              Each call of the bean however is using a different
              Oracle connection. Oracle does not realize these
              calls are part of the same transaction so when the
              update comes it gets blocked.

              • 4. Re: different db connections for the same transaction
                Adrian Brock Master

                Use a jboss data source.
                examples in docs/examples/jca

                1) Straight jdbc doesn't know about the JTA transaction!
                2) It will reuse the same connection in the same transaction.

                Regards,
                Adrian

                • 5. Re: different db connections for the same transaction
                  aris Newbie

                  We are using jBoss DataSources and Container Managed transactions.

                  • 6. Re: different db connections for the same transaction
                    Adrian Brock Master

                    Then you have some bad configuration or bad code.

                    Need more infomration to tell which.

                    Let's go through the standard list shall we (sigh!):
                    But keep it relevent, (e.g. I don't want your entire
                    server.log or an ejb-jar.xml with 100 ejbs)

                    Versions OS/JVM/JBoss
                    Code snippets
                    ejb-jar.xml/jboss.xml
                    oracle-ds.xml
                    Some proof from you that there are two connections
                    rather than a guess that this is the cause.

                    Less standard:
                    TRACE logging for
                    org.jboss.ejb.plugins, org.jboss.resource and org.jboss.tm

                    Regards,
                    Adrian

                    • 7. Re: different db connections for the same transaction
                      jamesstrachan Newbie

                      If you are using stateless session beans, JBoss will allocate an instance of the bean from a pool of stateless beans on each occasion that you call a method. The pool is used to avoid the performance hit of creating a new instance on each method call.

                      So the chances of getting the same instance (and DB connection) on two successive calls is very small. Hence the interference between the two operations on the database.

                      The quickest way out may be to change the bean to a stateful session bean so that the database connection is retained in the bean between the two calls. The ejbCreate() method must contain code to get the connection, and the ejbRemove() method must release the connection.

                      From the client, instead of :-

                      remote = home.create();
                      remote.doA();

                      // Other operations
                      remote = home.create();
                      remote.doB();

                      You write :-

                      remote = home.create();
                      remote.doA();

                      // Other operations
                      remote.doB();

                      remote = home.remove();

                      doA() and doB() should then use the same bean and DB connection. The remove() is needed to release the resources (bean and connection) for use by the next client.

                      James






                      • 8. Re: different db connections for the same transaction
                        aris Newbie

                        From the Oracle iAS manual:
                        Retrieving multiple connections from a data source
                        using the same username and password within a single global transaction
                        causes the logical connections to share a single physical connection.

                        From the BEA Weblogic manual:
                        Once a transaction begins, all database operations in an execute thread that get their connection from the same connection pool share the same connection
                        from that pool. These operations can be made through services such as Enterprise JavaBeans (EJB)
                        or Java Messaging Service (JMS), or by directly sending SQL statements using standard JDBC calls.
                        All of these operations will, by default, share the same connection and participate in the same
                        transaction.When the transaction is committed or rolled back, the connection is returned to the pool.

                        Can someone tell me what the expected jBoss behavior should be?

                        • 9. Re: different db connections for the same transaction
                          Adrian Brock Master

                          If you use an xa datasource you should use
                          <track-connection-by-tx> in your -ds.xml
                          This should already be there in the example oracle-xa-ds.xml

                          If you use a local datasource the same.
                          Local does this track by connection automatically.

                          Regards,
                          Adrian

                          • 10. Re: different db connections for the same transaction
                            aris Newbie

                            We are using Local transactions.
                            The jBoss version is 3.0.6.
                            It doesn't seem to guarantee that there is
                            only one connection per transaction.
                            Do we need to set track-connection in this case?

                            • 11. Re: different db connections for the same transaction
                              Adrian Brock Master

                              The 3.0 version does not support track-by-connection.

                              This was added for 3.2

                              I plan to backport the 3.2 jca implementation for 3.0.9 if some
                              backwards compatibility issues can be resolved in time.

                              Regards,
                              Adrian

                              • 12. Re: different db connections for the same transaction
                                aris Newbie

                                So, the 3.0.6 does not guarantee that the same connection will be used for Local transactions?
                                Should we upgrade to 3.2?
                                Exactly what version of 3.2 has this functionality?

                                • 13. Re: different db connections for the same transaction
                                  Andreas Ecker Newbie

                                  Hi,
                                  we are facing a similar problem.
                                  We are using JBoss 3.0.4 with Oracle9i Release 9.2.0.4.0 (using LocalTxConnectionManager).
                                  A stateful CMT Session Bean calls a BMP Entity Bean, all methods with Transaction REQUIRED. The primary key of the Entity Bean is then used to insert a detail using JDBC from the Session Bean.

                                  That works most of the time, but sometimes the inserts get different connections.
                                  I made some debug output into the bean logging the underlying connection. When I do the inserts, the underlying connection is the same most of the time. But very seldom and quite unpredictable I get different underlying connections causing a lock in the database.

                                  The connection in the Session Bean is created in ejbCreate() and closed in ejbPassivate(). The Entity Bean calls getConnetion in every method where a connection is needed.

                                  Regards,
                                  Andreas

                                  • 14. Re: different db connections for the same transaction
                                    Andreas Ecker Newbie

                                    The problem of our configuration seems to be, that we store the SessionBeans in an own ConnectionPool for our Web-Application. We did this, because we used deploying EJBs directly to the Oracle 8 Database and Lookups were very slow ...
                                    After switching to JBoss and Oracle 9 we left this for compatibility of the older version.
                                    It seems, that if the Session Bean is stored for a while, the Managed Connection may be already removed from the pool (but is still in the bean), and the method call of the Session Bean is not associated with the connection, that is used for the transaction. The Entity Bean gets the correct connection. Therefore there are two different connections used for the insert causing an exception or even worse a deadlock in the database.

                                    My questions concerning this are:

                                    Is is generally bad to store Stateful Session Beans in a kind of connection pool on client side?

                                    Is it bad to hold the connection in the Session Bean or preferable to fetch the connection from the DataSource everytime it is needed?

                                    Will switching to a newer version of JBoss avoid these problems?

                                    Regards,
                                    Andreas

                                    1 2 Previous Next