[데이터베이스]TCL(Transaction Control Language) - commit, rollback, savepoint - SQL, 오라클(ORACLE)

안녕하세요. 신기한 연구소입니다.

DML(Data Manipulation language)의 범주에 속한다는 TCL(Transaction Control Language)에 대해 알아봅니다.

DML의 범주에 속하지 않는다는 의견도 있지만 그보다 TCL에 대해 잘 정리하는 게 중요한 듯합니다.

오라클 기반에서 설명하겠습니다.

기업에서 내부 이체를 하는 경우를 생각해봅니다.

A라는 계좌의 돈을 B 계좌로 이체하는 경우입니다.

A에서 출금한 뒤 B로 입금이 완료되었습니다.

기업의 자금 시스템에서 해당 이체에 대한 결과를 수신했는데..

A 계좌에서 출금된 기록을 입력한 뒤 시스템 장애나 또는 B 계좌에 입금 처리하는 프로그램 오류로 인해서 B 계좌의 입금내역을 처리하지 못하게 된다면 큰 문제가 발생할 수 있습니다.

A 계좌에서는 돈이 나갔다고 하는데 B 계좌에서는 입금내역이 없는 것이죠.

물론 기업 내 자금 시스템 문제라면 실물 금액은 잘 이체되고 통장에는 잘 정리가 되어 있기에 나중에 대사 하는 과정에서 잘 정리하고 오류를 수정 및 개선하면 됩니다.

그렇지만 은행 시스템이라면 정말 심각한 문제이지요.

그래서 이렇게 하나 이상의 프로세스가 서로 밀접하게 연결되어서 일괄 처리할 수 있도록 논리적 그룹 또는 집합을 구성하는데 이를 트랜잭션(Transaction)이라고 합니다.

위의 경우를 보면 A 계좌의 출금 내역 갱신과 B 계좌의 입금내역 갱신은 서로 밀접하게 연결된 프로세스로 하나만 잘 되면 안 되고 두 개의 프로세스가 같이 실행되거나 취소돼야 데이터의 일관성을 유지할 수 있기에 하나의 논리적 그룹으로 묶어서 처리해야 합니다.

트랜젝션은 데이터의 변화에 대한 처리를 지원하기에 Insert, Update, Delete가 대상이 됩니다.

트랜젝션(Transaction)은 commit, rollback, savepoint 세 가지로 구성됩니다.

세가지 구성에 대해 알아봅니다.

COMMIT;

먼저 위 예제를 확인했을 경우 

A 계좌에 출금 내역 입력, B계좌의 입금내역 입력으로 

두 개의 insert문이 일괄 실행됩니다.

commit문이 실행되기 전은 두 개의 insert문 실행으로 메모리에 임시로 데이터가 입력된 상태입니다.

모든 사용자가 볼 수 있는 실제 입력이 완료된 상황이 아니고 작업자만 확인할 수 있는 상태입니다.

또한 commit 전이기에 해당 행은 락이 걸린 상태라 다른 사용자가 접근해서 변경할 수 없는 상태입니다.

오류가 없거나 데이터 입력이 확신이 되면 commit;문을 실행해서 실제로 데이터베이스에 입력됩니다.

[예시]

UPDATE ACCOUNT

SET REMAIN = 50000

WHERE ACCOUNT_NO = '100-100-100000';

1건 수정완료...

COMMIT; 

커밋완료...

 

ROLLBACK;

두 개의 입력문을 실행했는데..

입력문을 보니 오류가 있습니다.

그래서 다시 작업을 해서 입력문을 실행해야 한다면 

현재 메모리에 임시 반영된 입력 데이터를 지우고 락을 제거해야 합니다.

그때 Rollback;을 사용합니다.

Rollback;를 실행하면 두 개의 insert을 실행하기 전 상태로 돌아갑니다.

단, commit;을 하고 rollback;을 하는 건 의미가 없습니다.

commit; 전에 사용할 수 있습니다.

commit; 전에 실행한 모든 insert, update, delete문에 대해 원복 한다는 점을 기억하세요.

[예시]

DELETE FROM ACCOUNT

WHERE ACCOUNT_NO = '100-100-100000';

1건 삭제완료...

ROLLBACK; 

롤백완료...

 

SAVEPOINT;

실전에서 사용한 적은 없습니다만..

한 번에 처리해야 할 프로세스가 많은 상황에서 중간 부분까지 취소를 하고 오류 수정 후 다시 실행할 필요가 있는 경우에 사용하면 편리하겠더군요.

어떤 경우가 있을지 생각해봤는데요.

하나의 테이블로 관리되던 재고 자료를 기본정보와 부가정보로 나눠서 입력하는 경우를 생각해봅니다.

데이터양이 많기에 한번 실행하면 

기본정보가 6시간 부가정보가 4시간 정도로 

총 10시간 정도 걸린다고 가정해봅니다.

일괄처리라 sql문을 쭉 실행했는데 부가정보 중간 부분에 오류가 발생합니다.

헉~

rollback 하고 다시 수정 후 진행하려면 엄청난 시간이 소요되겠지요.

그때 중간에 savepoint를 활용해서 rollback를 해당 savepoint까지만 진행하고 오류 수정 후 다시 진행할 수 있기에

효율적으로 업무를 진행할 수 있게 됩니다.

 

[예시]

--기본정보 입력

INSERT INTO EMP (EMP_ID, EMP_NM)

VALUES ("00001", "홍순이");

INSERT INTO EMP (EMP_ID, EMP_NM)

VALUES ("00002", "강돌이");

...

INSERT INTO EMP (EMP_ID, EMP_NM)

VALUES ("99985", "공이돌");  --99985건 입력

SAVEPOINT SV01;

--부가정보 입력

INSERT INTO EMP_SUB (EMP_ID, EMP_SEQ, BIRTH)

VALUES ("00001", 1, "20030202");

...

INSERT INTO EMP_SUB (EMP_ID, EMP_SEQ, BIRTH)

VALUES ("99985", 99985, "20100309"); --99985건 입력

 

COMMIT;를 하면 전체 입력 완료~

ROLLBACK;을 하면 전체 입력 초기화~

ROLLBACK TO SV01;을 하면 제일 마지막 INSERT문에서 위로 올라가 SAVEPOINT SV01; 바로 전까지 ROLLBACK를 실행합니다.

SAVEPOINT는 중간중간 필요한 부분에 추가해서 넣을 수 있으며 하단부터 위로 ROLLBACK TO SAVEPOINT를 진행하면서 데이터를 확인 및 처리할 수 있습니다.

 

잘못된 정보나 수정이 필요하면 댓글 주세요.

감사합니다.