[Error] DB Connection 종료 에러 해결: No operations allowed after statement closed

[Error] DB Connection 에러 해결:  No operations allowed after statement closed 

 

 

 

JSP를 MySQL과 연동하여 잘 구동되던 페이지를 다음날, 처음으로 들어갈 때마다 아래와 같은 오류가 발생했다.

 

Cause: java.sql.SQLException: No operations allowed after statement closed.
; SQL []; No operations allowed after statement closed.; nested exception is java.sql.SQLException: No operations allowed after statement closed.

 

 

이는 Statement 객체를 close 한 후에 그 Statement 객체를 사용하려고 해서 발생한 오류이다.

 

닫은 후 재사용한 경우 중에 닫은 Statement 객체로 다시 SQL을 실행하여 발생한 에러는 MySQL 연결이 일정 시간동안 사용되지 않으면 연결 해제되어 발생하는 문제였다.

 

 

 

MySQL 사이트에 들어가보면 실제로 mysql wait_timeout 설정이 기본값 28800으로 8시간인데,  이 커넥션이 연결된 이후 해당 커넥션의 close 없이 8시간이 지나면 해당 커넥션을 종료시킨다고 나와있다.

 

MySQL :: Re: connection lost after a few hours

 

 

문제는 이렇게 종료된 커넥션을 dbcp connection pool에서 여전히 가지고 있는 상태가 되는 것이다. 이 때 DB 관련 프로그램이 호출되면 커넥션 관련 에러가 발생한다.

 

 

이를 해결하기 위해서 wait_timeout 이나 autoReconnect=true로 수정하라고 나오는데, wait_timeout은 커넥션 수를 증가가 됨으로 좋은 방법은 아니다.

 

 

autoReconnect=true사용 방법은 jdbc 커넥션 설정 파일에서 url뒤에 ?autoReconnect=true를 추가해주면 된다.

 

 

- globals.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://[ip]:[port]/[dbname]?autoReconnect=true
username=[username]
password=[password]

 

 

 

하지만

 

 

autoReconnect 옵션은 첫 쿼리를 수행한 다음 DB 세션에 문제가 있으면 SQLException 리턴 후 재접속 처리를 한다.

 

 

그렇기 때문에 트랜잭션 구동 환경에서 수행 중인 트랜잭션은 롤백이 되어야 하고, 남은 트랜잭션은 수행 되지 않아야 할 때 autoReconnect 옵션 후 SQLException이 발생하는 경우는 해당 트랜잭션이 진행되지 않게 App 단에서 예외처리를 해주어야 한다.

 

 

 

트랜잭션을 사용하는 환경이라면 DB 세션의 재접속 처리는 JDBC autoReconnect 설정이 아닌  java에서 DB 사용하기 전 해당 커넥션이 정상적인지 검사하는 DBCP의 validationQuery 기능을 사용하는 것이다.

 

 

 

- dataSource 설정 파일에서 validationQuery 속성을 추가

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
	<property name="driverClassName" value="${driver}"/>
	<property name="url" value="${url}"/>
	<property name="username" value="${username}"/>
	<property name="password" value="${password}"/>
	<property name="validationQuery" value="SELECT 1"/>
	<property name="testWhileIdle" value="true"/>
</bean>

 

 

 

여기서 확인할 것은 이 옵션으로 사용하고 있는 쿼리가 최경량의 SQL인지 확인하는 것이다 !

 

 

왜냐, 사용자쿼리 실행 전에 이 쿼리를 실행하게 하여 커넥션이 되어있는 지 확인하는 것이어서 DB로서는 추가적인 부하가 되기도 한다.

 

 

그래서 보통 가장 간단한 쿼리인 "SELECT 1"(MySQL) 혹은 "SELECT 1 FROM DUAL"(Oracle) 을 쓴다.

 

 

 

 

에에 대한 재밌는 글이 있다.

 

출처 : DBCP의 validationQuery 약인가? 독인가?

 

현재 담당하고 있는 프로젝트와 Apache의 Commons에 있는 DBCP를 사용하여 구현된 Connection Pool 관리자로부터 Connection을 받아서 쓰고 있다. 그런데 DB 서버의 부하를 점검해 본 결과, 당연하게도 DBCP의 validationQuery로 할당한 SQL문의 호출이 가장 많았다.

솔루션의 성격상 DB 서버에 대한 Access가 빈번히 일어나고 그 응답 또한 빠른시간을 필요로하기 때문에 DB의 부하를 가능한 줄여야 하는데 DBCP에서 Connection 객체를 받아 올 때 마다 실행되는 validationQuery는 엄청난 부하를 야기하고 있었다. 값비싼 객체인 DB와의 Connection을 효율적으로 관리하여 DB 관련 프로세스의 효율을 높이고자 사용하는 DBCP가 아이러니하게도 DB 서버의 부담을 높이는 작용을 하고 있었던 것이다.

과연 validationQuery는 반드시 필요한가?
DBCP의 API 문서를 확인한 결과, BasicDataSource의 Validation Query는 필수가 아닌 선택적인 기능으로서 할당되었을 경우에만 수행되는 것으로 나와 있다. 단, 이 기능을 사용하고자 할당한 경우에는 해당 쿼리의 실행결과 반드시 1개 이상의 resultset이 나와야 한다고 명시되어 있다.
만약 자신의 프로젝트에서 DBCP를 사용하고 있다면, 그리고 validationQuery를 설정하여 사용하고 있다면 해당 쿼리가 최경량의 SQL문을 사용하고 있는지 확인하는 것은 잊지 말아야 할 것이다.

추천되는 쿼리는
Oracle의 경우 : select 1 from dual;
MySQL의 경우 : select 1;

정도를 사용하면 좋을 것이다. 이외의 쿼리를 validationQuery로 사용 중 이라면 지금 당장 쿼리 실행 소요 시간을 비교해 보라.