트랜잭션과 저장 프로그램·커서·예외 처리
트랜잭션은 함께 성공하거나 함께 취소해야 하는 논리적 작업 단위이며 COMMIT과 ROLLBACK으로 확정·취소한다. 저장 프로시저·함수·트리거는 호출 방식과 반환 목적이 다르다. 커서는 조회 결과를 한 행씩 처리하며 선언·열기·인출·닫기 순서를 따른다. 예외 처리는 오류의 제어 흐름을 다루므로 전체 트랜잭션 롤백과 같은 기능으로 보면 안 된다.
트랜잭션: 함께 성공하거나 함께 취소하는 작업 단위
트랜잭션(Transaction)은 여러 SQL 문장을 하나의 논리적 작업으로 묶은 단위다. 계좌 이체라면 출금과 입금이 모두 성공해야 하며, 둘 중 하나만 반영되어서는 안 된다.
| 명령 | 의미 | 실행 후 핵심 상태 |
|---|---|---|
COMMIT | 현재 트랜잭션의 미확정 변경을 확정한다. | 일반적인 ROLLBACK으로 되돌릴 수 없는 확정 상태가 된다. |
ROLLBACK | 현재 트랜잭션의 미확정 변경을 취소한다. | 트랜잭션 시작 이후의 변경을 원래 상태로 되돌린다. |
SAVEPOINT 이름 | 트랜잭션 내부에 중간 복구 지점을 만든다. | 변경은 아직 확정되지 않는다. |
ROLLBACK TO SAVEPOINT 이름 | 지정한 저장점 이후의 변경만 취소한다. | 저장점 이전 변경은 미확정 상태로 남고 트랜잭션은 계속된다. |
SAVEPOINT는 변경을 확정하는 명령이 아니다. 또한 COMMIT이 끝난 뒤에는 그 트랜잭션의 변경을 보통의 ROLLBACK으로 취소할 수 없다.
계좌 이체 예
다음은 트랜잭션 경계를 보여 주기 위한 예다. 트랜잭션 시작 구문과 행 수 확인 방법은 DBMS나 클라이언트에 따라 다를 수 있다.
START TRANSACTION;
UPDATE account
SET balance = balance - 100000
WHERE account_id = 'A'
AND balance >= 100000;
UPDATE account
SET balance = balance + 100000
WHERE account_id = 'B';
COMMIT;
실제 업무에서는 두 UPDATE가 각각 정확히 한 행에 적용되었는지, 출금 후 잔액이 업무 규칙을 만족하는지 등을 COMMIT 전에 확인해야 한다. 어느 조건이라도 충족하지 못하면 ROLLBACK하여 출금과 입금을 함께 취소한다.
트랜잭션에서 자주 틀리는 부분
- 자동 커밋(autocommit)은 도구나 연결의 실행 설정이며,
COMMIT·ROLLBACK의 개념 또는 SQL 명령 분류와 같은 뜻이 아니다. - 오류가 발생했다고 모든 DBMS에서 이전의 모든 문장이 자동으로 취소되는 것은 아니다. 실패한 문장과 트랜잭션 전체가 어떻게 처리되는지는 DBMS 및 실행 문맥에 따라 다르다.
- 트랜잭션은 지나치게 넓게 잡으면 잠금 유지 시간과 충돌 가능성이 커지고, 지나치게 좁게 잡으면 함께 보장해야 할 업무 일관성이 깨질 수 있다.
ACID 속성, 격리 수준, 동시성 이상, 잠금과 교착상태의 세부 내용은 별도 트랜잭션 이론에서 다룬다. 여기서는 SQL 응용에 필요한 작업 경계와 확정·취소 판단에 집중한다.
절차형 SQL과 저장 프로그램
절차형 SQL은 SQL에 변수, 조건문, 반복문, 예외 처리 같은 절차적 기능을 결합한 방식이다. 저장 프로시저, 사용자 정의 함수, 트리거는 대표적인 저장 프로그램이지만 호출 방식과 결과 전달 방식이 서로 다르다.
| 구분 | 실행 시작 | 주된 목적 | 결과 전달 | 핵심 판별 기준 |
|---|---|---|---|---|
| 저장 프로시저(Procedure) | 호출자가 CALL, EXEC 등으로 명시적으로 호출 | 여러 SQL과 업무 절차를 하나의 작업으로 묶음 | 출력 매개변수·결과 집합 등을 사용할 수 있으며 DBMS마다 다름 | 호출해서 작업을 수행한다. |
| 함수(Function) | 호출자가 함수명과 인수를 사용해 호출 | 입력을 계산하거나 재사용 가능한 결과를 생성 | 값 또는 집합을 반환하며 SQL 식에서의 사용 범위는 DBMS마다 다름 | 반환 결과를 사용하는 데 초점이 있다. |
| 트리거(Trigger) | 지정한 사건이 발생하면 DBMS가 자동 실행 | 변경 검증, 파생 처리, 감사 기록 등 | 호출자에게 일반 함수처럼 직접 값을 반환하지 않음 | 사건에 반응해 자동 실행된다. |
“프로시저는 반환값이 없고 함수만 값을 반환한다”라고 단정하면 안 된다. 프로시저도 출력 매개변수나 결과 집합으로 결과를 전달할 수 있다. 반대로 함수가 데이터 변경이나 트랜잭션 제어를 어디까지 수행할 수 있는지는 DBMS와 호출 문맥에 따라 제한이 다르다.
절차형 블록의 기본 구성
Oracle PL/SQL의 익명 블록은 다음과 같은 대표 구조를 가진다. 다른 절차형 SQL 언어에서는 키워드와 배치가 달라질 수 있다.
DECLARE -- 선언부: 선택
변수·상수·커서·사용자 정의 예외 선언
BEGIN -- 실행부: 필수
SQL 문장과 제어문
EXCEPTION -- 예외 처리부: 선택
예외별 처리 동작
END;
저장 프로시저와 함수에는 이 본문 앞에 이름과 매개변수를 정의하는 머리부가 붙는다. 프로시저 매개변수는 대표적으로 입력용 IN, 출력용 OUT, 입력과 출력을 겸하는 IN OUT으로 나눌 수 있다. 함수는 RETURN 또는 해당 DBMS의 반환 구문으로 호출자에게 결과를 전달한다. 정확한 선언 문법과 지원 방식은 DBMS마다 다르다.
트리거의 실행 시점과 단위
DML 트리거는 보통 INSERT, UPDATE, DELETE 같은 사건에 연결된다.
BEFORE: 원래 문장이 반영되기 전에 실행한다.AFTER: 원래 문장이 처리된 뒤 실행한다.INSTEAD OF: 원래 동작을 대신하며 주로 뷰 등에 사용한다.- 행 수준 트리거: 영향을 받는 각 행마다 실행한다.
- 문장 수준 트리거: 영향을 받은 행 수와 관계없이 SQL 문장마다 한 번 실행한다.
문장 수준 트리거가 트랜잭션당 한 번 실행되는 것은 아니다. 한 트랜잭션에 UPDATE 문장이 세 번 있으면 문장 수준 트리거도 사건 조건에 따라 세 번 실행될 수 있다.
트리거는 일반적으로 원래 문장과 같은 트랜잭션 안에서 동작하므로 트리거 오류가 원래 DML의 실패로 이어질 수 있다. 다만 세부 실행 순서와 예외 동작은 DBMS마다 다르다. 트리거에 지나치게 큰 업무 로직을 넣으면 호출 흐름이 숨겨져 테스트와 장애 분석이 어려워질 수 있다.
이벤트와 트리거의 관계
이벤트는 데이터 삽입·수정·삭제, 특정 시스템 상태 변화처럼 처리를 시작하게 하는 사건이다. 트리거는 자신에게 정의된 이벤트가 발생하면 자동 실행되는 프로그램이다. 제품에 따라 일정 시각에 작업하는 예약 이벤트 기능도 있지만, 이벤트라는 말과 트리거 객체 자체를 같은 뜻으로 보지 않는다. 행 수준 트리거가 5행을 바꾼 문장에 연결되면 통상 5번, 문장 수준 트리거라면 문장에 대해 한 번 실행된다는 처리 단위를 구분한다.
조건문과 반복문
절차형 SQL에서는 다음과 같은 제어 구조를 사용한다.
IF: 조건의 참·거짓에 따라 실행 경로를 나눈다.CASE: 여러 조건 또는 값에 따라 분기한다.LOOP: 명시적인 종료 조건을 만날 때까지 반복한다.WHILE: 조건이 참인 동안 반복한다.FOR: 지정한 범위나 질의 결과를 기준으로 반복한다.
정확한 블록 문법은 PL/SQL, PL/pgSQL, T-SQL 등 DBMS의 절차형 언어마다 다르다. 반복문이 존재한다고 해서 반드시 커서를 사용해야 하는 것은 아니다. 여러 행을 한 번의 UPDATE로 처리할 수 있다면 집합 기반 SQL이 보통 더 단순하다.
커서: 결과 집합을 한 행씩 처리하는 제어 수단
커서(Cursor)는 SQL 문장의 처리 정보나 결과 집합을 가리키는 포인터 또는 핸들이다. 커서 자체가 결과 데이터 전체와 같은 뜻은 아니다.
- 암시적 커서: SQL 문장을 실행할 때 DBMS 또는 절차형 언어가 자동으로 만들고 관리한다.
- 명시적 커서: 개발자가 질의를 선언하고 열기·인출·닫기를 직접 제어한다.
명시적 커서의 기본 수명주기
DECLARE: 커서와 연결할 질의를 선언한다.OPEN: 질의를 실행할 준비를 하고 결과 집합을 연다.FETCH: 현재 위치의 행을 변수로 가져오고 다음 위치로 이동한다.- 종료 여부 확인: 방금 가져온 행이 있는지 확인한다.
- 행 처리: 가져온 값으로 필요한 작업을 수행한다.
- 3~5단계를 반복한다.
CLOSE: 커서를 닫고 관련 자원을 해제한다.
다음 코드는 특정 DBMS에서 그대로 실행하는 문법이 아니라 수명주기를 보여 주는 의사 코드다.
DECLARE cursor c FOR query
OPEN c
LOOP
FETCH c INTO row_variables
EXIT WHEN no_row_was_fetched
process row_variables
END LOOP
CLOSE c
종료 조건은 FETCH 직후에 확인해야 한다. 더 이상 가져올 행이 없는데 이전 변수 값을 다시 처리하는 오류를 막기 위해서다.
Oracle PL/SQL 형식에서는 명시적 커서 속성을 다음처럼 표현한다. 다른 DBMS의 표기와 동일하다고 일반화해서는 안 된다.
| 속성 | 의미 |
|---|---|
커서명%ISOPEN | 커서가 열려 있는지 확인한다. |
커서명%FOUND | 최근 FETCH가 행을 가져왔는지 확인한다. |
커서명%NOTFOUND | 최근 FETCH가 행을 가져오지 못했는지 확인한다. |
커서명%ROWCOUNT | 지금까지 인출한 행 수를 확인한다. |
커서 FOR LOOP처럼 열기·인출·닫기를 자동 처리하는 구문도 있지만 정확한 문법은 DBMS마다 다르다.
집합 기반 SQL을 먼저 검토한다
모든 A등급 직원의 급여를 5% 인상하는 작업은 커서보다 다음과 같은 단일 문장이 자연스럽다.
UPDATE employee
SET salary = salary * 1.05
WHERE grade = 'A';
행마다 이전 행의 결과에 의존하는 복잡한 순차 처리, 행별로 다른 외부 승인 결과 반영 등 집합 연산으로 표현하기 어려운 요구가 있을 때 커서나 애플리케이션 반복을 검토한다. 커서는 정확성을 자동으로 높여 주는 기능이 아니며, 불필요한 행 단위 반복은 처리 시간과 잠금 유지 시간을 늘릴 수 있다.
커서의 반복과 종료
DECLARE → OPEN → FETCH 뒤에 가져온 행이 있는지 검사한다. 있으면 행을 처리하고 FETCH로 돌아가며, 없으면 CLOSE한다. 예외 경로에서도 열린 커서 자원을 정리한다.
예외 처리: 오류를 분류하고 복구 또는 전파한다
예외(Exception)는 정상 실행을 계속할 수 없게 만드는 오류나 비정상 조건이다. 예외 처리의 목적은 오류를 무조건 없애는 것이 아니라 무엇이 실패했는지, 어디까지 취소할지, 상위 호출자에게 알릴지를 명확히 하는 데 있다.
PL/SQL에서 자주 사용하는 분류는 다음과 같다.
- 내부 정의 예외: DBMS가 내부적으로 정의한 오류다.
- 사전 정의 예외: 내부 정의 오류 중
NO_DATA_FOUND,ZERO_DIVIDE처럼 미리 이름이 부여된 예외다. - 사용자 정의 예외: 업무 규칙 위반 등을 표현하기 위해 개발자가 선언하고 발생시키는 예외다.
예외가 발생했을 때의 흐름
- 정상 실행부의 문장 수행 중 예외가 발생한다.
- 현재 블록의 정상 실행은 중단된다.
- 일치하는 예외 처리기가 있으면 복구·기록·변환·재발생 같은 동작을 수행한다.
- 처리기가 없거나 다시 예외를 발생시키면 상위 블록 또는 호출자에게 전파된다.
예외를 처리한 뒤 실패한 문장의 다음 줄로 자동 복귀한다고 단정해서는 안 된다. 블록 종료 이후의 제어 흐름과 전파 방식은 절차형 언어 규칙에 따라 판단한다.
구체적인 예외 처리기를 먼저 두고, 모든 예외를 받는 처리기는 마지막에 두는 것이 일반적이다. Oracle PL/SQL의 WHEN OTHERS가 대표적인 포괄 처리기다. 포괄 처리기에서 오류를 기록하지도, 다시 발생시키지도 않고 성공처럼 종료하면 원인을 숨길 수 있다.
예외 처리와 트랜잭션 제어는 같은 기능이 아니다
예외를 잡았다고 해서 전체 트랜잭션이 자동으로 ROLLBACK되는 것은 아니다. 반대로 처리되지 않은 오류가 항상 동일한 범위로 취소되는 것도 아니다. DBMS에 따라 실패한 문장만 취소되거나 트랜잭션이 오류 상태가 될 수 있으므로, 트랜잭션 경계를 소유한 호출자가 최종 COMMIT·ROLLBACK 여부를 명시적으로 결정해야 한다.
실행 시점과 반환값을 함께 읽기
Oracle PL/SQL 블록의 선언부·실행부·예외 처리부는 각각 변수 등의 준비, 정상 처리, 오류에 대한 처리를 맡는다. 실행부는 BEGIN … END이며 선언부와 예외 처리부는 선택 사항이다. IN OUT 매개변수는 호출자의 입력을 받아 사용한 뒤 변경된 값을 호출자에게 돌려주는 양방향 전달이다. 출력 매개변수가 있다고 자동으로 커밋되는 것은 아니다.
명시적 커서를 열었다고 질의 전체의 행 수가 %ROWCOUNT에 들어가는 것은 아니다. Oracle PL/SQL의 %ROWCOUNT는 성공적으로 인출한 행 수의 누계다. OPEN 직후 0이고, 두 행을 FETCH했다면 2다. %FOUND와 %NOTFOUND는 최근 FETCH의 성공 여부를 확인하는 속성이다.
일반적인 DML의 AFTER 트리거에서 '후'는 해당 DML 사건에 대한 실행 시점을 뜻한다. 트랜잭션의 COMMIT 이후를 뜻하지 않는다. BEFORE·AFTER 구분, 행·문장 단위 구분, 트랜잭션의 확정·취소 구분은 서로 다른 축이다. 트랜잭션 상태에서 마지막 작업을 실행한 부분 완료 상태도 지속성이 확보된 커밋 상태와 구분한다.