**중요**
트랜잭션(Transaction)
데이터베이스에서 데이터를 처리하는 나의 논리적인 작업 단위를 의미한다!
데이터 베이스에서 데이터를 관리할때 (insert, update, delete) 여러개의 SQL 명령어를 사용하게 되는데 이를 하나의 작업단위로 설정하는것이 트랜잭션(Transaction) 이다. 여기서 작업 단위는 "어디서 부터 시작해서 언제까지 끝이나는가?"
ex) 계좌이체의 예제
[ 계좌에서 돈을 인출한다 -> 계좌에서 잔액이 차감된다 -> 타 계좌에 돈이 입금된다. -> 계좌에 잔액이 더해진다. ]
A (S은행) -------------------> B (K은행)
-10,000 ---------------------> +10,000
1. 나의 계좌 -10,000원 1차 작업
2. 상대방 계좌 +10,000원 2차 작업
Trasaction_ = (1차 작업 + 2차 작업), 그리고 트랜잭션을 만족시키는 조건이 있다 ->
All(실행이 되면 끝까지 실행) or Nothing(원자성)(중간에 멈출거면 실행하지 말든지)
트랜잭션은 반드시 All or Nothing 조건에 부합하여야 한다 실행하다가 중간에 멈춘다고 과정이 중간에 머물러 있으면 안되고, 중간에 멈춘다면 애초에 실행된 기록또는 실행이 안되어야 하는것이다.
즉 해당 작업을 위한 commit, rollback, savepoint 문을 잘 익혀야 한다.
Commit_
모든 작업들을 정상적으로 처리, 데이터베이스에 모두 반영 => 작업한 내용 (insert, update, delete) 테이블에 저장, 변경된 내용을 모두 영구 저장 => 테이블에 저장 (중간에 값을 변경하지 않는한 영구저장)
* 주의할점) 자동 commit 이라는게 있음. Commit 이 되면, Rollback이 먹질 않는다.
1. create~, alter~, drop~ 으로 시작하는 문장 즉 DDL 문은 해당 문장을 선언하는 순간 자동으로 끝이 난다. 정의어로써 선언이 되는것이기에 Rollback이 되지 않는다. 오라클 10g 이전버전에선 drop~ 명령을 선언하는 순간 기회도 없이 데이터를 모두 삭제 당하는 일이 당연하였지만 사용자들의 많은 항의에 10g 이후버전 부터는 drop 명령을 선언 하더라도 딱 한번 다시 되돌릴수 있는 기회를 준다고 한다. 그리고 DCL문(grant,revoke) 사용권한 명령어도 복구가 불가하다.
DML문은 복구가 가능하다. -> 예외로는 commit 명령어를 사용하기 전까지!
그리하여 오라클에서 exit 명령어를 입력하고 프로그램 종료시 => 자동으로 commit이라 간주를 하여 모든 작업 내용을 테이블에 저장하고 종료 하게된다.
Rollback_
처리과정에서 발생한 변경 사항을 최소 이전의 상태로 되돌린다. => 처음부터 실행하지 않은 상태로 간주, DML만 가능 (예외 commit를 하기전까지)
자동 Rollback_
비정상적인 종료 (정전), 컴퓨터 다운 => 자동 rollback이 일어난다.
**중요**
우선 창을 2개를 열어놓는다.
창 A 에서 백업 테이블을 생성하였고,
SQL> spool c:\webtest\2.db\print\20190703.txt
SQL> create table b_dept2
2 as select * from dept;
테이블이 생성되었습니다.
창 A 에서 데이터를 생성하고 조회 결과
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
이렇게 나오게된다. 하지만 창 B 에서 조회를 하였을때
SQL> select * from b_dept2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
생성한 데이터가 출력되지 않음을 볼수가 있고 이 경우는 현재 창 A에서 commit을 진행하지않았기 때문에 해당데이터는 메모리 에만 저장이되었을뿐 저장장치에 저장이 된것이 아니다.
즉 창 A 에서 반드시 commit을 해야 데이터 로써 저장이 된다는것이 중요 포인트
문제2_
트랜잭션 작업 중간에 savepoint 지정이 가능하다. (savepoint = 책갈피 역할)
SQL> select * from b_dept2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 SUPPORT KWANGJU
해당 데이터를 아래의 구문을 통해 수정하려한다
SQL> 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
데이터를 조회해보면 바뀐것 같지만, 해당 데이터는 현재 메모리에만 올라와있는 상태로 반드시 Commit 을 거쳐야만 지정된 저장장치에 데이터 저장이 되는것이다. 여기서 아직 Commit을 하지 않았기에 Rollback도 가능은 한 상태.
문제3_
시나리오 트랜잭션
1) insert
------------->savepoint 별칭 (A)
2) update
------------->savepoint 별칭 (B)
3) delete
책갈피를 넣어주기전, Rollback 실행시 -> insert, update, delete 전부 복구
만약,
rollback -> insert,update,delete 복구
rollback to 별칭 A -> update와 delete 복구
rollback to 별칭 B -> delete만 복구
의 개념임 그리고
Savepoint 활용_
먼저아래의 데이터 값을 생성해준다.
SQL> insert into b_dept2 values(60,'TESTING','SEOUL');
1 개의 행이 만들어졌습니다.
물론, 현재는 메모리에만 들어간것이고, 저장매체에 저장한것은 아님.
형식으로는 ) savepoint 별칭명;
SQL> savepoint A;
저장점이 생성되었습니다.
이렇게 저장점 즉 A라는 Savepoint(책갈피) 를 저장해두었다.
부서번호(deptno) 50번 데이터를 'IMSI', 위치(loc)를 'BUSAN'으로 수정을 해보겠다.
SQL> update b_dept2 set dname='IMSI',loc='BUSAN' where deptno=50;
1 행이 갱신되었습니다.
이곳에 책갈피 B를 달아놓는다. 즉 B 라는 savepoint를 생성하려고 함.
SQL> savepoint B;
저장점이 생성되었습니다.
그리고 수정됨을 확인함, 아래의 테이블과 같다 savepoint를 지정해두었지만 savepoint는 화면에 나오지 않는다.
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 개의 행이 선택되었습니다.
마지막으로, 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
50 IMSI BUSAN
60 TESTING SEOUL
40번 부서의 데이터를 삭제 하였고, 데이터가 삭제 되었음을 확인하였다.
rollback -> insert,update,delete 복구
rollback to 별칭 A -> update와 delete 복구
rollback to 별칭 B -> delete만 복구
위에서 말한 해당 형식을 참고하여, 아래의 명령을 선언해보았다.
SQL> rollback to B;
롤백이 완료되었습니다.
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 개의 행이 선택되었습니다.
지정한 지점으로부터 저장이 잘 되었음을 확인할 수가 있다. 그리고 다시 A지점으로 복구를 하려한다.
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
50 SUPPORT KWANGJU ===> update 복구 (전)
60 TESTING SEOUL
6 개의 행이 선택되었습니다.
정~말 마지막으로 원점으로 돌아간다면, 기본 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
위 결과를 볼수가 있다. 즉 60번 데이터가 입력 되기 전으로 복구 되었다.
추가설명_
환경변수중 show autocommit라는것이 있다.
SQL> show autocommit;
autocommit OFF ==> 해당 의미는 롤백을 할수있다는 환경변수이다. 이것을 ON으로 바꾸는순간... 롤백이 안된다. 작업전 잘 확인하고 진행하길 !
댓글