[FIXED] Oracle zombie connections with Tomcat 9

Issue

I have created a very simple web service on Tomcat 9.0.12 that's each time it's called i create a connection to the Database ( Oracle 12 ) using a DataSource ( defined in the conf/context.xml file ) then before returning the response to the client, it closes the connection to the Database.

But if i check the active session on my Database it shows that the connection is still alive, so i have a lot of "zombie" connection to my Database.

I have used more or less the same code to a client side program, and in this case the program close the connection. Maybe can be a Tomcat bad configuration?

How can i fix this?

Thank you!

conf/context.xml

<Resource maxWaitMillis="5000" 
          maxIdle="100" 
          maxActive="200" 
          password="PASSWORD" 
          username="USER" 
          url="jdbc:oracle:thin:@/192.168.1.248:1521/PRD" 
          driverClassName="oracle.jdbc.driver.OracleDriver" 
          type="javax.sql.DataSource" 
          auth="Container" 
          name="jdbc_Connection"/>

Java

public String method() {

    try {
        System.out.println("---------------------------------------");
        System.out.println("START");
        System.out.println("---------------------------------------");


        DataSource ds = (DataSource) InitialContext.doLookup("java:comp/env/jdbc_Connection");

        Connection DB = ds.getConnection();


        String sql ="SELECT VAR FROM TABLE_NAME";
        PreparedStatement stmt = null;
        try {
            stmt = DB.prepareStatement(sql);
            ResultSet rs    = stmt.executeQuery();

            while(rs.next()){
                System.out.println("rs->"+rs.getString(1));
            }

            rs.close();
            stmt.close();
        } catch (SQLException ex) {
        }finally{
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException ex) {

                }
            }
        }


        System.out.println("CLOSE");
        DB.close();

    } catch (SQLException ex) {
        ex.printStackTrace();
    } catch (NamingException ex) {
        ex.printStackTrace();
    } 

    System.out.println("---------------------------------------");
    System.out.println("END");
    System.out.println("---------------------------------------");


    return "HELLO";
}

Solution

They're not zombies. They're waiting in a connection pool for reuse. Creating connections is expensive, so you don't want to (really) close them for nothing. As Andreas pointed out, connection pools return a wrapped connection, where calling close() doesn't really close it. It's just returned to the pool.

Your configuration says that there can be up to 100 idle connections in the pool, and 200 total connections. As Mark said, this is probably way too much.

Answered By - Kayaman