개요

여러 개의 레코드를 반환하는 select 문 처리를 위해 커서를 사용하여 레코드 fetch 과정 중 발생하는 오류에 관해 설명한다.

버전

Altibase 6.3.1 이상

현상

여러 개의 레코드를 반환하는 질의문 처리를 위해서는 아래와 같은 과정으로 커서(CURSOR)를 이용해야 한다. 

1. 커서 선언 (DECLARE CURSOR)
2. 커서 OPEN (OPEN CURSOR)
3. 커서 FETCH (FETCH CURSOR)
4. 커서 CLOSE/RELEASE (CLOSE CURSOR 또는 RELEASE CURSOR)

위와 같이 커서를 사용하였으나 '3. 커서 FETCH' 단계에서 어느 정도 FETCH 진행하다가 어느 순간 FETCH 할 레코드가 남아있음에도 ERR-410D2 (266450) Fetch out of sequence. 에러가 발생한다. 

아래는 커서 사용 시 에러가 발생하는 부분과 에러 발생 결과 예시이다. 

 
커서(CURSOR) 선언

커서(CURSOR) OPEN
 
/* fetch cursor in loop */
/* 조건에 맞는 레코드를 모두 가져오기 위해 수행 결과가 SQL_NO_DATA일 때까지 FETCH CURSOR 문을 반복 수행한다. */
while(1)
{
    커서(CURSOR) FETCH ;
    
    if (sqlca.sqlcode == SQL_SUCCESS) 
    {
        ...
    }
    else if (sqlca.sqlcode == SQL_NO_DATA)
    {
        ...
    }
    else 
    {   
        /* 어느 정도 FETCH 진행하다가 어느 순간 fetch 할 레코드가 남아있음에도
           ERR-410D2 (266450) Fetch out of sequence. 에러가 발생한다. */
        printf("Error : [%d] %s\n", SQLCODE, sqlca.sqlerrm.sqlerrmc);
        break;
    }  
}

$ ./cursor1
<CURSOR 1>
[Success declare cursor]

[Success open cursor]

[Fetch Cursor]                                                    
------------------------------------------------------------------
DNO      DNAME                          DEP_LOCATION       MGR_NO 
------------------------------------------------------------------
1     BUSINESS DEPT                  Seoul        100
2     BUSINESS DEPT                  Seoul        100
...중략...
583     BUSINESS DEPT                  Seoul        100
584     BUSINESS DEPT                  Seoul        100

Error : [-266450][HY000] Fetch out of sequence.                     /* fetch 수행 중 에러 발생 */
------------------------------------------------------------------
[Close Cursor]                                                    
------------------------------------------------------------------
Success close cursor

원인

커서(CURSOR) OPEN 상태에서 COMMIT/ROLLBACK 수행한 경우

Altibase는 ANSI 표준을 준수하여 기본적으로 fetch across commit 방식을 지원하지 않도록 설정되어 있다. 따라서  커서 OPEN 후 COMMIT 또는 ROLLBACK을 수행하면 ANSI 표준에 따라 커서를 강제로 닫는다. 
 

이런 이유로 애플리케이션에서 커서 OPEN 후 COMMIT 또는 ROLLBACK을 수행한 경우 에러가 발생할 수 있다.

어느 정도 FETCH 수행하다가 에러가 발생하는 이유는 처음 커서 FETCH 시 일정량의 레코드가 통신 버퍼에 담기기 때문이다. 통신 버퍼에 담긴 레코드를 모두 FETCH하고 다음 일정량의 레코드를 통신 버퍼에 담기 위한 FETCH를 할 때 에러가 발생하게 된다. 

아래는 커서 OPEN 상태에서 COMMIT 또는 ROLLBACK을 수행하여 에러가 발생할 수 있는 애플리케이션 작성 예이다.

non-autocommit mode 

커서(CURSOR) 선언

커서(CURSOR) OPEN

while(1)
{
    커서(CURSOR) FETCH ;
    
    if (sqlca.sqlcode == SQL_SUCCESS) {
    
       /* 변경 DML 수행 */                     
       
       /* COMMIT 또는 ROLLBACK 수행 */ 
       
    }
    else if (sqlca.sqlcode == SQL_NO_DATA) {
       ...
    }
    else {              
      /* 처음 통신 버퍼에 담긴 레코드를 모두 처리한 후 두 번째 통신 버퍼에 담길 때 이 단계에서 에러가 발생한다. */
       ...
    }   
}

조치

커서(CURSOR) OPEN 상태에서 COMMIT/ROLLBACK 수행한 경우

이 에러를 조치하기 위한 3가지 방법을 안내한다. 

• 다중 연결을 사용하여 fetch와 변경 DML 작업을 분리하는 방법

• 통신 버퍼에 담길 만큼만 커서 선언 후 반복적 커서 오픈하는 방법

• fetch across commit 을 이용하는 방법
  
  

위 방법은 모두 애플리케이션 변경이 필요하다.

1. fetch 세션과 변경 DML 세션 분리

하나의 애플리케이션 내에 다중 연결을 사용하여 COMMIT 또는 ROLLBACK 수행이 커서에 영향이 없도록 한다.  

• • 커서를 사용하는 세션과 변경 DML문을 수행하는 세션을 생성한다. 각각 CONN1, CONN2로 정의하여 설명한다. 
  • 변경 DML문을 수행하는 세션(CONN2)는 non-autocommit mode로 설정한다.
  • 커서를 사용하는 세션(CONN1)에서 커서 FETCH 수행할 때마다 CONN2에서 변경DML을 수행하고 COMMIT 또는 ROLLBACK 을 수행한다.

아래는 이 조치를 반영한 애플리케이션 작성 예이다. 

• /* FETCH를 위한 세션 */ EXEC SQL AT conn1 CONNECT;   /* 변경DML 수행을 위한 세션 */ EXEC SQL AT conn2 CONNECT; EXEC SQL AT conn2 AUTOCOMMIT OFF;   /* CONN1에서 커서 선언, OPEN, FETCH 수행 */ EXEC SQL AT conn1 DECLARE cursor; EXEC SQL AT conn1 OPEN cursor; while (1) { /* conn1에서 FETCH 수행. */ EXEC SQL AT conn1 FETCH cursor if (sqlca.sqlcode == SQL_SUCCESS) { /* conn2에서 변경DML 및 COMMIT 또는 ROLLBACK 수행 */ EXEC SQL AT conn2 INSERT or UPDATE or DELETE ; /* check sqlca.sqlcode */ if (sqlca.sqlcode == SQL_SUCCESS) { ... } else { ... } /* conn2에서 수행한 commit 또는 rollback은 conn1의 커서 사용에 영향을 주지 않는다. */ EXEC SQL AT conn2 commit or rollback; /* check sqlca.sqlcode */ if (sqlca.sqlcode == SQL_SUCCESS) { ... } else { ... } } else if (sqlca.sqlcode == SQL_NO_DATA) { ... } else { ... } }

2. 통신 버퍼에 담길 만큼만 커서 선언 후 반복적 커서 오픈

한 번의 FETCH 로 통신 버퍼에 담길 만큼의 레코드 수를 산정한 후 LIMIT 절을 사용해 커서를 선언한다.

Altibase 5 이상 버전의 통신 버퍼 크기는 32K이다. 통신 버퍼에 담기는 레코드 수는 레코드 크기에 따라 다르기 때문에 LIMIT절 마지막 값은 운영 환경에 따라 달라진다. 

LIMIT절에 통신 버퍼에 담길 만큼의 레코드 수를 지정하고 커서 오픈 전에 LIMIT절의 시작 값을 변경하면서 커서를 다시 오픈하여 사용한다. 

아래는 이 조치를 반영한 애플리케이션 작성 예이다. 

• /* LIMIT 절을 사용하여 커서를 선언한다. n은 LIMIT절에서 반환할 마지막 레코드 값으로 운영 환경에 맞게 정의해야 한다. 통신 버퍼에 담기는 레코드 수는 레코드 크기에 따라 다르다. */ DECLARE 커서 SELECT ~ FROM ~ WHERE ~ LIMIT :s_start, n; /* LIMIT 절에 사용한 시작 값을 선언한다. */ s_start = 1; while(1) { /* 조건에 맞는 레코드를 모두 FETCH할 때까지 커서 오픈을 반복한다. */ OPEN 커서 while(1) { 커서(CURSOR) FETCH ; if (sqlca.sqlcode == SQL_SUCCESS) { /* 변경 DML 수행 */ } else if (sqlca.sqlcode == SQL_NO_DATA) { ... } else { ... } } /* COMMIT 또는 ROLLBACK 수행 */   /* LIMIT 절의 시작 값을 지정한다. n은 예시이다. */ s_start = s_start + n ; } CLOSE 커서 또는 CLOSE RELEASE 커서

3. fetch across commit 

ANSI 표준에서 권장하는 방식은 아니지만 타 DBMS에 익숙한 사용자 편의를 위해 6.3.1부터 추가된 기능이다.

Precompiler(APRE) 의 경우 커서 선언 시 WITH HOLD 커서를 선언하여 사용한다. 

• EXEC SQL DECLARE cursor_name CURSOR WITH HOLD FOR SELECT문

참고

버전 별 차이

Altibase 버전에 따라 같은 상황에서 발생하는 에러 메시지는 다를 수 있다.

Non-autocommit 환경에서 FETCH 중 COMMIT/ROLLBACK 수행할 경우 발생하는 에러 메시지 차이는 아래와 같다.

참고 매뉴얼

Altibase 6.3.1부터 제공하는 fetch across commit과 관련한 매뉴얼을 안내한다. 

• Precompiler User's Manual : 8. 커서 처리 SQL문 -> 커서 관련 내장 SQL문 -> DECLARE CURSOR
• CLI User's Manual : 2. ALTIBASE HDB CLI 함수 -> SQLSetStmtAttr
• JDBC User's Manual : 3. 고급 기능 -> ResultSet 사용하기 -> Holdability

매뉴얼은 아래 페이지에서 다운로드 할 수 있다.

• http://support.altibase.com/kr/manual