Oracle Chapter10. 트랜잭션 관리(Commit, RollBack) (오라클 입문자용)

현 블로그는 모바일 환경이 아닌 PC환경에 최적화 되어있습니다.

Mir의 운영환경

자 이번Chapter에서는 데이터베이스의 가장 중요한 개념인 트랙잭션에 대해 배워보자.

트랜잭션(Transaction)
트랙잭션이란 데이터 처리의 한 단위이다.
오라클에서 발생하는 여러개의 SQL명령문들을 하나의 논리적인 작업단위로 묶은 것이다.

자 예를 들어 test1이라는 테이블을 만들고 난뒤
1과 3이라는 컬럼을 추가시키고 1이라는 컬럼에 2라는 값을 넣었다. 
그리고 난뒤 3이라는 컬럼에 4라는 값을 추가시키고 난뒤
1의 컬럼의 값을 2에서 5로 수정하였다.
위 글에 사용된 명령어를 살펴보면
CREATE, ALTER, INSERT, INSERT, UPDATE
이렇게 5개의 명령어를 사용하게 되었다.

위에서 사용된 모든 명령문을 하나의 트랜잭션으로 묶을수도 있고 두개의 트랜잭션으로 묶을수도 있다.
즉 트랜잭션은 하나의 명령어만을 가르키는 것이 아니라 명령어의 집합을 가르키는 것이며
우리가 제어할수도 있다는 뜻이다. 

이 트랜잭션은 All-OR-Noting 방식으로 처리되는데 하나의 트랜잭션 안의 여러개의 명령어가
모두 정상적으로 처리 되었다면 정상적으로 종료하지만 그중 하나라도 명령어가 잘못 입력되면 모두 다 취소하게 된다.

예를 들어 은행 현금입출기를 생각해보자.
카드를 넣어서 어떤 DB를 가져올지 검색시키고 난뒤 그 DB에 접근이 가능한지 입력하고(비밀번호 입력)
내가 원하는 값을 조회하고(잔액조회) 값을 수정한뒤(현금인출신청) 돈을 받는다.(인출) 
만약 이러한 거래에서 중간에 기계의 오작동 혹은 명령어의 잘못된 입력으로 오류가 나게 되면
통장에서는  돈이 빠져나갔는데 정작 돈은 나오지 않거나 돈을 받았는데 통장에 잔고는 그대로 유지되는
심각한 문제가 발생하게 된다.

이 때문에 돈을 인출하는 모든 명령을 하나의 트랜잭션으로 묶은뒤에
모든 명령어가 정상적으로 처리가 되어 돈이 인출되었으면 그 값을 저장하고 정상적으로 종료하고 
중간에 오류가 생겼을경우 모두 취소해버리고 난뒤 다시 처음부터 시작하게 되는것이다.


대략적인 트랜잭션에 대한 개념이 잡히는가?

자 그럼 이 트랜잭션을 제어하는 명령어를 살펴보며 좀더 확실한 개념을 잡아보자.
트랜잭션은 COMMIT (트랜잭션의 종료), SAVEPOINT (트랜잭션의 구역지정),  ROLLBACK (트랜잭션의 취소)이라는
3가지 명령어를 이용하여 제어하게 된다.

하나하나 알아보자.

COMMIT
COMMIT은 트랜잭션의 끝과 새로운 시작을 나타내게 된다.
즉 COMMIT을 입력을 기준으로 이전의 트랜잭션은 모두 정상적으로 처리 되었으니 저장하고
이제 부터 새로운 트랜잭션을 시작하겠다는 뜻이다.

SAVEPOINT
SAVEPOINT는 현재까지의 트랜잭션의 특정 이름으로 지정할때 사용된다.
필자가 위에서 하나의 트랜잭션으로 묶을수도 있고 두개의 트랜잭션으로 묶을수도 있다고 말했었는데
정확하게 말하면 하나의 트랜잭션안에 세이브포인트를 주어 구역을 나누게 되는것이다.

ROLLBACK
ROLLBACK는 트랜잭션을 취소하고 난뒤 처음부터 시작하거나 SAVEPOINT부터 여태까지의 트랜잭션을 취소하고
SAVEPOINT를 지정했을때의 상태로 되돌릴때 사용된다.

자 예를 들어 어떤 게임한판을 하나의 트랜잭션이라고 생각해보자.
새로운 게임을 시작하는겠다고 하는것이 COMMIT이다.
그리고 중간 중간 게임을 저장(SAVE)를 하는것이 SAVEPOINT이며
게임을 다시 시작하거나 저장한 부분을 로드하는것이 ROLLBACK이 되는것이다.

자 그럼 실제로 한번 적용 해보도록 하자.

CREATE table test1
AS SELECT * FROM  dept;

우선 dept테이블을 복사한
test1이라는 테이블을 생성하였다.

여기서 우선 COMMIT을 한번 적용 시켜서 이제부터
트랜잭션을 시작한다고 명령내려보자.

자 그리고 난뒤 값을 수정 및 삭제 해보자.
UPDATE test1 SET deptno = 20 ;
UPDATE test1 SET loc = 'SEOUL' WHERE dname = 'SALES';
DELETE FROM test1 WHERE dname = 'OPERATIONS';

위 3개의 명령을 내린뒤의 테이블 내용이다.
그럼 이제 ROLLBACK으로 트랜잭션의 시작위치로 돌아가보자.

위의 COMMIT을 적용시킬때의 상태와 비교해보자.
COMMIT을 적용시킬때의 모습으로 돌아간것을 확인할수 있다.

자 그럼 이번엔 COMMIT을 중간에 한번 넣어보자.
UPDATE test1 SET deptno = 20 ;
COMMIT;
UPDATE test1 SET loc = 'SEOUL' WHERE dname = 'SALES';
DELETE FROM test1 WHERE dname = 'OPERATIONS';

이전과 똑같이 수정 및 삭제를 하였지만 중간에 COMMIT을 한번 적용 시켜주었다.
다시 한번 ROLLBACK을 실행시켜보자.

COMMIT을 적용시킬때의 상태로 돌아가는 것이 확인된다.

이렇게 COMMIT과 ROLLBACK를 이용하면 여러 명령어를 내려 작업할때 혹 중간에 실수를 하더라도 되돌릴수가 있다.
하지만 주의할점이 COMMIT 명령을 내릴때 이전의 명령어를 실수없이 제대로 하였는지 확인을 해야된다.
COMMIT를 한번 적용하고 난뒤에는 그 이전으로 다시 되돌릴수 없기 때문이다.

또한 COMMIT이 자동으로 적용될때가 있는데
CREATE, ALTER, DROP, RENAME, TRUNCATE등의 DDL문을 사용할때에는 자동으로 COMMIT이 적용 된다.

예제에 한번 적용시켜 보자. 위 test1 테이블에 update문과 delete문을 적용시켜보았다.
UPDATE test1 SET loc = 'SEOUL' WHERE dname = 'SALES';
DELETE FROM test1 WHERE dname = 'OPERATIONS';

그리고 난뒤 test1을 복사한 test2테이블을 생성해본뒤에 롤백을 시켜보자.
CREATE  table test2 AS select * from test1;

--> create문에서 자동으로
COMMIT가 실행된다.

 







 

ROLLBACK을 적용시켜도 다시 되돌아가지 않는것이 확인된다.

자 이번엔 TRUNCATE문으로 한번 실험해보자.
우선 비교를 하기 위해 DELETE문을 적용시켜보았다.

DELETE문으로 test1테이블의
모든값을 제거한뒤에
ROLLBACK를 적용하면
다시 DELETE문을 사용하기
이전의 모습으로 되돌아 간 것을
확인할수 있다.









이번에 TRUNCATE문을 사용해보자.

ROLLBACK를 적용 시켜도 이전의 모습으로 돌아가지 않는다.
이렇게 COMMIT는 DDL문을 사용할때 자동으로 적용되니
DDL문을 사용하기전에는 혹 내가 잘못적은것이 없는지 한번더 확인하기 바란다.

여기서 주의할점 한가지!
DDL문은 오류가 나더라도 자동으로 COMMIT가 적용된다.
아래 dept테이블을 복사한 test1테이블이 있다.

이 테이블에 아래와 같은 명령을 내려보았다.
UPDATE test1 SET deptno = 20 ;
UPDATE test1 SET loc = 'SEOUL' WHERE dname = 'SALES';
DELETE FROM test1 WHERE dname = 'OPERATIONS';

자 그리고 난뒤 TRUNCATE를 사용 하는데
일부로 틀린명령을 내려보자.
TRUNCATE TABLE test11;
그후에 롤백을 해보자.

왼쪽 그림을 보면 ROLLBACK를 하여
수정 및 삭제명령을 내리기 전으로 돌아갈려고 
하였지만 TRUNCATE명령어가 오류났음에도
불구하고 자동으로 COMMIT가 적용 되었기 때문에
수정 및 삭제명령을 내리기 전으로 돌아가지
않는다.








이렇게 자동으로 COMMIT가 되어버리면 다시 되돌릴수 없기때문에 항상 주의를 해주어야 한다.

그럼 마지막으로 SAVEPOINT에 대해 알아보자.
SAVEPOINT는 게임과 비교할때에 저장지점이라고 생각하면 된다.
로드로 저장된 게임을 불러내듯이 ROLLBACK로 특정세이브 포인트로 돌아갈수 있다.
예제를 통해 자세히 알아보자.
우선 다시 dept테이블을 복사한 test1테이블을 생성하자.

COMMIT를 적용하고 난뒤 이번에도 수정 및 삭제를 하는데 명령어 하나마다 SAVEPOINT를 적용해보자.
COMMIT;
UPDATE test1 SET deptno = 20 ;
SAVEPOINT A1;
UPDATE test1 SET loc = 'SEOUL' WHERE dname = 'SALES';
SAVEPOINT A2;
DELETE FROM test1 WHERE dname = 'OPERATIONS';
SAVEPOINT A3; 

이처럼 수정 및 삭제된 모습을 확인할수 있다.
자 그럼 저장된 지점으로 한번 이동해보자.
ROLLBACK TO A2;

delete문이 적용되지기 전의 모습으로 돌아간것을 확인할수 있다.
그럼 세이브포인트 지점으로 이동하고 난뒤에 그냥 ROLLBACK를 사용하면 어떻게 될까?
정답은 COMMIT를 적용한 모습으로 돌아가게 된다.

자 그럼 이렇게 ROLLBACK을 적용하고 난뒤에 다시 A3저장 지점으로 돌아갈수 있을까?
정담은 'NO'이다.

COMMIT ------- A1 ------- A2 ---------- A3 
이렇게 저장구역을 정해주었을때 A1지점으로 ROLLBACK를 해버리면 A2와 A3의 SAVEPOINT롤 선언하기 전으로
돌아가기 때문에 A2와 A3의 SAVEPOINT는 사라지게 된다.
그러니 SAVEPOINT 지점으로 ROLLBACK할때에도 항상 주의해야 한다.


트랜잭션의 개념과 트랜잭션을 제어하기 위한 명령어를 알아보았는데
COMMIT과 SAVEPOINT, ROLLBACK는 잘 사용하면 데이터 복구도 가능하며 실수로 내린 명령문의 취소도 가능하다.
하지만 잘못사용하게 되면 완전한 복구가 되지 않거나 자신이 취소하고 싶은 명령어이외에 많은 명령문을 취소할수도 있다.
그러니 COMMIT와 SAVEPOINT, ROLLBACK에 대해 확실한 개념을 잡아두는게 좋을것이다.
제일 실수하기 쉬운 자동으로 적용되는 COMMIT은 꼭 기억해두기 바란다.