TCL 트랜잭션(commit, rollback, savepoint)

트랜잭션(transaction)

: 데이터베이스에서 데이터를 관리할 때(insert, update, delete) 여러개의 SQL 명령어를 사용하게 되는 데 

이를 하나의 작업 단위로 설정하는 것

- 트랜잭션 처리요건

All or Noting(원자성) : 반드시 처리가 되든지 or 처리가 되지 않든지

                                  commit or rollback

1) COMMIT

- 모든 작업들을 정상적으로 처리

- 데이터베이스에 모두 반영 : 테이블에 적용이 된다.

- 변경된 내용을 모두 영구 저장 : 테이블에 저장

- commit이 되면 작업이 끝난 상태로 취급

- rollback이 안됨(취소가 불가능)

2) ROLLBACK

- 처리 과정에서 발생한 변경 사항을 취소(메모리상에 저장된 결과물, insert, update, delete)

-이전의 상태로 되돌린다 -> 삭제

- commit 후 그 다음의 상태부터 롤백이 가능 commit 이전 데이터는 취소 불가

3) 자동 commit 되는 경우

- DDL문(create~ alter~ drop~으로 시작하는 문장)

- DCL문(grant, revoke) 사용권한

- exit하면 commit 명령어를 입력하지 않아도 메모리에 저장된 값을 테이블로 저장한 후 오라클을 종료

4) 자동 rollback 되는 경우

- 비정상적인 종료

Q1. commit 사용하기

창1. 흰색바탕 20170510.txt

SQL> create table b_dept2

  2  as select * from dept;

테이블이 생성되었습니다.

SQL> select * from b_dept2;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

SQL> insert into b_dept2 values (50,'SUPPORT','KWANGJU');

1 개의 행이 만들어졌습니다.

SQL> select * from b_dept2;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

        50 SUPPORT        KWANGJU   // 실제로 데이터가 들어가 있지 않음

-> 오라클 메모리에 저장된 상태로 존재(insert, update, delete) <- 허상

-> 아직 테이블에 저장된 상태가 아님

-> 창2에서 실행해보면 생성된 데이터가 나타나지 않음

-> commit을 하면 데이터가 저장처리

SQL> commit;

커밋이 완료되었습니다.

-> commit을 완료하면 rollback이 안됨

창2. 검은바탕 20170510_2.txt

SQL> select * from b_dept2;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

-> 창1에서 만든 데이터가 들어있지 않음

SQL> select * from b_dept2;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

        50 SUPPORT        KWANGJU

-> 창1에서 commit 후 데이터가 나타남

[SAVEPOINT]

- 트랜잭션 작업 중간에 savepoint를 지정이 가능하다.

- 책갈피와 같은 기능

- 이름 부여 가능

Q2. savepont 사용, 50번 부서 수정

1) 데이터 수정하기

update b_dept2 set dname='TEST',loc='SUWON'

 where deptno=50;

1 행이 갱신되었습니다.

SQL> select * from b_dept2;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

        50 TEST           SUWON  // 완전히 처리된 상태는 아님

SQL> rollback;

롤백이 완료되었습니다.

SQL> select * from b_dept2;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

        50 SUPPORT        KWANGJU  // 이전 상태로 되돌리기 가능

Q3. 트랙잭션 시나리오

insert		update		delete
	savepoint 별칭(A)		savepoint 별칭(B)		savepoint 별칭(C)

insert

------- savepoint 별칭(A)

update

------ savepoint 별칭(B)

delete

------ savepoint 별칭(C)

- rollback 하면 insert, update, delete 전부 복구

- rollback to 별칭 A : 별칭 A 이후 rollback (update, delete 복구)

   rollback to 별칭C : deldet 복구

 

1) insert : 데이터 생성

insert into b_dept2 values(60,'TESTING','SEOUL');

1 개의 행이 만들어졌습니다.

SQL> select * from b_dept2;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

        50 SUPPORT        KWANGJU

        60 TESTING        SEOUL

6 개의 행이 선택되었습니다.

2) SAVEPOINT A 설정

savepoint 별칭명;

SQL> savepoint A;

저장점이 생성되었습니다.

3) update : 부서번호 50번의 dname= 'IMSI', loc='BUSAN'으로 수정

update b_dept2 set dname='IMSI', loc='BUSAN'

where deptno=50;

SQL> select * from b_dept2;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

        50 IMSI           BUSAN

        60 TESTING        SEOUL

6 개의 행이 선택되었습니다.

4) savepotin B 설정

SQL> savepoint B;

저장점이 생성되었습니다.

SQL> select * from b_dept2;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

        50 IMSI           BUSAN

        60 TESTING        SEOUL

6 개의 행이 선택되었습니다.

5) delete : 40번 부서 데이터 삭제

SQL> delete from b_dept2

 where deptno=40;

1 행이 삭제되었습니다.

SQL> select * from b_dept2;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

---------------------------------- delete

        50 IMSI           BUSAN -- update

        60 TESTING        SEOUL -- insert

6) rollback B : delete 복구

SQL> rollback to B;

롤백이 완료되었습니다.

SQL> select * from b_dept2;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON  // 5)에서 삭제한 데이터 복구

        50 IMSI           BUSAN

        60 TESTING        SEOUL

6 개의 행이 선택되었습니다.

7) rollback A : update, delete 복구

SQL> rollback to A;

롤백이 완료되었습니다.

SQL> select * from b_dept2;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON // rollback B에서 이미 복구

        50 SUPPORT        KWANGJU // 3)결과를 복구

        60 TESTING        SEOUL

6 개의 행이 선택되었습니다.

8) rollback : 작업 결과 전체 복구

SQL> rollback;

롤백이 완료되었습니다.

SQL> select * from b_dept2;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

        50 SUPPORT        KWANGJU

-> 전체 복구되어 insert 한 60번 부서 삭제됨

*autocommit OFF : 자동으로 commit을 하지 않도로 설정된 상태

-> commit을 입력하기 전까지 복구가 가능