Hibernate c3p0 Connection NewPooledConnection clos

Question

I was trying to solve my problem from my previous post java.sql.SQLRecoverableException: Closed Connection . I tried with c3p0 connections. The property is as follows,

<property name="hibernate.connection.provider_class">
        org.hibernate.connection.C3P0ConnectionProvider</property>
    <property name="hibernate.connection.autoReconnect">true</property>
    <property name="dialect"> org.hibernate.dialect.Oracle10gDialect </property>
    <property name="hibernate.c3p0.min_size">5</property>
    <property name="hibernate.c3p0.max_size">20</property>
    <property name="hibernate.c3p0.timeout">300</property>
<property name="hibernate.c3p0.idle_test_period">6000</property>
    <property name="hibernate.c3p0.numHelperThreads">9</property>

After deploying in JBOSS i started testing my web. At beginning it was working fine and even faster. After 10 minutes it was keep loading and timed out. When gone through server.log i got some warning messages as follows.

00:34:13,162 INFO  [com.mchange.v2.c3p0.impl.NewPooledConnection] com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2) [c3p0] Exceptions occurred while trying to close a PooledConnection's resources normally.
00:34:13,162 INFO  [com.mchange.v2.c3p0.impl.NewPooledConnection] (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2) [c3p0] NewPooledConnection close Exception.: java.sql.SQLRecoverableException: IO Error: Software  caused connection abort: recv failed
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:556)   [ojdbc6.jar:11.2.0.3.0]
at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:3984) [ojdbc6.jar:11.2.0.3.0]
at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:474) [c3p0-0.9.1.jar:0.9.1]
at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:191) [c3p0-0.9.1.jar:0.9.1]
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.destroyResource(C3P0PooledConnectionPool.java:470) [c3p0-0.9.1.jar:0.9.1]
at com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask.run(BasicResourcePool.java:964) [c3p0-0.9.1.jar:0.9.1]
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547) [c3p0-0.9.1.jar:0.9.1]
Caused by: java.net.SocketException: Software caused connection abort: recv failed
at java.net.SocketInputStream.socketRead0(Native Method) [rt.jar:1.6.0_13]
at java.net.SocketInputStream.read(SocketInputStream.java:129) [rt.jar:1.6.0_13]
at oracle.net.ns.Packet.receive(Packet.java:300) [ojdbc6.jar:11.2.0.3.0]
at oracle.net.ns.DataPacket.receive(DataPacket.java:106) [ojdbc6.jar:11.2.0.3.0]
at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:315) [ojdbc6.jar:11.2.0.3.0]
at oracle.net.ns.NetInputStream.read(NetInputStream.java:260) [ojdbc6.jar:11.2.0.3.0]
at oracle.net.ns.NetInputStream.read(NetInputStream.java:185) [ojdbc6.jar:11.2.0.3.0]
at oracle.net.ns.NetInputStream.read(NetInputStream.java:102) [ojdbc6.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:124) [ojdbc6.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:80) [ojdbc6.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1137) [ojdbc6.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:290) [ojdbc6.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192) [ojdbc6.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4C7Ocommoncall.doOLOGOFF(T4C7Ocommoncall.java:61) [ojdbc6.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:543) [ojdbc6.jar:11.2.0.3.0]
... 6 more

00:34:13,177 WARN  [com.mchange.v2.resourcepool.BasicResourcePool] (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2) Failed to destroy resource: com.mchange.v2.c3p0.impl.NewPooledConnection@b041ecc: java.sql.SQLException: Some resources failed to close properly while closing com.mchange.v2.c3p0.impl.NewPooledConnection@b041ecc
at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:496) [c3p0-0.9.1.jar:0.9.1]
at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:191) [c3p0-0.9.1.jar:0.9.1]
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.destroyResource(C3P0PooledConnectionPool.java:470) [c3p0-0.9.1.jar:0.9.1]
at com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask.run(BasicResourcePool.java:964) [c3p0-0.9.1.jar:0.9.1]
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547) [c3p0-0.9.1.jar:0.9.1]

Please give some ideas to solve this issue.

Answer 1

This problem raises at some time the session is closed and the connection used for session is still alive, and when open a new session again a new session is opened. It leads to the usage of exceeding usage of Connections specified in connection pool, and helper threads plays their part and my problem comes here. I changed my Hibernate SessionFactory class and Customized Connection provider solved my problem. The changes are as follows....

public class HibernateSessionFactory {
private staticString CONFIG_FILE_LOCATION = "/hibernate.cfg.xml";
private static Configuration configuration = new Configuration();
private static org.hibernate.SessionFactory sessionFactory;
private static String configFile = CONFIG_FILE_LOCATION;
public static Session session;
static {
    try {
        configuration.configure(configFile);
        sessionFactory = configuration.buildSessionFactory();
    } catch (Exception e) {
        System.err.println("%%%% Error Creating SessionFactory %%%%");
        e.printStackTrace();
    }
}

public HibernateSessionFactory() {
}
public static Session getSession() throws HibernateException {

    try {
        if (session == null) {
            session = sessionFactory.openSession(MyConnectionProvider
                    .getConn());
        } else {

            if (session.isOpen() && !session.connection().isClosed()) {

                System.out.println("SESSION AVAILABLE...");

                MyConnectionProvider.showConnectionDetails();

                return session;
            }

            if (!session.isOpen()) {
                System.out.println("SESSION WAS CLOSED AND OPEN AGAIN..");
                session = sessionFactory.openSession(MyConnectionProvider
                        .getConn());
            } else if (session.connection().isClosed()) {

                System.out.println("CONNECTION WAS CLOSED AND OPENING AGAIN...");

                session.close();

                session = sessionFactory.openSession(MyConnectionProvider
                        .getConn());
            }

        }
        MyConnectionProvider.showConnectionDetails();
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    return session;
}
public static org.hibernate.SessionFactory getSessionFactory() {
    return sessionFactory;
}
public static void setConfigFile(String configFile) {
    HibernateSessionFactory.configFile = configFile;
    sessionFactory = null;
}
public static Configuration getConfiguration() {
    return configuration;
}

}

ang the MyConnectionProvider class is

public class MyConnectionProvider {

static String jdbcDriverClass;
static String jdbcUrl;
static String username;
static String password;
static String poolSize = "20";

static ComboPooledDataSource ds;

static {

    config(HibernateSessionFactory.getConfiguration().getProperties());

    ds = new ComboPooledDataSource();

    // System.out.println(" "+jdbcUrl);

    ds.setJdbcUrl(jdbcUrl);

    // System.out.println(username);

    ds.setUser(username);

    // System.out.println(password);

    ds.setPassword(password);

    ds.setMinPoolSize(1);

    ds.setMaxPoolSize(20);

}

public static void config(Properties props) {

    System.out.println("SETTING CONFIGURATION...");

    jdbcDriverClass = props.getProperty(Environment.DRIVER);
    jdbcUrl = props.getProperty(Environment.URL);
    username = props.getProperty(Environment.USER);
    password = props.getProperty(Environment.PASS);

    System.out.println(jdbcDriverClass + "  " + jdbcUrl + "  " + username
            + "  " + password + "  " + poolSize);
}

public static Connection getConn() {
    Connection conn = null;
    try {
        System.out.println("URL " + ds.getJdbcUrl());
        System.out.println("Busy Connections : "
                + ds.getNumBusyConnections("user", "user"));
        return ds.getConnection();
    } catch (SQLException e) {

        // TODO Auto-generated catch block
        e.printStackTrace();
        return conn;
    }
}

public static void showConnectionDetails() {

    try {
        System.out.println("Busy Connections :  "
                + ds.getNumBusyConnections() + " Connections : "
                + ds.getNumConnections() + " Thread Pool Size : "
                + ds.getThreadPoolSize() + " Active Threads : "
                + ds.getThreadPoolNumActiveThreads() + "  Idle Threads : "
                + ds.getThreadPoolNumIdleThreads() + "  Pending Tasks : "
                + ds.getThreadPoolNumTasksPending());
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

}