Uploaded image for project: 'Commons DBCP'
  1. Commons DBCP
  2. DBCP-265

Connection close handling DatabaseMetaData exceed max open cursor oracle

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 1.2.1, 1.2.2
    • 1.3
    • None
    • Windows/Unix, Oracle 10gR2, JDK 1.4, Oracle JDBC Driver 10.2.0.1.

    Description

      Hello,

      i was suprised by following behavior:
      /**

      • Here all is OK, the finally block closes the connection and implicit the statement and the resultset. If called repeatedly, only one cursor is used.
      • @throws SQLException
        */
        public void test2() throws SQLException{
        Connection con = null;
        try{
        con = dataSource.getConnection();
        Statement statement = con.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from MyTable");
        if(resultSet.next())
        resultSet.getString(1);
        }finally {
        if (con != null) {
        con.close();
        }
        }
        }

      But doing this repeatedly
      /**

      • Danger: exceeds max open cursors if called repeatedly.
      • @throws SQLException
        */
        public void test1() throws SQLException{
        Connection con = null;
        try{
        con = dataSource.getConnection();
        DatabaseMetaData metaData = con.getMetaData();
        ResultSet schemas = metaData.getSchemas();
        }finally {
        if (con != null) {
        con.close();
        }
        }
        }
        exceeds max open cursors on the database.

      DataSource created with following block:
      dataSource = new BasicDataSource();
      dataSource.setDriverClassName("oracle.jdbc.OracleDriver");
      dataSource.setUsername("myuser");
      dataSource.setPassword("mypasswd");
      dataSource.setUrl("jdbc:oracle:thin:@192.168.0.1:1521:mydb");

      Of course, explicit closing the 'schemas' ResultSet in the second case solves the problem.

      I am not certainly sure my expected behavior is conform to the jdbc spec. But without using the BasicDatasource this problem doesnt occure.

      I inspected the code and saw, that org.apache.commons.dbcp.BasicDataSource (or more specific, DelegatingConnection) doesnt track Connections and ResultSets created from DatabaseMetadata-Object.Only created Statements form DelegatingConnection are tracked. And in PoolableConnectionFactory.passivateObject() only these statements are closed and warnings are cleared on the underlying connection.

      Attachments

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            Unassigned Unassigned
            pd pd
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment