[Database] Ch09. Transaction Control

[Database] Ch09. Transaction Control

트랜잭션 Transaction

• SQL을 여러개 묶어 놓은 것
• 함께 수행되어야 하는 논리적인 업무의 작업 단위
  ex. 계좌 이체 ( 출금 sql, 입금 sql 로 구성)

특징

• all or nothing
  • 트랜잭션 내의 모든 sql문이 성공해야 commit이 되고 그렇지 않으면 트랜잭션의 맨처음 sql 문으로 이동
• 트랜잭션에 많은 sql문이 있는 경우 (작업 단위가 큰 경우) saverpoint를 두고, savepoint 까지만 rollboack(취소)할 수 있음.

라이프 사이클 과정

• 트랜잭션 시작
  • DDL, DCL 문이 실행되면 begin transaction 없이도 암묵적으로 트랜잭션 시작
• 트랜잭션 종료 commit, rollback
  • commit, rollback 호출해야 트랜잭션이 종료. 리소스 해제됨.

DDL, DML 차이 : rollback 여부 ⭕ 암기, 면접

DML data를 다루는 명령어

• rollback(취소) 가능
• delete

DDL DB 구조를 다루는 명령여

• rollback(취소) 불가
• truncate

명령어

• 명시적인(explicit) 트랜잭션 제어 명령어
  • commit
  • savepoint 이름
  • rollback [to savepoint이름]
• 암시적인(implicit) 트랜잭션 제어 명령어
  • 자동 commit
    • DDL (create table) 명령 실행
    • DCL 명령 실행
    • commit, rollback 명시적으로 실행하지 않고, sql plu를 정상 종료한 경우
• ROLLBACK [to savepoint명]
  • sql plus 비정상 종료 or 시스템 실패 시

commit

아직 저장되지 않은 모든 데이터 변경사항을 데이터베이스에 저장하고 현재 트랜잭션 종료

특징

• commit 전
  • commit 전 작업은 나만 볼 수 있음. DB에 아직 반영되지 않았기 때문
  • 현재 사용자가 select 문으로 작업의 결과를 확인할 수 있음.
  • 단, 다른 사용자는 현재 사용자가 수행한 작업 결과 볼 수 없음.
• commit 후
  • 변경 사항(After Image)이 물리적인 데이터베이스에 반영됨.
  • 모든 사용자가 결과를 읽을 수 있음
  • 행의 lock이 풀리고 다른 사용자가 DML 명령 가능해짐

update t_bank_account set balance = 160000 where acct_no = 1002;
commit; -- 커밋하면 데이터베이스에 값이 영구 저장. 트랜잭션 종료

 

 

Lock

• DML(UPDATE, DELETE) 실행 시 해당 행에만 lock이 걸림 → 다른 사용자가 쓰기(변경)불가. 읽기 가능
  • 마지막으로 커밋된 버전의 읽기를 제공. select문 가능.
• commit or rollback 시 해당 행의 lock이 해제되면서 다른 사용자가 쓰기(변경) 가능해짐.

rollback

아직 저장되지 않은 모든 데이터 변경사항을 취소하고 현재 트랜잭션 종료

특징

• rollback 전 commit 전 특성과 동일
• rollback 후
  • 데이터의 변경 사항 취소됨
  • 이전 데이터(Before Image)는 다시 재저장됨
  • 관련된 행의 lock이 풀리고 해당 행에 대한 다른 사용자가 DML 명령 가능해짐

savepoint

rollback(취소)할 수 있는 저장점 생성. 부분 취소 가능.

 

표현 (라이프 사이클)

• savepoint 이름;  트랜잭션 안에 중간 저장점 생성
• rollback to 이름; 해당 지점까지 부분 취소. 해당 지점 뒤에 찍힌 savepoint 들 삭제.
• rollback; 트랜잭션 전체 취소
• commit; 확정. 모든 savepoint 제거

-- 3. [하] SAVEPOINT 전에 새 계좌 1003(이몽룡 50 000)을 추가했다가 일부만 취소하라.
-- 1) 홍길동 잔액 200 000 → 210 000으로 증가
-- 2) 새 계좌 1003 추가
-- 3) ROLLBACK TO 로 2)만 유지하고 1)을 취소 후 COMMIT.
update t_bank_account set balance = 210000 where holder = '홍길동';
insert into t_bank_account values (1003, '이몽룡', 50000); -- 트랜잭션 종료 전까지 정의한 sql문들이 하나의 트랜잭션안에서 정의됨.
-- 삽입되었다고 꼭 행의 마지막에 삽입되지는 않음. 관계형 테이블은 집합임. 순서가 없음. 
savepoint before_update_1003;

update t_bank_account set balance = 100000 where acct_no = 1003;
savepoint after_update_1003;
select * from t_bank_account ;
rollback to before_update_1003;
select * from t_bank_account ; -- 이몽룡 5000인 상태로 돌아감. 즉, 100 000원 sql문이 실행되기전으로 이동
commit;

select * from t_bank_account;
rollback to after_update_1003;-- 에러. 
-- ORA-01086: 'AFTER_UPDATE_1003' 저장점이 이 세션에 설정되지 않았거나 부적합합니다.

-- 설명:
-- 1. COMMIT이 실행되면 해당 트랜잭션에서 정의한 모든 SAVEPOINT가 소멸
-- 2. 존재하지 않는 SAVEPOINT로 ROLLBACK TO를 시도하면 위와 같은 ORA-01086 에러가 발생

특징

• 동일한 식별자를 가진 savepoint의 위치는 덮어써짐
  • 동일 트랜잭션 내에서 동일한 식별자를 가진 savepoint를 다시 사용하면 이전 savepoint를 새 savepoint가 덮어씀. savpoint 에는 새로운 위치가 저장됨.
• commit을 하면 한 트랜잭션 내 정의한 savepoint 모두 소멸.
  • 존재하지 않은 saveoint로 rollback to를 하면 에러 발생
  • [에러] ORA-01086: 'AFTER_UPDATE_1003' 저장점이 이 세션에 설정되지 않았거나 부적합합니다.
• DDL 전 savepoint는 무효화됨.
  • DDL 실행 후 자동으로 암묵적 commit 일으켜서 savepoint 지워버림.
• savepoint 목록을 보여주는 명령어는 없음.

savepoint 위치 지정

• “취소하고 싶은 변경” 보다 앞에서 Savepoint를 찍어야 한다.
•  여러 단계를 거칠 때는 숫자·라벨을 붙여 순서를 명확하게 :
  • sp_step1_loaded, sp_step2_cleansed …

savepoint 네이밍 컨벤션

• 이름 sp_단계_업무행위
• ex. sp_before_bonus; sp_after_load_02;

단계 + 의미	sp_step1_load, sp_step2_clean	배치·ETL처럼 단계가 명확할 때
행위 중심	sp_before_bonus, sp_after_bonus	전·후 비교가 필요할 때
시리얼 번호	sp_batch_01, sp_batch_02	루프마다 자동 생성(동적 SQL)
컴포넌트 접두사	sp_payroll_calc, sp_payroll_post	여러 모듈이 한 트랜잭션을 공유할 때

 

 

set transaction

트랜잭션의 성격을 미리 선언하는 한 줄짜리 지시문

• 읽기 / 쓰기 전용
• 고립화(격리) 수준

작성 위치

• DML/ SELECT 전에 작성
  • 이미 실행된 SQL문이 있으면 에러 발생

적용 범위

• 하나의 트랜잭션에만 적용. rollback, commit 후에는 사라짐

격리 수준

동시에 접근하는 트랜잭션이 있을 때, 데이터 일관성을 어느 선까지 보장할 것인지 보호 수준 선택

 

종류

• serializable 가장 엄격 트랜잭션 결과가 순차적으로 실행한 것과 동일

예시

set transaction read only; 읽기만 OK. 쓰기 불가. 에러 발생

-- 5. [중] 읽기 전용 트랜잭션에서 총 잔액을 조회하고, 잔액 변경이 거부되는지 확인하라.
-- DML SELECT 이전에 트랜잭션 보호 수준 설정
commit;
set transaction read only;

select * from t_bank_account;
select to_char(sum(balance),'L999,999,999') as "총 잔액" 
from t_bank_account;

-- 잔액 변경 거부되지 않음. SET TRANSACTION READ ONLY 없는 경우
update t_bank_account set balance = 1000 where acct_no = 1003; -- 에러. 
-- SQL 오류: ORA-01456: READ ONLY 트랜잭션은 삽입/삭제/업데이트 작업을 수행할 수 없습니다.
-- 01456. 00000 -  "may not perform insert/delete/update operation inside a READ ONLY transaction"

-- 설명:
-- 1) SET TRANSACTION READ ONLY;  
--    → 현재 트랜잭션을 읽기 전용 모드로 설정
-- 2) 읽기 전용 모드에서는 INSERT/UPDATE/DELETE 작업이 불가능하므로  
--    UPDATE 문 실행 시 ORA-01456 에러가 발생  
-- 3) COMMIT;  
--    → 읽기 전용 트랜잭션을 종료하고, 이후 새 트랜잭션은 기본(읽기·쓰기) 모드가 적용
rollback ; -- 읽기 전용 트랜잭션 종료

 

 

실습 코드

테이블 생성 및 데이터 추가

CREATE TABLE t_bank_account (
    acct_no     NUMBER PRIMARY KEY,
    holder      VARCHAR2(30),
    balance     NUMBER(12,2)
);

INSERT INTO t_bank_account VALUES (1001,'홍길동', 200000);
INSERT INTO t_bank_account VALUES (1002,'김개똥', 150000);

 

 

COMMIT, ROLLBACK, SAVEPOINT 실습

select * from t_bank_account;

-- ① [하] 실습용 테이블과 초기 데이터 작성 후 COMMIT
commit; -- 트랜잭션 종료


select * from t_bank_account;

-- 1. [하] 홍길동(1001)의 잔액을 200 000 → 130 000으로 줄였다가 ROLLBACK으로 원복하라.
update t_bank_account set balance = 130000 where holder = '홍길동';
select * from t_bank_account;
rollback ; -- 실행 후 홍길동의 balance 가 200 000으로 돌아감. 트랜잭션 종료 


-- 2. [하] 김개똥(1002)의 잔액을 150 000 → 160 000으로 늘리고 COMMIT하라.
update t_bank_account set balance = 160000 where acct_no = 1002;
commit; -- 커밋하면 데이터베이스에 값이 영구 저장. 트랜잭션 종료

-- 3. [하] SAVEPOINT 전에 새 계좌 1003(이몽룡 50 000)을 추가했다가 일부만 취소하라.
-- 1) 홍길동 잔액 200 000 → 210 000으로 증가
-- 2) 새 계좌 1003 추가
-- 3) ROLLBACK TO 로 2)만 유지하고 1)을 취소 후 COMMIT.
update t_bank_account set balance = 210000 where holder = '홍길동';
insert into t_bank_account values (1003, '이몽룡', 50000); -- 트랜잭션 종료 전까지 정의한 sql문들이 하나의 트랜잭션안에서 정의됨.
-- 삽입되었다고 꼭 행의 마지막에 삽입되지는 않음. 관계형 테이블은 집합임. 순서가 없음. 
savepoint before_update_1003;

update t_bank_account set balance = 100000 where acct_no = 1003;
savepoint after_update_1003;
select * from t_bank_account ;
rollback to before_update_1003;
select * from t_bank_account ; -- 이몽룡 5000인 상태로 돌아감. 즉, 100 000원 sql문이 실행되기전으로 이동
commit;

select * from t_bank_account;
rollback to after_update_1003;-- 에러. 
-- ORA-01086: 'AFTER_UPDATE_1003' 저장점이 이 세션에 설정되지 않았거나 부적합합니다.

-- 설명:
-- 1. COMMIT이 실행되면 해당 트랜잭션에서 정의한 모든 SAVEPOINT가 소멸
-- 2. 존재하지 않는 SAVEPOINT로 ROLLBACK TO를 시도하면 위와 같은 ORA-01086 에러가 발생


--4. [중] 이몽룡 (계좌 1003)의 잔액을 50 000 → 0 으로 바꾼 뒤, 
--SAVEPOINT / ROLLBACK 조합으로 30 000만 차감된 20 000 상태만 남기고 COMMIT하라.
update t_bank_account set balance = 500000 where acct_no = 1003;

-- 잔고가 50000인 상태 원본 저장.
savepoint sp_before_zero;

-- 이몽룡의 잔고를 0으로 변경 
select * from t_bank_account;
update t_bank_account set balance = 0 where acct_no = 1003;
select * from t_bank_account;

-- 이몽룡 잔고가 0인 지점
savepoint sp_after_zero;
update t_bank_account set balance = 20000 where acct_no = 1003;


select * from t_bank_account;
commit; -- 잔고가 20000인 상태에서 commit