9 Replies Latest reply on Jul 24, 2004 10:21 AM by Gregg Freeman

    Jboss 3.2.3, Oracle-DS leaking cursors

    Raj Tiwari Newbie

      My application uses a pool of oracle database connections. I configured this by modifying docs/examples/jca/oracle-ds.xml.

      Under load, my DB accesses started failing with SQLExceptions saying: "database cursor limit reached". My DBA told me that jboss had just 2 connections in the pool, but somehow was leaking cursors. He guessed that the app wasn't commiting transactions.

      I searched Jboss fora and docs but did not find a way of setting autoCommit to true. My workaround was to set the following in my oracle-ds.xml:

      <check-valid-connection-sql>commit</check-valid-connection-sql>


      This seems to have worked.

      My questions:
      1. Is the default behaviour of the DB pool to not autocommit to true?
      2. What is the way to set autocommit?

      Thanks.
      Raj

        • 1. Re: Jboss 3.2.3, Oracle-DS leaking cursors
          Adrian Brock Master

          If you have problems with cursors, you are not closing result sets or statements.
          Try add

          <track-statements>true</track-statements>
          

          to your oracle-ds.xml

          In jboss-3.2.4, this will also tell you when you don't close result sets.

          To answer your question directly, the connection is in autocommit mode by default,
          until you are use JTA, then it goes under the control of the transaction manager,
          (or you if you use UserTransactions).

          • 2. Re: Jboss 3.2.3, Oracle-DS leaking cursors
            Raj Tiwari Newbie

            Adrian,
            Thanks for the quick response. My JDBC code is rather simple. It gets connections from a pool and creates statements and result sets. But it finally only closes the connection object. The code is usually of the following format:

            Connection conn=null;
            Statement stmt=null;
            ResultSet rs=null;
            try
            {
             conn=m_dataSource.getConnection();
             stmt=conn.createStatement();
             rs=stmt.XXX();
             .
             .
             .
            }
            finally
            {
             conn.close();
            }


            The assumption is that closing the connection will cascade into a closing or stmt and rs. This is incorrect?

            Will the 'track-statements' change actually fix the problem or simply report it in 3.2.3?

            Thanks.
            Raj

            • 3. Re: Jboss 3.2.3, Oracle-DS leaking cursors
              Adrian Brock Master

              You leak a statement and a result set on every invocation.
              Remember, the connection is not really closed, it is returned to the pool.

              track-statements will give you a warning AND close the statement, but it is more
              efficient for you to do this - and your logs don't fill up with stacktraces:-)

              • 4. Re: Jboss 3.2.3, Oracle-DS leaking cursors
                Raj Tiwari Newbie

                Adrian,
                Thanks a lot for this info. This is very helpful.
                The Apache commons connection pooling libs I used with Tomcat used to intercept connection.close() and close out open result sets, statements and return the connection to the pool. This also seems to be in line with the java.sql.Connection's contract (http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Connection.html#close()):
                Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released.

                Would this be considered a bug in Jboss' connection pooling code?

                -Raj

                • 5. Re: Jboss 3.2.3, Oracle-DS leaking cursors
                  Adrian Brock Master

                  No, it is by design. The book-keeping involved for JBoss has overhead over you
                  doing it - you know exactly what objects you are dealing with.

                  It would be trivial to implement it (just don't log the warning) but then you would
                  hit the same problem when you port to another appserver that doesn't do it for you.

                  It is better to warn you at development time and then run at full speed in production.

                  • 6. Re: Jboss 3.2.3, Oracle-DS leaking cursors
                    Adrian Brock Master

                    I know another appserver does it through finalize()/garbage collection which is even worse
                    for performance.

                    • 7. Re: Jboss 3.2.3, Oracle-DS leaking cursors
                      Janne Mattila Newbie

                      This cannot be anything else than a bug. If the spec states that closing a connection releases JDBC resources immediately, and JBoss does not do it, it's definately a bug, no?

                      Optimizing the performance is a smart thing to do, but there should at least be a configuration option for enabling spec-compliant (although slower) operation.

                      • 8. Re: Jboss 3.2.3, Oracle-DS leaking cursors
                        Janne Mattila Newbie

                        Also, check out JDBC3.0 specification 11.6.2, which states that connection.close always closes all (non-pooled) statements, and 14.2.5., which states that resultsets are automatically closed when statement is closed.

                        My interpretation of these is that developer should never have to include messy

                        } finally {
                        try {
                        resultset.close();
                        } catch (SQLException ignore) {
                        try {
                        statement.close();
                        } catch (SQLException ignore) {


                        garbage in the code as long as (s)he makes sure that connection is closed....unfortunately JBoss (and many other non-compliant implementations) compel to do that.

                        • 9. Re: Jboss 3.2.3, Oracle-DS leaking cursors
                          Gregg Freeman Newbie

                          The question was very specifically regarding oracle. Oracle very specifically states in their documentation that result sets are not closed when the connection is closed.

                          Blame Oracle for their jdbc driver.