Announcement Announcement Module
Collapse
No announcement yet.
Number of connections running out... Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Number of connections running out...

    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:
    Code:
    	<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):
    Code:
    	<bean id="adminDao"
    		class="com.thalasoft.learnintouch.core.dao.hibernate.AdminHibernateDao">
    		<property name="sessionFactory" ref="sessionFactory" />
    	</bean>
    The data source bean definition:
    Code:
    	<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..

  • #2
    The generic DAO class:
    Code:
    @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;
    	}
    
    }

    Comment


    • #3
      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:
      Code:
      		<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>

      Comment


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

        I tried adding the following properties to set up C3P0 as in:
        Code:
        <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.

        Comment


        • #5
          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.

          Comment


          • #6
            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.

            Comment

            Working...
            X