Number of connections running out...

[stephaneeybert]

Hello,

I have a suite of integration tests running fine against a MySql 5 database schema of 117 tables.

There are 350 tests roughly.

Now, when I want to run this same suite of tests against an Oracle 10g XE database with the same schema of 117 tables, I get an exception telling me the number of connections has run out.

Caused by: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12516, TNS:listener could not find available handler with matching protocol stack
The Connection descriptor used by the client was:
stephane-ThinkPad-X61s:1521/xe

Now I wonder about what to do.

I don't feel like increasing the maximum number of allowed connections as it will simply delay the problem.

I could make sure the connection is closed after each integration test. This would mean not reusing allocated connections and therefore running slower, but at least it would run.

Or I could try to use some connection pooling and limit the number of connections in the pool, making sure Hibernate or the pooling mechanism does not use too many of them.

I have a Spring 3.1.3.RELEASE / Hibernate 3.6.9.Final environment with the following setup:

	<bean id="sessionFactory"
		class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
		<property name="dataSource">
			<ref bean="dataSource" />
		</property>
		<property name="mappingDirectoryLocations">
			<list>
				<value>classpath:com/thalasoft/learnintouch/core/domain</value>
			</list>
		</property>
		<property name="mappingResources">
			<list>
				<value>com/thalasoft/learnintouch/core/domain/typedef.hbm.xml</value>
			</list>
		</property>
		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">${hibernate.dialect}</prop>
				<prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
				<prop key="hibernate.connection.pool_size">0</prop>
				<prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</prop>
				<prop key="hibernate.show_sql">true</prop>
				<prop key="hibernate.format_sql">true</prop>
			</props>
		</property>
	</bean>

	<!-- Setup the Spring transaction manager -->
	<bean id="transactionManager"
		class="org.springframework.orm.hibernate3.HibernateTransactionManager">
		<property name="sessionFactory">
			<ref local="sessionFactory" />
		</property>
	</bean>

	<tx:annotation-driven />

	<bean
		class="org.springframework.beans.factory.annotation.RequiredAnnotationBeanPostProcessor" />

	<!--  Translate dao exceptions into Spring exceptions -->
	<bean class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />

One DAO bean definition (there is one for each domain table):

	<bean id="adminDao"
		class="com.thalasoft.learnintouch.core.dao.hibernate.AdminHibernateDao">
		<property name="sessionFactory" ref="sessionFactory" />
	</bean>

The data source bean definition:

	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName">
			<value>${dataSource.driverClassName}</value>
		</property>
		<property name="url">
			<value>${dataSource.url}</value>
		</property>
		<property name="username">
			<value>${dataSource.username}</value>
		</property>
		<property name="password">
			<value>${dataSource.password}</value>
		</property>
	</bean>

Thanks for any hint..

[stephaneeybert]

The generic DAO class:

@Repository
@Transactional
public abstract class GenericHibernateDao<T, ID extends Serializable> implements GenericDao<T, ID> {

	private static Logger logger = LoggerFactory.getLogger(GenericHibernateDao.class);

	private Class<T> persistentClass;
	private SessionFactory sessionFactory;

	@SuppressWarnings("unchecked")
	public GenericHibernateDao() {
		this.persistentClass = (Class<T>) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0];
	}

	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	public SessionFactory getSessionFactory() {
		if (sessionFactory == null) {
			throw new IllegalStateException("sessionFactory has not been set on DAO before usage");
		}
		return sessionFactory;
	}

	public Session getSession() {
		return getSessionFactory().getCurrentSession();
	}

	public Class<T> getPersistentClass() {
		return persistentClass;
	}

	@SuppressWarnings("unchecked")
	@Override
	public T findWithId(ID id, boolean lock) {
		T entity;

		if (lock) {
			entity = (T) getSession().load(getPersistentClass(), id, LockOptions.UPGRADE);
		} else {
			entity = (T) getSession().load(getPersistentClass(), id);
		}

		return entity;
	}

	@SuppressWarnings("unchecked")
	@Override
	public boolean isFoundById(ID id) {
		try {
			@SuppressWarnings("unused")
			T entity = (T) getSession().load(getPersistentClass(), id);
		} catch (ObjectNotFoundException e) {
			return false;
		}

		return true;
	}

	@Override
	public long countAllRows() {
		Criteria criteria = getSession().createCriteria(getPersistentClass());
		criteria.setProjection(Projections.rowCount());
		return ((Long) criteria.list().get(0)).longValue();
	}

	@Override
	public long countFoundRows() {
		Criteria criteria = getSession().createCriteria(getPersistentClass());
		criteria.setProjection(Projections.rowCount());
		return ((Long) criteria.uniqueResult()).longValue();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<T> findWithExample(T exampleInstance, String... excludeProperty) {
		Criteria criteria = getSession().createCriteria(getPersistentClass());
		Example example = Example.create(exampleInstance);
		for (String exclude : excludeProperty) {
			example.excludeProperty(exclude);
		}
		criteria.add(example);
		return criteria.list();
	}

	@Override
	public T makePersistent(T entity) {
		getSession().saveOrUpdate(entity);
		return entity;
	}

	@Override
	public void makeTransient(T entity) {
		getSession().delete(entity);
		String message = "The entity " + entity.getClass().getCanonicalName() + " was deleted fine.";
		logger.debug(message);
	}

	@Override
	public void flush() {
		getSession().flush();
	}

	@Override
	public void clear() {
		getSession().clear();
	}

	@Override
	public void close() {
		getSession().close();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<T> findObjectsByCriteria(Criterion... criterion) {
		Criteria criteria = getSession().createCriteria(getPersistentClass());
		for (Criterion c : criterion) {
			criteria.add(c);
		}
		return criteria.list();
	}

	@Override
	public T findObjectByCriteria(Criterion... criterion) {
		T object = null;
		List<T> results = findObjectsByCriteria(criterion);

		if (results.isEmpty()) {
			if (logger.isDebugEnabled()) {
				logger.debug("No search results found for: " + criterion);
			}
		} else {
			if (results.size() > 1 && logger.isDebugEnabled()) {
				logger.debug("The criterion : " + criterion + " should return only one result");
			}
			object = results.get(0);
		}

		return object;
	}

	@SuppressWarnings("unchecked")
	@Override
	public T findObjectByCriteria(Criteria criteria) {
		T object = null;
		List<T> results = criteria.list();

		if (results.isEmpty()) {
			if (logger.isDebugEnabled()) {
				logger.debug("No search results found for the criteria : " + criteria);
			}
		} else {
			if (results.size() > 1 && logger.isDebugEnabled()) {
				logger.debug("The criteria : " + criteria + " should return only one result");
			}
			object = results.get(0);
		}

		return object;
	}

	@SuppressWarnings("unchecked")
	public Page<T> getPage(final int pageNumber, final int pageSize, final Criteria criteria) {
		int totalSize = 0;
		if (pageSize > 0) {
			// A separate criteria for the count is required to avoid an
			// exception if the criteria has an order clause
			Criteria countCriteria = getSession().createCriteria(getPersistentClass());
			countCriteria.setProjection(Projections.rowCount());
			// TODO Why is uniqueResult returning a Long and setFirstResult requiring an int ?
			totalSize = ((Long) countCriteria.uniqueResult()).intValue();
			int startIndex = Page.getStartIndex(pageNumber, pageSize, totalSize);
			criteria.setFirstResult(startIndex);
			criteria.setMaxResults(pageSize);
		}
		List<T> list = criteria.list();
		return new Page<T>(pageNumber, pageSize, totalSize, list);
	}

	@SuppressWarnings("unchecked")
	public Page<T> getPage(final int pageNumber, final int pageSize, final String statement, final Map<String, Object> parameters, Session session) {
		Query query = session.createQuery(statement);
		Set<String> keys = parameters.keySet();
		for (String key : keys) {
			query.setParameter(key, parameters.get(key));
		}
		int totalSize = 0;
		if (pageSize > 0) {
			String countStatement = "select count(1) " + removeSelect(removeOrders(statement));
			Query countQuery = session.createQuery(countStatement);
			for (String key : keys) {
				countQuery.setParameter(key, parameters.get(key));
			}
			Integer count = (Integer) countQuery.list().get(0);
			totalSize = count.intValue();
			int startIndex = Page.getStartIndex(pageNumber, pageSize, totalSize);
			query.setFirstResult(startIndex).setMaxResults(pageSize);
		}
		List<T> list = query.list();
		return new Page<T>(pageNumber, pageSize, totalSize, list);
	}

	public static String removeSelect(String hql) {
		int beginPos = hql.toLowerCase().indexOf("from");
		return hql.substring(beginPos);
	}

	private static final Pattern ORDER_PATTERN = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);

	public static String removeOrders(String hql) {
		Matcher m = ORDER_PATTERN.matcher(hql);
		StringBuffer sb = new StringBuffer();
		while (m.find()) {
			m.appendReplacement(sb, "");
		}
		m.appendTail(sb);
		return sb.toString();
	}

	public <T> T initializeAndUnproxy(T entity) {
		if (entity == null) {
			throw new NullPointerException("The entity passed for unproxying is null.");
		}
		Hibernate.initialize(entity);
		if (entity instanceof HibernateProxy) {
			entity = (T) ((HibernateProxy) entity).getHibernateLazyInitializer().getImplementation();
		}
		return entity;
	}

}

[stephaneeybert]

I don't see any connection pooling in my current configuration.

Is there any default connection pooling enabled by Hibernate ?

Any way to fine tune it ?

Or should I use another connection pooling like C3P0 ?

I could add some C3P0 properties to my Hibernate properties as in:

		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">${hibernate.dialect}</prop>
				<prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
				<prop key="hibernate.connection.pool_size">0</prop>
				<prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</prop>
				<prop key="hibernate.show_sql">true</prop>
				<prop key="hibernate.format_sql">true</prop>
				<prop key="hibernate.c3p0.acquire_increment">1</prop>
				<prop key="hibernate.c3p0.idle_test_period">100</prop>
				<prop key="hibernate.c3p0.max_size">5</prop>
				<prop key="hibernate.c3p0.max_statements">0</prop>
				<prop key="hibernate.c3p0.min_size">1</prop>
				<prop key="hibernate.c3p0.timeout">100</prop>
			</props>
		</property>

[stephaneeybert]

From the page http://docs.jboss.org/hibernate/core...-hibernatejdbc

I tried adding the following properties to set up C3P0 as in:

<prop key="hibernate.c3p0.min_size">5</prop>
<prop key="hibernate.c3p0.max_size">20</prop>
<prop key="hibernate.c3p0.timeout">1800</prop>
<prop key="hibernate.c3p0.max_statements">50</prop>

Although the build still works fine with MySql it again fails with Oracle with the same exception.

[stephaneeybert]

I ran this command:

ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;

to increase the number of processes allowed, but still got the same exception and error message from Oracle.

[stephaneeybert]

I had to restart the Oracle server for the number of processes update to take effect.

After the restart, I could see the number of processes being updated to their new value, with the command:

select * from gv$resource_limit;

And it looks like it solved the issue as the exception and Oracle error message no longer show up.