출처 :
http://msinterdev.org/blog/archive/200804?TSSESSIONmsinterdevorgblog=7701d2cc7e6ea2d431e379532dd3888d자바서비스.넷 에 있는 예전 글 입니다.
요즘엔 iBatis 니 Spring 이니 하는 것 들을 이용하기 때문에 별도로 커넥션을 관리 할 일이 없어지긴 하고 있지만, 기본 개념을 모른체로 하다보니 잘못 된 접근을 하는 경우가 발생하여 다시 한번 점검해 봅니다.
최초작성일자: 2000/09/05 16:19:47
최근 수정일 : 2001.01.27
최근 수정일 : 2001.03.12(nested sql query issue)
최근 수정일 : 2001.03.13(transaction)
최근 수정일 : 2001.03.20(instance variables in JSP)
최근 수정일 : 2001.04.03(문맥수정)
최근 수정일 : 2002.02.06("close 할 땐 제대로..." 추가사항첨가)
최근 수정일 : 2002.02.25("transaction관련 추가")
최근 수정일 : 2002.06.11(PreparedStatement에 의한 ResultSet close 이슈)
최근 수정일 : 2002.06.18(PreparedStatement관련 추가)
최근 수정일 : 2002.12.30(Instance Variable 공유 1.2 추가)
다들 아실법한 단순한 얘깁니다만, 아직 많은 분들이 모르시는 것 같아 다시한번
정리합니다. 아래의 각각의 예제는 잘못 사용하고 계시는 전형적인 예들입니다.
1. 서블렛에서 instance variable 의 공유
1.1 서블렛에서 instance variable 의 공유 - PrintWriter -
다음과 같은 코드를 생각해 보겠습니다.
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class CountServlet extends HttpServlet {
private PrintWriter out = null; // <-------------- (1)
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException
{
res.setContentType("text/html");
out = res.getWriter();
for(int i=0;i<20;i++){
out.println("count= " + (i+1) + "<br>"); // <---- (2)
out.flush();
try{Thread.sleep(1000);}catch(Exception e){}
}
}
}
위의 CountServlet.java 를 컴파일하여 돌려 보면, 1초간격으로 일련의 숫자가 올라가는
것이 보일 겁니다.(서블렛엔진의 구현방식에 따라 Buffering 이 되어 20초가 모두 지난
후에서 퍽 나올 수도 있습니다.)
혼자서 단일 Request 를 날려 보면, 아무런 문제가 없겠지만, 이제 브라우져 창을 두개 이상
띄우시고 10초의 시간 차를 두시면서 동시에 호출해 보세요... 이상한 증상이 나타날
겁니다. 먼저 호출한 창에는 10 까지 정도만 나타나고, 10초 뒤에 호출한 창에서는 먼저
호출한 창에서 나타나야할 내용들까지 덤으로 나타나는 것을 목격할 수 있을 겁니다.
이는 서블렛의 각 호출은 Thread 로 동작하여, 따라서, 각 호출은 위의 (1) 에서 선언한
instance variable 들을 공유하기 때문에 나타나는 문제입니다.
위 부분은 다음과 같이 고쳐져야 합니다.
public class CountServlet extends HttpServlet {
//private PrintWriter out = null;
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException
{
PrintWriter out = null; // <--- 이 쪽으로 와야죠 !!!
res.setContentType("text/html");
out = res.getWriter();
for(int i=0;i<20;i++){
out.println("count= " + (i+1) + "<br>"); // <---- (2)
out.flush();
try{Thread.sleep(1000);}catch(Exception e){}
}
}
}
국내 몇몇 Servlet 관련 서적의 일부 예제들이 위와 같은 잘못된 형태로 설명한
소스코드들이 눈에 띕니다. 빠른 시일에 바로 잡아야 할 것입니다.
실제 프로젝트 환경에서 개발된 실무시스템에서도, 그러한 책을 통해 공부하신듯, 동일한
잘못된 코딩을 하고 있는 개발자들이 있습니다. 결과적으로 테스트 환경에서는 나타나지
않더니만, 막상 시스템을 오픈하고나니 고객으로 부터 다음과 같은 소리를 듣습니다.
"내 데이타가 아닌데 남의 데이타가 내 화면에 간혹 나타나요. refresh 를 누르면 또,
제대로 되구요" .....
1.2 서블렛에서 instance variable 의 공유
앞서의 경우와 의미를 같이하는데, 다음과 같이 하면 안된다는 얘기지요.
public class BadServlet extends HttpServlet {
private String userid = null;
private String username = null;
private int hitcount = 0;
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException
{
res.setContentType("text/html");
PrintWriter out = res.getWriter();
userid = request.getParameter("userid");
username = request.getParameter("username");
hitcount = hitcount + 1;
....
}
}
새로운 매 HTTP 요청마다 userid/username변수는 새롭게 할당됩니다. 문제는 그것이 특정
사용자에 한하여 그러한 것이 아니라, BadServlet의 인스턴스(instance)는 해당
웹컨테이너(Web Container)에 상에서 (예외경우가 있지만) 단 하나만 존재하고, 서로 다른
모든 사용자들의 서로 다른 모든 요청들에 대해서 동일한 userid/username 및 count 변수를
접근하게 됩니다. 따라서, 다음과 같이 메소드 안으로 끌어들여 사용하여야 함을 강조합니다.
public class BadServlet extends HttpServlet {
//private String userid = null; // <---- !!
//private String username = null; // <---- !!
private int hitcount = 0;
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException
{
res.setContentType("text/html");
PrintWriter out = res.getWriter();
String userid = request.getParameter("userid"); // <---- !!
String username = request.getParameter("username"); // <---- !!
//또한, instance 변수에 대한 접근은 적어도 아래처럼 동기화를 고려해야...
synchronized(this){ hitcount = hitcount + 1; }
....
}
}
1.3 서블렛에서 instance variable 의 공유 - DataBase Connection -
public class TestServlet extends HttpServlet {
private final static String drv = "oracle.jdbc.driver.OracleDriver";
private final static String url = "jdbc:orache:thin@210.220.251.96:1521:ORA8i";
private final static String user = "scott";
private final static String password = "tiger";
private ServletContext context;
private Connection conn = null; <--- !!!
private Statement stmt = null; <------ !!!
private ResultSet rs = null; <------ !!!
public void init(ServletConfig config) throws ServletException {
super.init(config);
context = config.getServletContext();
try {
Class.forName(drv);
}
catch (ClassNotFoundException e) {
throw new ServletException("Unable to load JDBC driver:"+ e.toString());
}
}
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException, SQLException
{
String id = req.getParameter("id");
conn = DriverManager.getConnection(url,user,password); ---- (1)
stmt = conn.createStatement(); ---------- (2)
rs = stmt.executeQuery("select .... where id = '" + id + "'"); ----- (3)
while(rs.next()) { ----------- (4)
...... --------- (5)
}
rs.close(); -------- (6)
stmt.close(); ---- (7)
conn.close(); --- (8)
.....
}
}
위에서 뭐가 잘못되었죠? 여러가지가 있겠지만, 그 중에 하나가 java.sql.Connection과
java.sql.Statment, java.sql.ResultSet을 instance variable 로 사용하고 있다는 것입니다.
이 서블렛은 사용자가 혼자일 경우는 아무런 문제를 야기하지 않습니다. 그러나 여러사람이
동시에 이 서블렛을 같이 호출해 보면, 이상한 증상이 나타날 것입니다.
그 이유는 conn, stmt, rs 등과 같은 reference 들을 instance 변수로 선언하여 두었기
때문에 발생합니다. 서블렛은 Thread로 동작하며 위처럼 instance 변수 영역에 선언해 둔
reference 들은 doGet(), doPost() 를 수행하면서 각각의 요청들이 동시에 공유하게 됩니다.
예를 들어, 두개의 요청이 약간의 시간차를 두고 비슷한 순간에 doGet() 안으로 들어왔다고
가정해 보겠습니다.
A 라는 요청이 순차적으로 (1), (2), (3) 까지 수행했을 때, B 라는 요청이 곧바로 doGet()
안으로 들어올 수 있습니다. B 역시 (1), (2), (3) 을 수행하겠죠...
이제 요청 A 는 (4) 번과 (5) 번을 수행하려 하는데, 가만히 생각해 보면, 요청B 로 인해
요청A에 의해 할당되었던 conn, stmt, rs 의 reference 들은 바뀌어 버렸습니다.
결국, 요청 A 는 요청 B 의 결과를 가지고 작업을 하게 됩니다. 반면, 요청 B 는
요청 A 의 의해 rs.next() 를 이미 수행 해 버렸으므로, rs.next() 의 결과가 이미 close
되었다는 엉뚱한 결과를 낳고 마는 거죠...
다른 쉬운 얘기로 설명해 보면, A, B 두사람이 식탁에 앉아서 각자 자신이 준비해 온 사과를
하나씩 깎아서 식탁 위의 접시에 올려 놓고 나중에 먹어려 하는 것과 동일합니다. A 라는
사람이 열심히 사과를 깎아 접시에 담아둘 때, B 라는 사람이 들어와서 A가 깎아둔 사과를
버리고 자신이 깎은 사과를 대신 접시에 담아 둡니다. 이제 A라는 사람은 자신이 깎아서
담아 두었다고 생각하는 그 사과를 접시에서 먹어버립니다. 곧이어 B라는 사람이 자신의
사과를 접시에서 먹어려 하니 이미 A 가 먹고 난 후 였습니다. 이는 접시를 두 사람이
공유하기 때문에 발생하는 문제잖습니까.
마찬가지로 서블렛의 각 Thread는 instance variable 를 공유하기 때문에 동일한 문제들을
발생하게 됩니다.
따라서 최소한 다음처럼 고쳐져야 합니다.
public class TestServlet extends HttpServlet {
private final static String drv = "...";
private final static String url = "....";
private final static String user = "...";
private final static String password = "...";
private ServletContext context;
public void init(ServletConfig config) throws ServletException {
super.init(config);
context = config.getServletContext();
try {
Class.forName(drv);
}
catch (ClassNotFoundException e) {
throw new ServletException("Unable to load JDBC driver:"+ e.toString());
}
}
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");
conn = DriverManager.getConnection(url,user,password);
stmt = conn.createStatement();
rs = stmt.executeQuery("select ..... where id = '" + id + "'");
while(rs.next()) {
......
}
rs.close();
stmt.close();
conn.close();
.....
}
}
1.4 JSP에서 Instance Variable 공유
JSP에서 아래처럼 사용하는 경우가 위의 경우와 동일한 instance 변수를 공유하는 경우가
됩니다.
---------------------------------------------------------
<%@ page session=.... import=.... contentType=........ %>
<%!
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String userid = null;
%>
<html><head></head><body>
<%
........
conn = ...
stmt = .....
uesrid = ......
%>
</body></html>
---------------------------------------------------------
마찬가지로 위험천만한 일이며, 여러 Thread 에 의해 그 값이 변할 수 있는 변수들은
<%! ... %> 를 이용하여 선언하시면 안됩니다. 이처럼 instance 변수로 사용할 것은
다음 처럼, 그 값이 변하지 않는 값이거나, 혹은 공유변수에 대한 특별한 관리를
하신 상태에서 하셔야 합니다.
<%! private static final String USERID = "scott";
private static final String PASSWORD = "tiger";
%>
JSP에서의 이와 같은 잘못된 유형도, 앞선 서블렛의 경우처럼 일부 국내 JSP관련
서적에서 발견됩니다. 해당 서적의 저자는 가능한 빨리 개정판을 내셔서 시정하셔야
할 것입니다. 해당 책은 출판사나 책의 유형, 그리고 글자체로 추정건데, 초보자가
쉽게 선택할 법한 책인 만큼 그 파급력과 영향력이 너무 큰 듯 합니다.
이와 같은 부분이 실 프로젝트에서 존재할 경우, 대부분 시스템 오픈 첫날 쯤에
문제를 인식하게 됩니다. Connection reference 가 엎어쳐지므로 Pool 에 반환이
일어나지 않게 되고, 이는 "connection pool"의 가용한 자원이 부하가 얼마 없음에도
불구하고 모자라는 현상으로 나타나며, 때론 사용자의 화면에서는 엉뚱한 다른
사람의 데이타가 나타나거나, SQLException 이 나타납니다.
NOTE: 어떻게하란 말입니까? 각 호출간에 공유되어서는 안될 변수들은 <%! ...%> 가
아니라, <% ... %> 내에서 선언하여 사용하란 얘깁니다. JSP가 Pre-compile되어
Servlet형태로 변환될 때, <%! ... %>는 서블렛의 instance variable로 구성되는 반면,
<% ... %>는 _jspService() 메소드 내의 method variable로 구성됩니다.
2. 하나의 Connection을 init()에서 미리 연결해 두고 사용하는 경우.
public class TestServlet extends HttpServlet {
private final static String drv = "oracle.jdbc.driver.OracleDriver";
private final static String url = "jdbc:orache:thin@210.220.251.96:1521:ORA8i";
private final static String user = "scott";
private final static String password = "tiger";
private ServletContext context;
private Connection conn = null; <--- !!!
public void init(ServletConfig config) throws ServletException {
super.init(config);
context = config.getServletContext();
try {
Class.forName(drv);
conn = DriverManager.getConnection(url,user,password);
}
catch (ClassNotFoundException e) {
throw new ServletException("Unable to load JDBC driver:"+ e.toString());
}
catch (SQLException e) {
throw new ServletException("Unable to connect to database:"+ e.toString());
}
}
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException, SQLException
{
Statement stmt = null;
ResultSet rs = null;
String id = req.getParameter("id");
stmt = conn.createStatement();
rs = stmt.executeQuery("select ..... where id = '" + id + "'");
while(rs.next()) {
......
}
rs.close();
stmt.close();
.....
}
public void destroy() {
if ( conn != null ) try {conn.close();}catch(Exception e){}
}
}
위는 뭐가 잘못되었을 까요? 서블렛당 하나씩 java.sql.Connection 을 init()에서 미리
맺어 두고 사용하는 구조 입니다.
얼핏 생각하면 아무런 문제가 없을 듯도 합니다. doGet() 내에서 별도의 Statement와
ResultSet 을 사용하고 있으니, 각 Thread는 자신만의 Reference를 갖고 사용하게 되니까요.
이 구조는 크게 세가지의 문제를 안고 있습니다. 하나는 DB 연결자원의 낭비를 가져오며,
두번째로 수많은 동시사용자에 대한 처리한계를 가져오고, 또 마지막으로 insert, update,
delete 와 같이 하나 이상의 SQL문장을 수행하면서 단일의 Transaction 처리를 보장받을
수 없다는 것입니다.
1) DB 자원의 낭비
위의 구조는 서블렛당 하나씩 java.sql.Connection 을 점유하고 있습니다. 실 프로젝트에서
보통 서블렛이 몇개나 될까요? 최소한 100 개에서 400개가 넘어 갈 때도 있겠죠?
그럼 java.sql.Connection에 할당 되어야 할 "DB연결갯수"도 서블렛 갯수 많큼 필요하게
됩니다. DB 연결 자원은 DB 에서 설정하기 나름이지만, 통상 maximum 을 셋팅하기
마련입니다. 그러나 아무런 요청이 없을 때도 400 여개의 DB연결이 연결되어 있어야 한다는
것은 자원의 낭비입니다.
2) 대량의 동시 사용자 처리 불가.
또한, 같은 서블렛에 대해 동시에 100 혹은 그 이상의 요청이 들어온다고 가정해 보겠습
니다. 그럼 같은 java.sql.Connection 에 대해서 각각의 요청이 conn.createStatement() 를
호출하게 됩니다.
문제는 하나의 Connection 에 대해 동시에 Open 할 수 있는 Statement 갯수는 ( 이 역시
DB 에서 셋팅하기 나름이지만 ) maximum 제한이 있습니다. Oracle 의 경우 Default는 50
입니다. 만약 이 수치 이상을 동시에 Open 하려고 하면 "maximum open cursor exceed !"
혹은 "Limit on number of statements exceeded"라는 SQLExceptoin 을 발생하게 됩니다.
예를 들어 다음과 같은 프로그램을 실행시켜 보세요.
public class DbTest {
public static void main(String[] args) throws Exception {
Class.forName("jdbc driver...");
Connection conn = DriverManager.getConnection("url...","id","password");
int i=0;
while(true) {
Statement stmt = conn.createStatement();
System.out.println( (++i) + "- stmt created");
}
}
}
과연 몇개 까지 conn.createStement() 가 수행될 수 있을까요? 이는 DB에서 설정하기 나름
입니다. 중요한 것은 그 한계가 있다는 것입니다.
또한 conn.createStatement() 통해 만들어진 stmt 는 java.sql.Connection 의 자원이기
때문에 위처럼 stmt 의 reference 가 없어졌다고 해도 GC(Garbage Collection)이 되지
않습니다.
3) Transaction 중복현상 발생
예를 들어 다음과 같은 서비스가 있다고 가정해 보겠습니다.
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException, SQLException
{
Statement stmt = null;
String id = req.getParameter("id");
try {
conn.setAutoCommit(false);
stmt = conn.createStatement();
stmt.executeUpdate("update into XXXX..... where id = " + id + "'");
stmt.executeUpdate("delete from XXXX..... where id = " + id + "'");
stmt.executeUpdate(".... where id = " + id + "'");
stmt.executeUpdate(".... where id = " + id + "'");
conn.commit();
}
catch(Exception e){
try{conn.rollback();}catch(Exception e){}
}
finally {
if ( stmt != null ) try{stmt.close();}catch(Exception e){}
conn.setAutoCommit(true);
}
.....
}
아무런 문제가 없을 듯도 합니다. 그러나 위의 서비스를 동시에 요청하게 되면, 같은
java.sql.Connection 을 갖고 작업을 하고 있으니 Transaction 이 중첩되게 됩니다.
왜냐면, conn.commit(), conn.rollback() 과 같이 conn 이라는 Connection 에 대해서
Transaction 이 관리되기 때문입니다. 요청 A 가 총 4개의 SQL문장 중 3개를 정상적으로
수행하고 마지막 4번째의 SQL문장을 수행하려 합니다. 이 때 요청 B가 뒤따라 들어와서
2개의 SQL 문장들을 열심히 수행했습니다. 근데, 요청 A에 의한 마지막 SQL 문장
수행중에 SQLException 이 발생했습니다. 그렇담 요청 A 는 catch(Exception e) 절로
분기가 일어나고 conn.rollback() 을 수행하여 이미 수행한 3개의 SQL 수행들을 모두
rollback 시킵니다. 근데,,, 문제는 요청 B 에 의해 수행된 2개의 SQL문장들도 같이
싸잡아서 rollback() 되어 버립니다. 왜냐면 같은 conn 객체니까요. 결국, 요청B 는
영문도 모르고 마지막 2개의 SQL문장만 수행한 결과를 낳고 맙니다.
따라서 정리하면, Connection, Statement, ResultSet 는 doGet() , doPost() 내에서
선언되고 사용되어져야 합니다.
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException, SQLException
{
Connection conn = null; <----- 이곳으로 와야죠..
Statement stmt = null; <-------
ResultSet rs = null; <---------
.....
}
3. Exception 이 발생했을 때도 Connection 은 닫혀야 한다 !!
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException, SQLException
{
String id = req.getParameter("id");
Connection conn = DriverManager.getConnection("url...","id","password");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("ssselect * from XXX where id = '" + id + "'");
while(rs.next()) {
......
}
rs.close();
stmt.close();
conn.close();
.....
}
위에선 뭐가 잘못되었을까요? 네, 사실 특별히 잘못된 것 없습니다. 단지 SQL문장에 오타가
있다는 것을 제외하곤.... 근데, 과연 그럴까요?
SQLException 이라는 것은 Runtime 시에 발생합니다. DB 의 조건이 맞지 않는다거나
개발기간 중에 개발자의 실수로 SQL문장에 위처럼 오타를 적을 수도 있죠.
문제는 Exception 이 발생하면 마지막 라인들 즉, rs.close(), stmt.close(), conn.close()
가 수행되지 않는다는 것입니다.
java.sql.Connection 은 reference 를 잃더라도 JVM(Java Virtual Machine)의 GC(Garbage
Collection) 대상이 아닙니다. 가뜩이나 모자라는 "DB연결자원"을 특정한 어플리케이션이
점유하고 놓아 주지 않기 때문에 얼마안가 DB Connection 을 더이상 연결하지 못하는
사태가 발생합니다.
따라서 다음처럼 Exception 이 발생하든 발생하지 않든 반드시 java.sql.Connection 을
close() 하는 로직이 꼭(!) 들어가야 합니다.
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 = DriverManager.getConnection("url...","id","password");
stmt = conn.createStatement();
rs = stmt.executeQuery("sselect * from XXX where id = '" + id + "'");
while(rs.next()) {
......
}
rs.close();
stmt.close();
}
finally {
if ( conn != null ) try {conn.close();}catch(Exception e){}
}
.....
}
참고로, 실프로젝트의 진단 및 튜닝을 나가보면 처음에는 적절한 응답속도가 나오다가
일정한 횟수 이상을 호출하고 난 뒤부터 엄청 응답시간이 느려진다면, 십중팔구는 위처럼
java.sql.Connection 을 닫지 않아서 생기는 문제입니다.
가용한 모든 Connection 을 연결하여 더이상 연결시킬 Connection 자원을 할당할 수 없을
때, 대부분 timewait 이 걸리기 때문입니다. 일단 DB관련한 작업이 들어오는 족족
timewait에 빠질 경우, "어플리케이션서버"에서 동시에 처리할 수 있는 최대 갯수만큼
호출이 차곡차곡 쌓이는 건 불과 몇분 걸리지 않습니다. 그 뒤부터는 애궂은 dummy.jsp
조차 호출이 되지 않게 되고, 누군가는 "시스템 또 죽었네요"라며 묘한 웃음을 짓곤
하겠죠....
4. Connection 뿐만 아니라 Statement, ResultSet 도 반드시 닫혀야 한다 !!
4.1 3번의 예제에서 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에 반환되어 지게 되는데, 결국 reference가 사리지지 않기 때문에 GC시점에
자동 close되지 않게 됩니다.
특정 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)번글에서 이미 언급드렸습니다. 보다 자세한 기술적 내용은 아래의 글을
참고하세요.
Connection/Statement 최대 동시 Open 수
http://www.javaservice.net/~java/bbs/read.cgi?m=devtip&b=jdbc&c=r_p&n=972287002
따라서 또다시 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 = ...<getConnection()>...; // (편의상 생략합니다.)
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 ) ...<releaseConnection()>...; // (편의상 생략)
}
.....
}
4.2 사실 위와 같은 구조에서, java.sql.Statement의 쿼리에 의한 ResultSet의 close()에
대한 것은 그리 중요한 것은 아닙니다. 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 = ...<getConnection()>...;
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 ) ...<releaseConnection()>...;
}
.....
}
4.3 가장 대표적으로 잘못 프로그래밍하고 있는 예를 들라면 다음과 같은 유형입니다.
Connection conn = null;
try {
conn = ...<getConnection()>....;
Statement stmt = conn.createStatement();
stmt.executeUpdate("...."); <--- 여기서 SQLException 이 일어나면...
.....
.....
..... <-- 만약,이쯤에서 NullPointerException 이 일어나면 ?
.....
stmt.close(); <-- 이것을 타지 않음 !!!
}
finally{
if ( conn != null ) ...<releaseConnection()>...;
}
4.4 Statement 가 close() 되지 않는 또 하나의 경우가 다음과 같은 경우입니다.
Connection conn = null;
Statement stmt = null;
try {
conn = .....
stmt = conn.createStatement(); // ....(1)
rs = stmt.executeQuery("select a from ...");
.....
rs.close();
stmt = conn.createStatement(); // ....(2)
rs = stmt.executeQuery("select b from ...");
....
}
finally{
if ( rs != null ) try {rs.close();}catch(Exception e){}
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(); // ....(1)
rs = stmt.executeQuery("select a from ...");
.....
rs.close();
// stmt = conn.createStatement(); // <--- (2) !!!
rs = stmt.executeQuery("select b from ...");
....
}
finally{
if ( rs != null ) try {rs.close();}catch(Exception e){}
if ( stmt != null ) try{stmt.close();}catch(Exception e){}
if ( conn != null ) ....
}
4.5 Statement 뿐만 아니라 PreparedStatement 를 사용할 때로 마찬가지 입니다.
....
PreparedStatement pstmt = conn.prepareStatement("select ....");
....
이렇게 만들어진 pstmt 도 반드시 pstmt.close() 되어야 합니다.
예를 들면, 다음과 같은 코드를 생각할 수 있습니다.
Connection conn = null;
try {
conn = ...<getConnection()>...;
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 ) ...<releaseConnection()>...;
}
따라서 같은 맥락으로 다음과 같이 고쳐져야 합니다.
Connection conn = null;
PreparedStatement pstmt = null; // <-------- !!
ResultSet rs = null;
try {
conn = ...<getConnection()>...;
pstmt = conn.prepareStatement("select .... ?...?");
pstmt.setString(1,"xxxx");
pstmt.setString(2,"yyyy");
rs = pstmt.executeQuery(); <--- 여기서 SQLException 이 일어나더라도...
while(rs.next()){
....
}
//rs.close();
//pstmt.close();
}
finally{
if ( rs != null ) try {rs.close();}catch(Exception e){}
if ( pstmt != null ) try {pstmt.close();}catch(Exception e){} // <-- !!!!
if ( conn != null ) ...<releaseConnection()>...;
}
4.6 PreparedStatement 에 관련해서 다음과 같은 경우도 생각할 수 있습니다.
4.6.1 앞서의 4.4에서 Statement를 createStatement() 연거푸 두번 생성하는 것과
동일하게 PreparedStatement에서도 주의하셔야 합니다. 예를 들면 다음과 같습니다.
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = .....
pstmt = conn.prepareStatement("select a from ..."); // ....(1)
rs = pstmt.executeQuery();
.....
rs.close();
pstmt = conn.prepareStatement("select b from ..."); // <--- (2) !!!
rs = pstmt.executeQuery();
....
}
finally{
if ( rs != null ) try {rs.close();}catch(Exception e){}
if ( pstmt != null ) try{pstmt.close();}catch(Exception e){} // <--- (3)
if ( conn != null ) ...<releaseConnection()>...;
}
Statement의 인스턴스는 conn.createStatement() 시에 할당되어 지는 반면,
PreparedStatement는 conn.prepareStatement("..."); 시에 할당되어 집니다. 위의 경우에서
설령 마지막 finally 절에서 pstmt.close() 를 하고 있기는 하지만, (1)번에서 할당되어진
pstmt 는 (2)에서 엎어쳤기 때문에 영원히 close() 되지 않게 됩니다. 따라서, 다음과
같이 코딩되어야 한다는 것은 자명합니다.
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = .....
pstmt = conn.prepareStatement("select a from ..."); // ....(1)
rs = pstmt.executeQuery();
.....
rs.close();
pstmt.close(); // <------- !!!!! 이처럼 여기서 먼저 close() 해야지요.
pstmt = conn.prepareStatement("select b from ..."); // <--- (2)
rs = pstmt.executeQuery();
....
}
finally{
if ( rs != null ) try {rs.close();}catch(Exception e){}
if ( pstmt != null ) try{pstmt.close();}catch(Exception e){} // <--- (3)
if ( conn != null ) ...<releaseConnection()>...;
}
혹은 다음과 같이 서로 다른 두개의 PreparedStatement를 이용할 수도 있습니다.
Connection conn = null;
PreparedStatement pstmt1 = null;
ResultSet rs1 = null;
PreparedStatement pstmt2 = null;
ResultSet rs2 = null;
try {
conn = .....
pstmt1 = conn.prepareStatement("select a from ..."); // ....(1)
rs1 = pstmt1.executeQuery();
.....
pstmt2 = conn.prepareStatement("select b from ..."); // <--- (2)
rs2 = pstmt2.executeQuery();
....
}
finally{
if ( rs1 != null ) try {rs1.close();}catch(Exception e){}
if ( pstmt1 != null ) try{pstmt1.close();}catch(Exception e){} // <--- (3)
if ( rs2 != null ) try {rs2.close();}catch(Exception e){}
if ( pstmt2 != null ) try{pstmt2.close();}catch(Exception e){} // <--- (4)
if ( conn != null ) ...<releaseConnection()>...;
}
4.6.2 아래는 앞서의 4.6.1과 같은 맥락인데, for loop 안에서 사용된 경우입니다.
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = ...<getConnection()>...;
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 ) ...<releaseConnection()>...;
}
이 경우가 실제 프로젝트 performace 튜닝을 가 보면 종종 발견되는 잘못된
유형 중의 하나입니다. 핵심은 pstmt.prepareStatement("update..."); 문장을
수행할 때 마다 내부적으로 pstmt 가 새로 할당된다는 것에 있습니다.
결국, (1)번 문장이 for loop 을 돌면서 9개는 pstmt.close()가 되지 않고, 마지막
하나의 pstmt 만 finally 절의 (2)번에서 close 되지요...
따라서 다음처럼 고쳐져야 합니다.
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = ...<getConnection()>...;
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 ) ...<releaseConnection()>...;
}
PreparedStatement 라는 것이, 한번 파싱하여 동일한 SQL문장을 곧바로 Execution할 수
있는 장점이 있는 것이고, 궁극적으로 위와 같은 경우에 효과를 극대화 할 수 있는
것이지요.
어느 개발자의 소스에서는 위의 경우를 다음과 같이 for loop 안에서 매번
conn.prepareStatement(...)를 하는 경우를 보았습니다.
Connection conn = null;
try {
conn = ...<getConnection()>...;
for(int i=0;i<10;i++) {
PreparedStatement pstmt = null;
try{
pstmt = conn.prepareStatement("update .... ?... where id = ?");
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){}
}
}
}
finally{
if ( conn != null ) ...<releaseConnection()>...;
}
위 경우는 장애관점에서 보면 사실 별 문제는 없습니다. 적어도 닫을 건 모두 잘 닫고
있으니까요. 단지 효율성의 문제가 대두될 수 있을 뿐입니다.
4.7 생각해 보면, Statement 나 PreparedStatement 가 close() 되지 않는 유형은
여러가지가 있습니다. 그러나 열려진 Statement는 반드시 close()되어야 한다라는
단순한 사실에 조금만 신경쓰시면 어떻게 프로그래밍이 되어야 하는지 쉽게
감이 오실 겁니다. 단지 신경을 안쓰시니 문제가 되는 것이지요...
Statement 를 닫지 않는 실수를 한 코드가 400-1000여개의 전 어플리케이션을 통털어
한두군데에 숨어 있는 경우가 제일 찾아내기 어렵습니다. 한두번 Statement 를
close 하지 않는다고 하여 곧바로 문제로 나타나지 않기 때문입니다.
하루나 이틀, 혹은 며칠지나서야, Oracle 의 경우, "maximum open cursor exceed"
에러를 내게 됩니다. oracle 의 경우, 위와 같은 에러를 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-4일이 지난 후에 "DB성능이 이상하네,
응답속도가 느리네" 하면서 또 한번의 "maximum open cursor exceed" 메세지를
확인하고 난 뒤의 일이 되곤 합니다.
에러가 없는 정상적인 로직 flow 에서는 대부분 Statement가 잘 닫힐 겁니다. 그러나,
어떤 이유에서건 아주 드물게 Runtime Exception 이 발생하여 exception throwing으로
인해 "stmt.close()"를 타지 않는 경우가 제일 무섭지요. 정말 무섭지요...
Statement, PreparedStatement, CallableStatement 모두 마찬가지 입니다.
5. close() 를 할 땐 제대로 해야 한다!!
5.1 다음과 같은 프로그램 형식을 생각할 수 있습니다.
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try{
conn = ...<getConnection()>...; //.......(1)
stmt = conn.createStatement(); //.............(2)
rs = stmt.executeQuery("select ....."); // .....(3)
while(rs.next()){
......
}
}
finally {
try {
rs.close(); //........(4)
stmt.close(); //......(5)
...<releaseConneciton()>...; //......(6)
}catch(Exception e){}
}
위에선 뭐가 잘못되었을까요? 다 제대로 한듯 한데....
finally 절에서 rs, stmt, conn 을 null check 없이, 그리고 동일한 try{}catch 절로
실행하고 있습니다.
예를 들어, (1), (2) 번을 거치면서 conn 과 stmt 객체까지는 제대로 수행되었으나
(3)번 Query문장을 수행하는 도중에 SQLException 이 발생할 수 있습니다. 그러면,
finally 절에서 (4) 번 rs.close()를 수행하려 합니다. 그러나, executeQuery()가
실패 했기 때문에 rs 의 reference 는 null 이므로 rs.close() 시에
NullPointerException 이 발생합니다. 결국 정작 반드시 수행되어야할 (5)번, (6)번이
실행되지 않습니다. 따라서 반드시(!) 다음과 같은 형식의 코딩이 되어야 합니다.
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try{
conn = ...<getConnection()>...;
stmt = conn.createStatement();
rs = stmt.executeQuery("select .....");
while(rs.next()){
......
}
}
catch(Exception e){
....
}
finally {
if ( rs != null ) try{rs.close();}catch(Exception e){}
if ( stmt != null ) try{stmt.close();}catch(Exception e){}
if ( conn != null ) ...<releaseConnection()>...;
}
같은 맥락으로 PreparedStatement 를 사용할 경우도, 다음과 같은 코딩은 잘못되었습니다.
Connection conn = null;
PreparedStatement pstmt = null;
try{
conn = ...<getConnection()>...; //.......(1)
pstmt = conn.prepareStatement("ddelete from EMP where empno=7942"); //...(2)
int k = pstmt.executeUpdate(); // .....(3)
......
}
finally {
try {
pstmt.close(); //......(4)
...<releaseConneciton()>...; //......(5)
}catch(Exception e){}
}
왜냐면, SQL문장에 오타가 있었고, 이는 prepareStatement("ddelete..")시점에 에러가
발생하여 pstmt 가 여전히 null 인 상태로 finally 절로 분기되기 때문인 것이죠.
5.2.0 앞서 4.2에서도 언급했지만, java.sql.Statement의 executeQuery()에 의한 ResultSet은
Statement 가 close 될때 자원이 같이 해제되므로 다음과 같이 하여도 그리 문제가 되진
않습니다.
Connection conn = null;
Statement stmt = null;
//ResultSet rs = null;
try{
conn = ...<getConnection()>...;
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select .....");
while(rs.next()){
......
}
rs.close(); // <---- !!!
}
catch(Exception e){
....
}
finally {
//if ( rs != null ) try{rs.close();}catch(Exception e){}
if ( stmt != null ) try{stmt.close();}catch(Exception e){}
if ( conn != null ) ...<releaseConnection()>...;
}
5.2.1 그러나 PreparedStatement에 의한 ResultSet close()는 얘기가 다를 수 있습니다.
(2002.06.11 추가 사항)
많은 분들이, "아니 설령 ResultSet를 close하지 않았더라도 Statement/PreparedStatement를
close하면 함께 ResultSet로 close되는 것 아니냐, JDBC 가이드에서도 그렇다고
나와 있다, 무슨 개뿔같은 소리냐?" 라구요.
그러나, 한가지 더 이해하셔야 할 부분은, 웹스피어, 웹로직과 같은 상용 웹어플리케이션
서버들은 성능향상을 위해 PreparedStatement 및 심지어 Statement에 대한 캐싱기능을
제공하고 있습니다. pstmt.close() 를 하였다고 하여 정말 해당 PreparedStatement가
close되는 것이 아니라, 해당 PreparedeStatement가 생겨난 java.sql.Connection당 지정된
개수까지 웹어플리케이션서버 내부에서 reference가 사라지지 않고 캐시로 남아 있게 됩니다.
결국, 연관된 ResultSet 역시 close()가 되지 않은 채 남아있게 되는 것이지요. 명시적인
rs.close()를 타지 않으면, 웹어플리케이션서버의 JVM내부 힙영역 뿐만아니라, 쿼리의 결과로
데이타베이스에서 임시로 만들어진 메모리데이타가 사라지지 않는 결과를 낳게 됩니다.
특히 ResultSet이 닫히지 않으면 DB에서의 CURSOR가 사라지지 않습니다.
또한, CURSOR를 자동으로 닫거나 닫지 않는 등의 옵션은 WAS마다 WAS의 DataSource설정에서
CORSOR 핸들링에 대한 별도의 옵션을 제공하게 되며 특히 아래 [항목6]에서 다시 언급할
nested sql query 처리시에 민감한 반응을 하게 됩니다.
따라서, 앞서의 코딩 방법을 반드시 다음처럼 ResultSet도 close하시기를 다시금 정정하여
권장 드립니다.
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null; // <---- !!!
try{
conn = ...<getConnection()>...;
pstmt = conn.prepareStatement("select .....");
rs = pstmt.executeQuery(); // <----- !!!
while(rs.next()){
......
}
//rs.close(); // <---- !!!
}
catch(Exception e){
....
}
finally {
if ( rs != null ) try{rs.close();}catch(Exception e){} // <---- !!!
if ( pstmt != null ) try{pstmt.close();}catch(Exception e){}
if ( conn != null ) ...<releaseConnection()>...;
}
PS: 웹어플리케이션서버에서의 PreparedStatement 캐싱기능에 관한 부분은 아래의 글들을
참조하세요.
Connection Pool & PreparedStatement Cache Size
http://www.javaservice.net/~java/bbs/read.cgi?m=appserver&b=was&c=r_p&n=995572195
WebLogic에서의 PreparedStatement Cache -서정희-
http://www.javaservice.net/~java/bbs/read.cgi?m=dbms&b=jdbc&c=r_p&n=1023286823
5.3 간혹, 다음과 같은 코딩은 문제를 야기하지 않을 것이라고 생각하는 분들이 많습니다.
finally{
try{
if ( stmt != null ) stmt.close();
if ( conn != null ) conn.close();
}catch(Exception e){}
}
저명한 많은 책에서도 위처럼 코딩되어 있는 경우를 종종봅니다. 맞습니다. 특별히 문제성이
있어보이지는 않습니다. 그러나, 간혹 웹어플리케이션서버의 Connection Pool과 연계하여
사용할 땐 때론 문제가 될 때도 있습니다. 즉, 만약, stmt.close()시에 Exception이 throw
되면 어떻게 되겠습니까?
아니 무슨 null 체크까지 했는데, 무슨 Exception이 발생하느냐고 반문할 수도 있지만,
오랜 시간이 걸리는 SQL JOB에 빠져 있다가 Connection Pool의 "Orphan Timeout"이 지나
자동으로 해당 Connection을 Pool에 돌려보내거나 혹은 특별한 marking처리를 해 둘 수
있습니다. 이 경우라면 stmt.close()시에 해당 웹어플리케이션서버에 특화된 Exception이
발생하게 됩니다. 그렇게 되면 conn.close()를 타지 못하게 되는 사태가 벌어집니다.
따라서, 앞서 하라고 권장한 형식으로 코딩하세요.
6. Nested (Statemet) SQL Query Issue !!
6.1 아래와 같은 코딩을 생각해 보겠습니다.
Connection conn = null;
Statement stmt = null;
try{
conn = ...<getConnection()>...;
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select deptno ...where id ='"+ id +"'");
while(rs.next()){
String deptno = rs.getString("deptno");
stmt.executeUpdate(
"update set dept_name = ... where deptno = '"+ deptno +"'"
);
......
}
rs.close();
}
catch(Exception e){
....
}
finally {
if ( stmt != null ) try{stmt.close();}catch(Exception e){}
if ( conn != null ) ...<releaseConnection()>...;
}
위 코드는 사실 실행하자 말자 다음과 같은 에러를 만나게 됩니다.
DB2 : -99999: [IBM][CLI Driver] CLI0115E 커서 상태가 유효하지 않습니다.
SQLSTATE=24000
Oracle :
에러는 두번째 while(rs.next()) 시에 발생합니다. 왜냐면, Statement가 nested하게
실행된 executeUpdate() 에 의해 엎어쳐 버렸기 때문입니다. ResultSet 은
Statement와 밀접하게 관련이 있습니다. ResultSet은 자료를 저장하고 있는 객체가
아니라, 데이타베이스와 step by step으로 상호 통신하는 Interface 일 뿐이기 때문입니다.
따라서 위 코드는 다음처럼 바뀌어져야 합니다.
Connection conn = null;
Statement stmt1 = null;
Statement stmt2 = null;
try{
conn = ...<getConnection()>...;
stmt1 = conn.createStatement();
stmt2 = conn.createStatement();
ResultSet rs = stmt1.executeQuery("select deptno ...where id ='"+ id +"'");
while(rs.next()){
String deptno = rs.getString("deptno");
stmt2.executeUpdate(
"update set dept_name = ... where deptno = '"+ deptno +"'"
);
......
}
rs.close();
}
catch(Exception e){
....
}
finally {
if ( stmt1 != null ) try{stmt1.close();}catch(Exception e){}
if ( stmt2 != null ) try{stmt2.close();}catch(Exception e){}
if ( conn != null ) ...<releaseConnection()>...;
}
즉, ResultSet에 대한 fetch 작업이 아직 남아 있는 상태에서 관련된 Statement를
또다시 executeQuery()/executeUpdate() 를 하면 안된다는 것입니다.
PS: IBM WebSphere 환경일 경우, 아래의 글들을 추가로 확인하시기 바랍니다.
349 Re: Function sequence error (Version 3.x)
http://www.javaservice.net/~java/bbs/read.cgi?m=appserver&b=was&c=r_p&n=991154615
486 WAS4.0x: Function sequence error 해결
http://www.javaservice.net/~java/bbs/read.cgi?m=appserver&b=was&c=r_p&n=1015345459
7. executeUpdate() 의 결과를 비즈니스 로직에 맞게 적절히 활용하라.
7.1 아래와 같은 코딩을 생각해 보겠습니다.
public void someMethod(String empno) throws Exception {
Connection conn = null;
Statement stmt = null;
try{
conn = ...<getConnection()>...;
stmt = conn.createStatement();
stmt.executeUpdate(
"UPDATE emp SET name='이원영' WHERE empno = '" + empno + "'"
);
}
finally {
if ( stmt != null ) try{stmt.close();}catch(Exception e){}
if ( conn != null ) ...<releaseConnection()>...;
}
}
사실 흔히들 이렇게 하십니다. 별로 잘못된 것도 없어보입니다. 근데, 만약
DB TABLE에 해당 empno 값이 없으면 어떻게 될까요? SQLException 이 발생
하나요? 그렇지 않죠? 아무런 에러없이 그냥 흘러 내려 옵니다. 그러면, Update를
하러 들어 왔는데, DB에 Update할 것이 없었다면 어떻게 해야 합니까? 그냥 무시하면
되나요? 안되죠.. 따라서, 다음 처럼, 이러한 상황을 이 메소드를 부른 곳으로
알려 줘야 합니다.
public void someMethod(String empno) throws Exception {
Connection conn = null;
Statement stmt = null;
try{
conn = ...<getConnection()>...;
stmt = conn.createStatement();
int affected = stmt.executeUpdate(
"UPDATE emp SET name='이원영' WHERE empno = '" + empno + "'"
);
if ( affected == 0 ) throw new Exception("NoAffectedException");
else if ( affected > 1 ) throw new Exception("TooManyAffectedException");
}
finally {
if ( stmt != null ) try{stmt.close();}catch(Exception e){}
if ( conn != null ) ...<releaseConnection()>...;
}
}
물론 이러한 부분들은 해당 비즈니스로직이 뭐냐에 따라서 다릅니다. 그것을 무시케
하는 것이 비즈니스 로직이었다면 그냥 무시하시면 되지만, MIS 성 어플리케이션의
대부분은 이처럼 update 나 delete 쿼리의 결과에 따라 적절한 처리를 해 주어야
할 것입니다.
8. Transaction 처리를 할 땐 세심하게 해야 한다.
단, 아래는 웹어플리케이션서버의 JTA(Java Transaction API)기반의 트렌젝션처리가 아닌,
java.sql.Connection에서의 명시적인 conn.setAutoCommit(false) 모드를 통한 트렌젝션처리에
대해서 언급 하고 있습니다.
8.1 Non-XA JDBC Transaction(명시적인 java.sql.Connection/commit/rollback)
Connection conn = null;
Statement stmt = null;
try{
conn = ...<getConnection()>...;
stmt = conn.createStatement();
stmt.executeUpdate("UPDATE ...."); // -------- (1)
stmt.executeUpdate("DELETE ...."); // -------- (2)
stmt.executeUpdate("INSERT ...."); // -------- (3)
}
finally {
if ( stmt != null ) try{stmt.close();}catch(Exception e){}
if ( conn != null ) ...<releaseConnection()>...;
}
위와 같은 코딩은 아무리 비즈니스적 요구가 간소하더라도, 실 프로젝트에서는
있을 수가 없는 코딩입니다.(JTS/JTA가 아닌 명시적인 Transaction 처리시에..)
다들 아시겠지만, (1), (2) 번까지는 정상적으로 잘 수행되었는데, (3)번문장을
수행하면서 SQLException 이 발생하면 어떻게 되나요? (1),(2)은 이미 DB에 반영된
채로 남아 있게 됩니다. 대부분의 비즈니스로직은 그렇지 않았을 겁니다.
"(1),(2),(3)번이 모두 정상적으로 수행되거나, 하나라도 잘못되면(?) 모두 취소
되어야 한다"
가 일반적인 비즈니스적 요구사항이었을 겁니다. 따라서, 다음처럼 코딩 되어야
합니다.
Connection conn = null;
Statement stmt = null;
try{
conn = ...<getConnection()>...;
conn.setAutoCommit(false);
stmt = conn.createStatement();
stmt.executeUpdate("UPDATE ...."); // -------- (1)
stmt.executeUpdate("DELETE ...."); // -------- (2)
stmt.executeUpdate("INSERT ...."); // -------- (3)
conn.commit(); // <-- 반드시 try{} 블럭의 마지막에 와야 합니다.
}
catch(Exception e){
if ( conn != null ) try{conn.rollback();}catch(Exception ee){}
// error handling if you want
throw e; // <--- 필요한 경우, 호출한 곳으로 Exception상황을 알려줄
// 수도 있습니다
}
finally {
if ( stmt != null ) try{stmt.close();}catch(Exception e){}
// in some connection pool, you have to reset commit mode to "true"
if ( conn != null ) ...<releaseConnection()>...;
}
8.2 auto commit mode 를 "false"로 셋팅하여 명시적인 Transaction 관리를 할 때,
정말 조심해야 할 부분이 있습니다. 예를 들면 다음과 같은 경우입니다.
Connection conn = null;
Statement stmt = null;
try{
conn = ...<getConnection()>...;
conn.setAutoCommit(false);
stmt = conn.createStatement();
stmt.executeUpdate("UPDATE ...."); // ----------------------- (1)
ResultSet rs = stmt.executeQuery("SELECT ename ..."); // ---- (2)
if ( rs.next() ) {
conn.commit(); // ------------------- (3)
}
else {
conn.rollback(); // ----------------- (4)
}
}
finally {
if ( rs != null ) try{rs.close();}catch(Exception e){}
if ( stmt != null ) try{stmt.close();}catch(Exception e){}
// in some connection pool, you have to reset commit mode to "true"
if ( conn != null ) ...<releaseConnection()>...;
}
코드가 왜 위처럼 됐는지는 저도 모르겠습니다. 단지 비즈니스가 그러했나보죠..
문제는 만약, (2)번 executeQuery()문장을 수행하면서 SQLException 이나 기타의
RuntimeException 이 발생할 때 입니다.
commit() 이나 rollback()을 타지 않고, finally 절로 건너 뛰어 Statement를
닫고, connection 은 반환됩니다. 이때, commit() 이나 rollback()이 되지 않은채
(1)번 UPDATE 문이 수행된채로 남아 있게 됩니다. 이는 DB LOCK을 점유하게
되고, 경우에 따라 다르겠지만, 다음번 요청시에 DB LOCK으로 인한 hang현상을
초래할 수도 있습니다.
일단 한두개의 어플리케이션이 어떠한 이유였든, DB Lock 을 발생시키면, 해당
DB에 관련된 어플리케이션들이 전부 응답이 없게 됩니다. 이 상황이 조금만
지속되면, 해당 waiting 을 유발하는 요청들이 어플리케이션서버의 최대 동시
접속처리수치에 도달하게 되고, 이는 전체 시스템의 hang현상으로 이어지게
되는 것이죠..
따라서, 비즈니스 로직이 어떠했든, 반드시 지켜져야할 사항은 다음과 같습니다.
"conn.setAutoComm(false); 상태에서 한번 실행된 Update성 SQL Query는 이유를
막론하고 어떠한 경우에도 commit() 이나 rollback() 되어야 한다."
위의 경우라면 다음처럼 catch 절에서 rollback 시켜주는 부분이 첨가되면 되겠네요.
Connection conn = null;
Statement stmt = null;
try{
conn = ...<getConnection()>...;
conn.setAutoCommit(false);
stmt = conn.createStatement();
stmt.executeUpdate("UPDATE ....");
ResultSet rs = stmt.executeQuery("SELECT ename ...");
if ( rs.next() ) {
conn.commit();
}
else {
conn.rollback();
}
}
catch(Exception e){ // <---- !!!!!
if ( conn != null ) try{conn.rollback();}catch(Exception ee){}
throw e;
}
finally {
if ( rs != null ) try{rs.close();}catch(Exception e){}
if ( stmt != null ) try{stmt.close();}catch(Exception e){}
// in some connection pool, you have to reset commit mode to "true"
if ( conn != null ) ...<releaseConnection()>...;
}
8.3 모든 경우의 수를 생각하라.
다음과 같은 경우를 생각해 보겠습니다.
Connection conn = null;
Statement stmt = null;
try{
conn = ...<getConnection()>...;
conn.setAutoCommit(false);
stmt = conn.createStatement();
stmt.executeUpdate("UPDATE ....");
String idx = name.substring(3);
ResultSet rs = stmt.executeQuery("SELECT ename ... where idx=" + idx);
if ( rs.next() ) {
.....
}
rs.close(); rs = null;
stmt.executeUpdate("UPDATE ....");
conn.commit();
}
catch(SQLException e){
if ( conn != null ) try{conn.rollback();}catch(Exception ee){}
throw e;
}
finally {
if ( rs != null ) try{rs.close();}catch(Exception e){}
if ( stmt != null ) try{stmt.close();}catch(Exception e){}
// in some connection pool, you have to reset commit mode to "true"
if ( conn != null ) ...<releaseConnection()>...;
}
잘 찾아 보세요. 어디가 잘못되었습니까? 잘 안보이시죠?
Connection conn = null;
Statement stmt = null;
try{
conn = ...<getConnection()>...;
conn.setAutoCommit(false);
stmt = conn.createStatement();
stmt.executeUpdate("UPDATE ...."); //---- (1)
String idx = name.substring(3); //<------ (2) NullPointerExceptoin ?
ResultSet rs = stmt.executeQuery("SELECT ename ... where idx=" + idx);
if ( rs.next() ) {
.....
}
rs.close(); rs = null;
stmt.executeUpdate("UPDATE ....");
conn.commit();
}
catch(SQLException e){ //<------ (3) 이 부분을 탈까?
if ( conn != null ) try{conn.rollback();}catch(Exception ee){}
throw e;
}
finally {
if ( rs != null ) try{rs.close();}catch(Exception e){}
if ( stmt != null ) try{stmt.close();}catch(Exception e){}
// in some connection pool, you have to reset commit mode to "true"
if ( conn != null ) ...<releaseConnection()>...;
}
위 코드를 보듯, 만약 (1)을 수행 후 (2)번 에서 NullPointerException 이나
ArrayIndexOutOfBoundException이라도 나면 어떻게 되죠? catch(SQLException ...)에는
걸리지 않고 곧바로 finally 절로 건너띄어 버리네요. 그럼 (1)에서 update 된 것은
commit()이나 rollback() 없이 connection 이 반환되네요... ;-)
어떻게 해야 합니까? SQLException만 잡아서 되는 것이 아니라, catch(Exception ...)과
같이 모든 Exception 을 catch해 주어야 합니다.
8.4 위 주석문에서도 언급해 두었지만, Hans Bergsteins 의 DBConnectionManager.java
와 같은 Connection Pool 을 사용할 경우에, 개발자의 코드에서 transaction auto
commit mode 를 명시적으로 "false"로 한 후, 이를 그냥 pool 에 반환하시면,
그 다음 사용자가 pool 에서 그 connection 을 사용할 경우, 여전히 transaction
mode 가 "false"가 된다는 것은 주지하셔야 합니다. 따라서, DBConnectionManger의
release method를 수정하시든지, 혹은 개발자가 명시적으로 초기화한 후 pool 에
반환하셔야 합니다. 그렇지 않을 경우, DB Lock 이 발생할 수 있습니다.
반면, IBM WebSphere 나 BEA WebLogic 과 같인 JDBC 2.0 스펙에 준하는 Connection
Pool을 사용할 경우는 반환할 당시의 transaction mode 가 무엇이었든 간에,
pool 에서 꺼내오는 connection 의 transaction mode는 항상 일정합니다.
(default 값은 엔진의 설정에 따라 달라집니다.)
그렇다고 commit 시키지 않은 실행된 쿼리가 자동으로 commit/rollback 되는 것은
아닙니다. 단지 auto commit 모드만 자동으로 초기화 될 뿐입니다.
PS:WAS의 JTS/JTA 트렌젝션 기반으로 운영될 경우는 명시적으로 commit/rollback되지
않은 트렌젝션은 자동으로 rollback되거나 commit됩니다. default는 WAS 설정에 따라
다를 수 있습니다.
---------------
NOTE: 자바서비스컨설팅의 WAS성능관리/모니터링 툴인 제니퍼(Jennifer 2.0)를 적용하면,
어플리케이션에서 명시적으로 commit/rollback시키지 않고 그대로 반환시킨 어플리케이션의
소스 위치를 실시간으로 감지하여 알려줍니다. 이를 만약 수작업으로 한다면, 수많은 코드
중 어디에서 DB lock을 유발 시키는 코드가 숨어있는지를 찾기가 경우에 따라 만만치 않은
경우가 많습니다.
8.5 XA JDBC Driver, J2EE JTS/JTA
JDBC 2.0, 표준 javax.sql.DataSource를 통한 JDBC Connection을 사용할 경우에,
대부분의 상용WAS제품들은 J2EE의 표준 JTS(Java Transaction Service)/JTA(Java Transaction
API)를 구현하고 있습니다. 특별히, 하나 이상의 데이타베이스에서 2 phase-commit과
같은 XA Protocol를 지원하고 있지요(사실 WAS에서 2PC가 지원되기 시작한 것은 몇년
되지 않습니다. 2PC를 사용하려면 반드시 XA-JDBC Driver가 WAS에 설치되어야 합니다)
샘플 예제는 다음과 같습니다.
...
javax.transaction.UserTransaction tx = null;
java.sql.Connection conn1 = null;
java.sql.Statement stmt1 = null;
java.sql.Connection conn2 = null;
java.sql.Statement stmt2 = null;
java.sql.CallableStatement cstmt2 = null;
try {
javax.naming.InitialContext ctx = new javax.naming.InitialContext();
tx = (javax.transaction.UserTransaction) ctx.lookup("java:comp/UserTransaction");
// 트렌젝션 시작
tx.begin();
// -------------------------------------------------------------------------
// A. UDB DB2 7.2 2PC(XA) Test
// -------------------------------------------------------------------------
javax.sql.DataSource ds1 =
(javax.sql.DataSource)ctx.lookup("java:comp/env/jdbc/DB2");
conn1 = ds1.getConnection();
stmt1 = conn1.createStatement();
stmt1.executeUpdate(
"insert into emp(empno,ename) values(" + empno + ",'Name" + empno + "')"
);
stmt1.executeUpdate(
"update emp set ename = 'LWY" + count + "' where empno = 7934"
);
java.sql.ResultSet rs1 = stmt1.executeQuery("select empno,ename from emp");
while(rs1.next()){
...
}
rs1.close();
// -------------------------------------------------------------------------
// B. Oracle 8.1.7 2PC(XA) Test
// -------------------------------------------------------------------------
javax.sql.DataSource ds2 =
(javax.sql.DataSource)ctx.lookup("java:comp/env/jdbc/ORA8i");
conn2 = ds2.getConnection();
stmt2 = conn2.createStatement();
stmt2.executeUpdate(
"update emp set ename = 'LWY" + count + "' where empno = 7934"
);
java.sql.ResultSet rs2 = stmt2.executeQuery("select empno,ename from emp");
while(rs2.next()){
...
}
rs2.close();
// -------------------------------------------------------------------------
// 트렌젝션 commit
tx.commit();
}
catch(Exception e){
// 트렌젝션 rollback
if ( tx != null ) try{tx.rollback();}catch(Exception ee){}
...
}
finally {
if ( stmt1 != null ) try { stmt1.close();}catch(Exception e){}
if ( conn1 != null ) try { conn1.close();}catch(Exception e){}
if ( stmt2 != null ) try { stmt2.close();}catch(Exception e){}
if ( conn2 != null ) try { conn2.close();}catch(Exception e){}
}
NOTE: 위에서 설명한 하나하나가 제 입장에서 보면 너무나 가슴깊이 다가오는
문제들입니다. 개발하시는 분의 입장에서 보면, 위의 가이드에 조금 어긋났다고
뭐그리 문제겠느냐고 반문하실 수 있지만, 수백본의 소스코드 중에 위와 같은 규칙을
준수하지 않은 코드가 단 하나라도 있다면, 잘 운영되던 시스템이 며칠에 한번씩
에러를 야기하거나 응답이 느려지고 급기야 hang 현상을 초래하는 결과를 가져 옵니다.
정말(!) 그렇습니다.
NOTE: 위에서 사용한 코딩 샘플들은 JDBC Connection Pooling 은 전혀 고려치 않고
설명드렸습니다. 그러했기 때문에 <getConnection()>, <releaseConnection()> 이란
Pseudo 코드로 설명했던 것입니다.
반드시 "서블렛 + JDBC 연동시 코딩 고려사항 -제2탄-" 을 읽어 보세요.
http://www.javaservice.net/~java/bbs/read.cgi?m=devtip&b=servlet&c=r_p&n=968522077
-------------------------------------------------------
본 문서는 자유롭게 배포/복사 할 수 있으나 반드시
이 문서의 저자에 대한 언급을 삭제하시면 안됩니다
================================================
자바서비스넷 이원영
E-mail: javaservice@hanmail.net
PCS:010-6239-6498
================================================
