http://www.daesangit.com/ditboard/board/read.cgi?board=oralib&y_number=50&nnew=2


   ▒   자료실   ▒    

 이름 관리자 
 등록일 2002/9/21(토) 20:47 
 
 서블렛 + JDBC 연동시 코딩 고려사항 ⑤   
Connection뿐 아니라 Statement, ResultSet도 반드시 닫혀야 한다

Connection 의 close() 만 고려했지 Statement나 ResultSet에 대한 close는 전혀 고려 하지 않고 있다. Statement를 닫지 않아도 Connection을 닫았기 때문에 Statement나 ResultSet 은 자동으로 닫히는 것이라고 생각하기 쉽다. 하지만 사실은 그렇지 않다. 

만약 DB Connection Pooling을 사용하지 않고 직접 JDBC Driver를 이용해 매번 DB에 연결하고 끊는 구조라면 문제될 게 없다. 하지만 DB Connection Pooling은 이제 보편화됐기 때문에 DB Connection Pooling을 사용해야 한다는 것은 누구나 아는 사실이다. 그것을 애플리케이션 서버가 제공해 줄수도 있고, 작은 서블렛 엔진에서 운영하고 있다면 직접 만들거나 인터넷에 있는 소스를 이용할 수도 있을 것이다. 

이처럼 DB Connection Pooling을 사용하고 있을 경우 Conneciton이 실제 close()되는 것이 아니라 Pool에 반환되는데 특정 Connection 에서 열어둔 Statement를 close() 하지 않은채 그냥 반환시켜 놓게 되면, 언젠가는 그 Connection 은 다음과 같은 SQLException을 야기할 가능성이 있다. 

Oracle :
java.sql.SQLException : ORA-01000: maximum open cursor exceeded !!
(최대열기 커서수 초과)
UDB DB2 :
COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC 드라이버] CLI0601E 유효하지 않은
명령문 핸들 또는 명령문이 닫힘. SQLSTATE=S1000
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0129E 핸들(handle)이 
더이상 없다. SQLSTATE=HY014 
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0954C 응용프로그램 힙(heap)에 명령문을 처리하기 위해 사용 가능한 저장영역이 충분하지 않다.
SQLSTATE=57011

이유는 앞 2번글에서 이미 언급했다. 따라서 3번의 소스는 다음과 같은 유형으로 수정돼야 한다. 

public void doGet(HttpServletRequest req, HttpServletResponse res) 
throws ServletException, IOException, SQLException
{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String id = req.getParameter("id");
try {
conn = ......; // (편의상 생략합니다.)
stmt = conn.createStatement();
rs = stmt.executeQuery("sselect * from XXX where id = '" + id + "'");
while(rs.next()) {
...... 
}
// rs.close();
// stmt.close();
}
finally {
if ( rs != null ) try {rs.close();}catch(Exception e){}
if ( stmt != null ) try {stmt.close();}catch(Exception e){} // <-- !!!!
if ( conn != null ) ......; // (편의상 생략)

}
.....
}

사실 위와 같은 구조에서 ResultSet에 대한 것은 그다지 중요하지 않다. ResultSet은 Statement가 close()될 때 함께 자원이 해제된다. 따라서 다음과 같이 해도 된다. 

public void doGet(HttpServletRequest req, HttpServletResponse res) 
throws ServletException, IOException, SQLException
{
Connection conn = null;
Statement stmt = null;
String id = req.getParameter("id");
try {
conn = ......;
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("sselect * from XXX where id = '" + id + "'");
while(rs.next()) {
...... 
}
rs.close(); //<--- !!!
}
finally {
// if ( rs != null ) try {rs.close();}catch(Exception e){}
if ( stmt != null ) try {stmt.close();}catch(Exception e){}
if ( conn != null ) ......;
}
.....
}


다음은 프로그래밍을 잘못하고 있는 가장 대표적인 유형 중 하나다. 

Connection conn = null;
try {
conn = .......;
Statement stmt = conn.createStatement();
stmt.executeUpdate("...."); <--- 여기서 SQLException 이 일어나면...
.....
.....
..... <-- 만약,이쯤에서 NullPointerException 이 일어나면 ?
.....
stmt.close(); <-- 이것을 타지 않음 !!!
}
finally{
if ( conn != null ) ......;
}


Statement가 close()되지 않는 또 다른 경우가 다음과 같다. 

Connection conn = null;
Statement stmt = null;
try {
conn = .....
stmt = conn.createStatement(); // ....(1)
rs = stmt.executeQuery("select a from ...");
.....
stmt = conn.createStatement(); // ....(2)
rs = stmt.executeQuery("select a from ...");
....
}
finally{
if ( stmt != null ) try{stmt.close();}catch(Exception e){}
if ( conn != null ) ....
}

두 번 stmt = conn.createStatement()를 수행함으로써, 먼저 생성된 stmt는 (2)번에 의해 그 reference 가 엎어쳐버림으로써 영원히 close()되지 않는다. 이 경우 (2)번 문장은 주석처리해야 한다. 한번 생성된 Statement로 여러번 Query를 수행하면 된다. 

Connection conn = null;
Statement stmt = null;
try {
conn = .....
stmt = conn.createStatement();
rs = stmt.executeQuery("select a from ...");
.....
// stmt = conn.createStatement(); // <--- !!!
rs = stmt.executeQuery("select a from ...");
....
}
finally{
if ( stmt != null ) try{stmt.close();}catch(Exception e){}
if ( conn != null ) ....
}


Statement뿐 아니라 PreparedStatement를 사용할 때로 마찬가지다. 
....
PreparedStatement pstmt = conn.prepareStatement("select ....");
....
이렇게 만들어진 pstmt도 반드시 pstmt.close()돼야 한다. 

예를 들면 다음과 같은 코드를 생각할 수 있다.

Connection conn = null;
try {
conn = ......;
PreparedStatement pstmt = conn.prepareStatement("select .... ?...?");
pstmt.setString(1,"xxxx");
pstmt.setString(2,"yyyy");
ResultSet rs = pstmt.executeQuery(); <--- 여기서 SQLException 이 일어나면
while(rs.next()){
....
}
rs.close();
pstmt.close(); <-- 이것을 타지 않음 !!!
}
finally{
if ( conn != null ) ......;
}

따라서 같은 맥락에서 다음과 같이 수정돼야 한다.

Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = ......;
pstmt = conn.prepareStatement("select .... ?...?");
pstmt.setString(1,"xxxx");
pstmt.setString(2,"yyyy");
ResultSet rs = pstmt.executeQuery(); <--- 여기서 SQLException 이 일어나면
while(rs.next()){
....
}
rs.close();
//pstmt.close(); 
}
finally{
if ( pstmt != null ) try {pstmt.close();}catch(Exception e){} // <-- !!!!
if ( conn != null ) ......;
}

PreparedStatement에 관련해 다음과 같은 경우도 생각할 수 있다.

Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = ......;
for(int i=0;i<10;i++){
pstmt = conn.prepareStatement("update .... ?... where id = ?"); //... (1)
pstmt.setString(1,"xxxx");
pstmt.setString(2,"id"+(i+1) );
int affected = pstmt.executeUpdate();
if ( affected == 0 ) throw new Exception("NoAffected");
else if ( affedted > 1 ) throw new Exception("TooManyAffected");
}
}
finally{
if ( pstmt != null ) try {pstmt.close();}catch(Exception e){} // ...(2)
if ( conn != null ) ......;
}

이 경우가 실제 프로젝트 performace 튜닝을 가 보면 종종 발견되는 잘못된 유형 중 하나다. 핵심은 pstmt.prepareStatement("update..."); 문장을 수행할 때마다 내부적으로 pstmt가 새롭게 할당된다는 것이다. 결국 (1)번 문장이 for loop을 돌면서 9개는 pstmt.close()가 되지 않고, 마지막 하나의 pstmt만 (2)번에서 close된다. 

따라서 다음과 같이 수정돼야 한다. 

Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = ......;
pstmt = conn.prepareStatement("update .... ?... where id = ?");
for(int i=0;i<10;i++){
pstmt.clearParameters(); 
pstmt.setString(1,"xxxx");
pstmt.setString(2,"id"+(i+1) );
int affected = pstmt.executeUpdate();
if ( affected == 0 ) throw new Exception("NoAffected");
else if ( affedted > 1 ) throw new Exception("TooManyAffected");
}
}
finally{
if ( pstmt != null ) try {pstmt.close();}catch(Exception e){}
if ( conn != null ) ......;
}

Statement나 PreparedStatement가 close()되지 않는 유형은 여러가지가 있다. 하지만 열린 Statement는 반드시 close()돼야 한다는 단순한 사실에 조금만 신경쓰면 어떻게 프로그래밍돼야 하는지 쉽게 알 수 있다.

Statement를 닫지 않는 실수를 한 코드가 400∼1000개의 전 애플리케이션을 통털어 1∼2군데에 숨어 있는 경우가 제일 찾기 어렵다. 1∼2번 Statement를 close하지 않는다고 해서 곧바로 문제가 나타나진 않기 때문이다. 

하루나 이틀 도는 며칠이 지나서야 Oracle 의 경우, "maximum open cursor exceed" 에러를 낸다. 오라클의 경우 위와 같은 에러를 conn.createStatement()시에 발생하므로(경우에 따라) 큰 문제로 이어지진 않는다. 찾아서 수정하면 그만이다. 

반면 DB2의 경우 메모리가 허용하는 한도까지 지속적인 메모리 증가를 야기한다. 급기야 "핸들(handle)이 더이상 없다", "애플리케이션 힙(heap)에 명령문을 처리하기 위해 사용 가능한 저장영역이 충분하지 않다", "유효하지 않은 명령문 핸들이나 명령문이 닫혔다" 등의 에러를 발생시킨다. 뿐만 아니라 새로운 connection을 맺는 시점에 SIGSEGV를 내면 crashing이 일어난다. java.lang.OutOfMemoryError가 발생하기 때문.

Oracle의 경우 사실 상황에 따라 심각할 수도 있다. 예를 들어 어떤 개발자가 "maximum open cursor exceed"라는 Oracle SQL 에러 메세지를 보고 자신이 코딩을 잘못한 것은 생각지 못하고 무조건 DBA에게 oraXXX.ini 파일에서 OPEN_CURSORS 값을 올려달라고 요청하고, DBA는 이를 받아들여 Default 50에서 이를 3000으로 조정한다. 여기서 문제는 좀더 깊숙히 숨겨지게 되는 셈이다. 

close()안된 Statement가 3000회에 도달하지 전까진 아무도 문제를 인식하지 못하기 때문. 하지만 Statement가 하나씩 close()되지 않은 갯수에 비례해 Oracle 엔진의 메모리 사용은 자꾸만 증가하고, 전체적인 성능저하를 야기하지만, 이를 인식하기란 막상 시스템을 오픈하고 나서 3∼지난 후에 "DB성능이 이상하네, 응답속도가 느리네" 하면서 또 한번의 "maximum open cursor exceed" 메세지를 확인하고 난 뒤의 일이 되곤 한다. 

에러없는 정상적인 로직 flow에서는 대부분 Statement가 잘 닫힌다. 하지만 이유야 어떠하든 아주 드물게 Runtime Exception이 발생해 exception throwing으로 인해 "stmt.close()"를 타지 않는 경우라면 문제는 상당히 심각하다. Statement, PreparedStatement, CallableStatement 모두 마찬가지이다. @ 
