트와일라잇
95
2018-10-12 10:50:45 작성 2018-10-12 11:02:44 수정됨
2
73

스프링 DB 풀 Connection is invalid. 질문드립니다.


<환경>

OS : Window Server 2012

WAS : Tomcat 7.0

DB : MSSQL 2012


위 환경에서 스프링 4.0 기반 프로젝트가 운영되고 있는데

리스너로 별도 스레드 구동 후 백그라운드에서 특정 폴더의 파일을 주기적으로 이관하고 있습니다.


문제는 서비스 초기에는 사용자가 적어서 그런지 문제가 없었는데 아침, 새벽을 제외하고 

### Cause: java.sql.SQLException: Error accessing PooledConnection. Connection is invalid.

메세지가 뜨면서 계속 쿼리문을 실행하지 못하고 실패되고 있습니다.

스레드 부분을 제외한 일반 사용자들은 사용상에 문제가 없는걸로 봐서 소스상에 문제가 있는거 같은데 갈피를 못잡겠습니다. 


MyBatis 설정은 아래와 같습니다.

<bean id="dataSource" class="org.apache.ibatis.datasource.pooled.PooledDataSource">
        <property name="driver" value="${DB_DRIVER_CLASS}" />
        <property name="url" value="${DB_URL}" />
        <property name="username" value="${DB_USERNAME}" />
        <property name="password" value="${DB_PASSWORD}" />
	<property name="poolMaximumActiveConnections" value="100" />
	<property name="poolMaximumIdleConnections" value="10" />		
	<property name="poolMaximumCheckoutTime" value="20000" />
	<property name="poolTimeToWait" value="20000" />
	<property name="poolPingEnabled" value="true" />
	<property name="poolPingQuery" value="select 1" />
	<property name="poolPingConnectionsNotUsedFor" value="60000" />
</bean>


특이사항이 있다면 아래와 같이 수동으로 bean을 호출하고 , 수동 트랜잭션을 사용하고 있습니다.

public class PollingDocument {
	private DataSource dataSource = ClassDispatcher.getApplicationContext().getBean(DataSource.class);
	private DocumentService documentService = ClassDispatcher.getApplicationContext().getBean(DocumentService.class);

	.....
  
	private void process(Document doc) throws Exception  {
	 // 트랜잭션 설정
	DefaultTransactionDefinition def = new DefaultTransactionDefinition();
	def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
	DataSourceTransactionManager txManager = new DataSourceTransactionManager(dataSource);
	TransactionStatus sts = txManager.getTransaction(def);

	......
	
	txManager.commit(sts);
	
	......
}


이와같이 bean을 사용하거나 수동 트랜잭션 사용시 혹시 문제사항이 있을까요?

검색을 해봐도 몇일째 답을 못찾고 다른분들도 모르겠다 하시고.....

조언을 해주시면 감사하겠습니다.


<에러 내용>

### Cause: java.sql.SQLException: Error accessing PooledConnection. Connection is invalid.
; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; Error accessing PooledConnection. Connection is invalid.; nested exception is java.sql.SQLException: Error accessing PooledConnection. Connection is invalid.
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:368)
	at com.sun.proxy.$Proxy76.selectOne(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:163)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)
	at com.sun.proxy.$Proxy133.selectPollingMoreInfo(Unknown Source)
	at sun.reflect.GeneratedMethodAccessor361.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:168)
	at org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:52)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:168)
	at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:58)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:168)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
	at com.sun.proxy.$Proxy134.selectPollingMoreInfo(Unknown Source)
	at kr.co.exsoft.exrep.explorer.polling.MappingPollingMoreInfo.proc_mappingPollingMoreInfo(MappingPollingMoreInfo.java:38)
	at kr.co.exsoft.exrep.explorer.polling.PollingAutoDir.processAutoPolling(PollingAutoDir.java:141)
	at kr.co.exsoft.exrep.explorer.polling.PollingAutoDir.startJob(PollingAutoDir.java:65)
	at kr.co.exsoft.exrep.explorer.polling.AutoPollingService.run(AutoPollingService.java:50)
	at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Error accessing PooledConnection. Connection is invalid.
	at org.apache.ibatis.datasource.pooled.PooledConnection.checkConnection(PooledConnection.java:254)
	at org.apache.ibatis.datasource.pooled.PooledConnection.invoke(PooledConnection.java:243)
	at com.sun.proxy.$Proxy74.prepareStatement(Unknown Source)
	at sun.reflect.GeneratedMethodAccessor72.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:50)
	at com.sun.proxy.$Proxy74.prepareStatement(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:72)
	at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:82)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:54)
	at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:70)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:56)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:259)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:132)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:62)
	at sun.reflect.GeneratedMethodAccessor99.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)
	... 26 more


0
0
  • 답변 2

  • 노올자
    450
    2018-10-12 11:05:19

    myBatis 버전을 확인해 보세요.

    https://github.com/mybatis/mybatis-3/issues/748

    0
  • 트와일라잇
    95
    2018-10-12 11:20:30

    노올자 님,

    확인해보니 현재 mybatis 버전이 3.2.3이라 버전업을 해봐야겠습니다.

    감사합니다~

    0
  • 로그인을 하시면 답변을 등록할 수 있습니다.