Database Connection break EJB3_问答_开发者_运维开发者技术经验分享

I have a jsf/ejb3 application doing some queries to an oracle database. Everything was working OK. Then, I loss my db connection, and after that, the application keeps failing with these message:

INFO: Communication failure detected when attempting to perform read query outside of a transaction. Attempting to retry query.

And followed by this stacktrace

    at co.gov.icfes.bheva.delegate.ResultadosDelegate.buscarAction(ResultadosDelegate.java:46)
    at co.gov.icfes.bheva.mbean.ListarResultadosHistoricos.buscarAction(ListarResultadosHistoricos.java:55)
    at sun.reflect.GeneratedMethodAccessor155.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.sun.el.parser.AstValue.invoke(AstValue.java:234)
    at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:297)
    at com.sun.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:68)
    at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88)
    ... 45 more
Caused by: javax.ejb.TransactionRolledbackLocalException: Exception thrown from bean
    at com.sun.ejb.containers.BaseContainer.checkExceptionClientTx(BaseContainer.java:4975)
    at com.sun.ejb.containers.BaseContainer.postInvokeTx(BaseContainer.java:4810)
    at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:2004)
    ... 81 more
Caused by: javax.ejb.EJBTransactionRolledbackException
    at com.sun.ejb.containers.BaseContainer.mapLocal3xException(BaseContainer.java:2253)
    at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:2053)
    at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:1955)
    at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:198)
    at com.sun.ejb.containers.EJBLocalObjectInvocationHandlerDelegate.invoke(EJBLocalObjectInvocationHandlerDelegate.java:84)
    at $Proxy124.buscarResultados(Unknown Source)
    at co.gov.icfes.bheva.service.impl.ResultadosService.buscarResultados(ResultadosService.java:33)
    at sun.reflect.GeneratedMethodAccessor157.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.glassfish.ejb.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1056)
    at org.glassfish.ejb.security.application.EJBSecurityManager.invoke(EJBSecurityManager.java:1128)
    at com.sun.ejb.containers.BaseContainer.invokeBeanMethod(BaseContainer.java:5292)
    at com.sun.ejb.EjbInvocation.invokeBeanMethod(EjbInvocation.java:615)
    at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:797)
    at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:567)
    at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.doAround(SystemInterceptorProxy.java:157)
    at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.aroundInvoke(SystemInterceptorProxy.java:139)
    at sun.reflect.GeneratedMethodAccessor96.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:858)
    at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:797)
    at com.sun.ejb.containers.interceptors.InterceptorManager.intercept(InterceptorManager.java:367)
    at com.sun.ejb.containers.BaseContainer.__intercept(BaseContainer.java:5264)
    at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:5252)
    at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:190)
    ... 79 more
Caused by: javax.ejb.TransactionRolledbackLocalException: Exception thrown from bean
    at com.sun.ejb.containers.BaseContainer.checkExceptionClientTx(BaseContainer.java:4975)
    at com.sun.ejb.containers.BaseContainer.postInvokeTx(BaseContainer.java:4810)
    at com.sun.ejb.containers.BaseContainer.postInvok开发者_开发技巧e(BaseContainer.java:2004)
    ... 104 more
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Conexión cerrada
Error Code: 17008
Call: SELECT RESULTADO_ID, PER_FISICA, PER_SOCIALES, FISICA, ESPANOL, ANO_PRESENTA, PER_ESPANOL, MES_PRESENTA, PUNTAJE_COMPARABLE, PER_QUIMICA, IDIOMAS, REGISTRO_SNP, SOCIALES, APTITUD_VERBAL, PER_APTITUD_MATEMATICA, PER_RAZONAMIENTO_ABSTRACTO, APTITUD_MATEMATICA, PER_IDIOMAS, PRODUCCION, PER_BIOLOGIA, RAZONAMIENTO_ABSTRACTO, QUIMICA, PER_RELACIONES_ESPACIALES, BIOLOGIA, PER_APTITUD_VERBAL, PUNTAJE_TOTAL, RELACIONES_ESPACIALES, APLICACION, PERSONA, ESTADO FROM BHEVA_RESULTADO WHERE (REGISTRO_SNP = ?)
    bind => [GN00495]
Query: ReadAllQuery(referenceClass=Resultado sql="SELECT RESULTADO_ID, PER_FISICA, PER_SOCIALES, FISICA, ESPANOL, ANO_PRESENTA, PER_ESPANOL, MES_PRESENTA, PUNTAJE_COMPARABLE, PER_QUIMICA, IDIOMAS, REGISTRO_SNP, SOCIALES, APTITUD_VERBAL, PER_APTITUD_MATEMATICA, PER_RAZONAMIENTO_ABSTRACTO, APTITUD_MATEMATICA, PER_IDIOMAS, PRODUCCION, PER_BIOLOGIA, RAZONAMIENTO_ABSTRACTO, QUIMICA, PER_RELACIONES_ESPACIALES, BIOLOGIA, PER_APTITUD_VERBAL, PUNTAJE_TOTAL, RELACIONES_ESPACIALES, APLICACION, PERSONA, ESTADO FROM BHEVA_RESULTADO WHERE (REGISTRO_SNP = ?)")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.processExceptionForCommError(DatabaseAccessor.java:1422)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:679)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:530)
    at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:529)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:205)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:191)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:262)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:618)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2537)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2496)
    at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:455)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:997)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:675)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:958)
    at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:432)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1021)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2857)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1225)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1299)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1299)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1299)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1299)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1207)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1167)
    at org.eclipse.persistence.internal.sessions.AbstractSession.readAllObjects(AbstractSession.java:2664)
    at co.gov.icfes.bheva.eao.impl.ResultadosEAO.buscarResultados(ResultadosEAO.java:59)
    at sun.reflect.GeneratedMethodAccessor158.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.glassfish.ejb.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1056)
    at org.glassfish.ejb.security.application.EJBSecurityManager.invoke(EJBSecurityManager.java:1128)
    at com.sun.ejb.containers.BaseContainer.invokeBeanMethod(BaseContainer.java:5292)
    at com.sun.ejb.EjbInvocation.invokeBeanMethod(EjbInvocation.java:615)
    at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:797)
    at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:567)
    at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.doAround(SystemInterceptorProxy.java:157)
    at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.aroundInvoke(SystemInterceptorProxy.java:139)
    at sun.reflect.GeneratedMethodAccessor96.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:858)
    at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:797)
    at com.sun.ejb.containers.interceptors.InterceptorManager.intercept(InterceptorManager.java:367)
    at com.sun.ejb.containers.BaseContainer.__intercept(BaseContainer.java:5264)
    at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:5252)
    at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:190)
    ... 102 more
Caused by: java.sql.SQLException: Conexión cerrada
    at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:3331)
    at oracle.jdbc.OracleConnectionWrapper.prepareStatement(OracleConnectionWrapper.java:205)
    at com.sun.gjc.spi.base.ConnectionHolder.prepareStatement(ConnectionHolder.java:535)
    at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareCachedStatement(ConnectionWrapper40.java:251)
    at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareCachedStatement(ConnectionWrapper40.java:48)
    at com.sun.gjc.spi.ManagedConnection.prepareCachedStatement(ManagedConnection.java:880)
    at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareStatement(ConnectionWrapper40.java:169)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1404)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1353)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:645)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:580)
    ... 146 more

As you can see from the exception i´m using eclipselink 2. After some minutes the connection is back and the application continue working, but it takes several minutes.

I´ve read somethings about the type of exceptions involved. If there are unchecked, the container does the job, but then why is this happening?

Here´s the portion of code where the exception appears:

public class ResultadosEAO implements IResultadosEAO {

@PersistenceContext(unitName = "BHEVA_EJB")
private EntityManager em;

public List<ResultadoDTO> buscarResultados(String documento, String registroSNP,
        String ano, String periodo) throws SinResultadoException {
    ExpressionBuilder eb = new ExpressionBuilder();
    Expression expression = null;
    if (registroSNP != null && !registroSNP.trim().isEmpty()){
        expression = eb.get("registroSNP").equal(registroSNP);
    } else if (documento != null && !documento.trim().isEmpty()){
        expression = eb.get("persona").get("numeroDocumento").equal(documento);
    }
    expression = addExpressionParams(eb, expression, ano, periodo);
    Session session = ((JpaEntityManager)em.getDelegate()).getActiveSession();
    List<Resultado> resultados = session.readAllObjects(Resultado.class, expression);
    List<ResultadoDTO> resultadosDTO = crearListadoResultadosDTO(resultados);
    return resultadosDTO;
}


private List<ResultadoDTO> crearListadoResultadosDTO(List<Resultado> resultados) throws SinResultadoException {
    List<ResultadoDTO> resultadosDTO = new ArrayList<ResultadoDTO>();
    if (resultados.size() == 0){
        throw new SinResultadoException("No se encuentran resultados para su búsqueda");
    }
    for (Resultado r : resultados){
        try{
            ResultadoDTO rDTO = bindResultadoDTO(r);
            if (rDTO != null){
                resultadosDTO.add(rDTO);
            }
        } catch (SinResultadoException e){
            continue;
        }
    }
    if (resultadosDTO.size() == 0){
        throw new SinResultadoException("No se encuentran resultados para su búsqueda");
    }
    return resultadosDTO;
}

Thanks in advance for reading,

From the reported symptoms it appears that it is taking "several minutes" for the database connection to return but clients are still able to connect and use the application during this time.

What is your transaction handling layer?

After an exception the EntityManager must be closed and discarded, as exceptions are not recoverable, and you'd need a new EntityManager for future calls. And I can't really see that happening from your code.