RecoverableDataAccessException 커넥션을 잃어버렸을때 에러

안녕하세요. 얼마전 스프링을 개발하다  RecoverableDataAccessExceptoin 에러를 마주하게되었습니다. 

 

에러내용을 살펴보니

 

org.springframework.dao.RecoverableDataAccessException: 
PreparedStatementCallback; SQL [select email, password, enabled from members where email=?]; 

The last packet successfully received from the server was 107,705,271 milliseconds ago. 
 The last packet sent successfully to the server was 107,705,271 milliseconds ago. 
is longer than the server configured value of 'wait_timeout'.
 You should consider either expiring and/or testing connection validity before use in your application,
 increasing the server configured values for client timeouts, or using the Connector/J connection property 
'autoReconnect=true' to avoid this problem.; 
nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: 

... (이하 중복 생략) ...

 

 

마지막 패킷이 107,705,271밀리세컨드(대략 21시간) 전에 성공적으로 연결되었고,

이것은 서버의 wait_timeout 보다 긴 시간이라고 말하고 있습니다.

 

이 뜻에 대해 알아보니 mysql은 wait_timout(기본값 8시간)동안 접속하지 않으면 커넥션 연결을 종료한다는 것입니다.

저 역시 매일 직장에 다녀와 8시간을 넘긴 상태에서 접속하여 프로젝트를 진행하려다보니 이러한 에러와 마주했습니다.

 

에러의 해결방법은 비교적 간단합니다. 해결방법은 2가지가 있습니다. 

 

 

첫번째,

에러내용을 자세히 읽어보면 You should ... (중략) .. autoReconnect=true' to avoid this problem.;'  하며

에러를 어떻게 해결할지 알려주고 있습니다. 바로 autoReconnect=true값을 설정하여 재접속 처리를 해주는 것입니다.

 

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
		destroy-method="close">
		<property name="driverClass" value="com.mysql.jdbc.Driver" />
		<property name="jdbcUrl"
			value="jdbc:mysql://localhost/testProject?autoReconnect=true" />
		<property name="user" value="root" />
		<property name="password" value="1111" />
</bean>

 

위와 같이 datasource의 jdbcUrl 속성의 value값 뒤에 ?autoReconnect=true를 설정하여 주면 됩니다. 

연결이 끊겨 발생한 에러이므로 자동으로 재접속 처리를 해준다는 것입니다. 

 

하지만 위의 해결방식은 치명적인 문제가 있습니다. 

그것은 바로 트랙잭션 롤백처리가 안된다는 것입니다. 

 

autoReconnect=true의 재접속 처리 방법은 wait_timeout이후 커넥션에 접근시 SqlException에러를 리턴하고

재접속 처리를 합니다. 

 

예를 들어 두개의 쿼리를 트랙잭션 처리하는 요청을 처리한다고 할때

첫번째 쿼리에서 SqlException을 리턴하고 롤백하여 다시 첫번째 쿼리를 실행하는게 아니라

첫번째 쿼리는 에러처리하고 실행하지 않은채 두번째 쿼리를 실행하여 데이터의 정합성이 깨지는 결과를

초래할 수 있습니다. 

 

따라서 많은 이들이 이러한 방법을 추천하지 않으며 실제로 Mysql Documentary에서도 추천하지 않는 방법입니다. 

그래서 이러한 치명적 에러를 보완해줄 두번째 방법이 있습니다.

 

 

두번째,

커넥션 검사 쿼리 validation query 설정하기

validation query를 설정하면 서버에서 DB에 접속하기전 커넥션이 유효한지 확인하기 위해 사용합니다. 

이를 이용하면 autoReconnect=true에서 발생하는 트랙잭션 오류도 해결할 수 있습니다. 

 

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">
    <property name="driverClassName" 
value="oracle.jdbc.driver.OracleDriver"/>
    <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:orcl"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
    <property name="defaultAutoCommit" value="true"/>
    <property name="initialSize" value="5"/>
    <property name="maxActive" value="30"/>
    <property name="maxIdle" value="5"/>
    <property name="maxWait" value="30000"/>
    <property name="validationQuery" value="SELECT 1"/>
    <property name="testOnBorrow" value="false"/>
    <property name="testOnReturn" value="false"/>
    <property name="testWhileIdle" value="true"/>
    <property name="timeBetweenEvictionRunsMillis" value="60000"/>
</bean>

validation query는 말그대로 커넥션이 유효한지 확인하기 위해 사용하는 쿼리로 우리가 임의로 설정할 수 있습니다. 

하지만 반드시 반환값이 있는 select절과 같은 쿼리를 실행해야 합니다. 

 

그런데 실제로 테이블에 있는 데이터를 조회하는 경우에 운영서버에 많은 부담을 줄 수 있습니다. 

validation query를 실행한다는 것 자체가 DB에 접근할때 마다 validation query를 실행하여 운영서버에 부담을 주는데

거기에 더해 validation query로 많은 양의 데이터를 조회하는 쿼리를 사용하면 더 큰 부담을 줄 수 있습니다. 

 

따라서 사용하는 dbms에 따라

 

• Oracle: select 1 from dual
• Microsoft SQL Server: select 1
• MySQL: select 1
• CUBRID: select 1 from db_root

위와 같은 validation query를 권장하고 있습니다.

그래서 저 역시도 MySQL을 사용하고 있어 select 1 이라고 설정하였습니다.

 

 

그런데 저의 dataSource설정을 보면 ,validationQuery 속성 외에

testOnBorrow, testOnReturn, testWhileIdle 속성이 있는것을 알 수 있는데  

 

• testOnBorrow: 커넥션 풀에서 커넥션을 얻어올 때 테스트 실행(기본값: true)
• testOnReturn: 커넥션 풀로 커넥션을 반환할 때 테스트 실행(기본값: false)
• testWhileIdle: Evictor 스레드가 실행될 때 (timeBetweenEvictionRunMillis > 0) 커넥션 풀 안에 있는 유휴 상태의 커넥션을 대상으로 테스트 실행(기본값: false)

각각의 속성은 위의 내용을 말하고 있습니다. 

검증에 지나치게 자원을 소모하지 않게 testOnBorrow 옵션과 testOnReturn 옵션은 false로 설정하고,

오랫동안 대기 상태였던 커넥션이 끊어지는 현상을 막게 testWhileIdle 옵션은 true로 설정하는 것을 추천합니다.

위와 같이 설정하면 DB에 부담은 되지만 트랙잭션 처리도 올바르게 처리하여 에러처리를 할 수 있습니다.

 

이렇게 두가지의 해결방법을 알아 보았습니다. 

 

두가지 방법 모두 datasource 설정부분만 간단하게 수정해주면 되어 그렇게 어려운 방법은 아니라고 생각됩니다. 

 

커넥션 종료와 관련된 에러 해결방법은 이렇게 하면 되지만 

종료 뿐만 아니라 커넥션과 관련하여 여러가지 에러가 발생할 수도 있습니다. 

자신의 웹사이트를 안정적으로 운영하고 싶다면 커넥션풀에 대해 대해 깊게 공부하는 것이 중요할 것 같습니다. 

 

제가 이 글을 쓰는데 많은 참조를 한 레퍼런스를 링크 남깁니다.

 

https://d2.naver.com/helloworld/5102792

여러분에게 많은 도움이 됫으면 하네요~