[SQL 19] 트랜잭션 & TCL(COMMIT, ROLLBACK, SAVEPOINT)

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

No. 01
트랜잭션이란?

트랜잭션이란 데이터베이스에서 하나의 논리적 기능을 수행하는 연산자들의 집합이다. 즉, 사용자의 의도에 따라 여러 개의 문장으로 구성된 트랜잭션은 반드시 동시에 실행(COMMIT) 되거나 취소(ROLLBACK)된다. 트랜잭션을 통해 작업의 단위를 결정함으로써 작업 결과의 신뢰성이 확보된다.

예를 들어 계좌 이체를 한다고 해보자. A라는 사람이 소유 계좌의 금액 중 1000만원을 B라는 사람의 계좌로 이체한다면 취소 두 가지 작업으로 분리할 수 있다. 첫 번째 작업은 A의 계좌에서 1000만원이 출금되는 것이고, 두 번째는 B의 계좌에 1000만원이 입금되는 것이다. 그런데 만약 A의 계좌에서 1000만원이 출금된 이후 두 번째 작업이 수행되기 전에 시스템이 다운되면 B의 계좌에 돈이 입금되지 않고 A의 계좌에서만 1000만원이 없어지게 될 수 있다. 이런 문제를 방지하기 위해 두 개의 작업을 하나의 트랜잭션으로 묶어 놓으면 두 개의 작업이 모두 실행되지 않으면 모두 취소되도록 하여 거래의 신뢰도를 높일 수 있다. 즉 두 번째 작업인 입금이 실행되지 않으면 첫 번째 작업은 자동으로 취쇠되도록 하는 것이다.

No. 02
트랜잭션의 시작과 종료

I. 시작

 - 이전 트랜잭션이 종료된 이후 DML(INSERT, UPDATE, DELETE) 문장이나 DDL(CREATE, ALTER, DROP, TRUNCATE), DCL(GRANT, REVOKE) 문장이 실행됐을 때 시작된다.

II. 종료

 - COMMIT이나 ROLLBACK 명령이 실행될 때 종료된다.

 - DDL이나 DCL 문장은 실행이 완료되면 자동으로 종료된다.

 - 사용자의 정상 종료 시에 종료된다.

 - 데드락(Deadlock)이 걸리면 트랜잭션의 일부만 종료된다.

 

트랜잭션은 DDL이나 DCL의 경우 문장이 실행되고 난 후 자동으로 종료된다.

하지만, DML인 경우 반드시 COMMIT이나 ROLLBACK으로 종료해야 한다.

오라클의 경우 SAVEPOINT 명령을 이용하여 트랜잭션의 과정을 제어할 수 있지만 실용적이지 않아 거의 사용하지 않는다.

No. 03
TCL(COMMIT, ROLLBACK, SAVEPOINT)

① COMMIT

사용:

commit;

• 한 작업의 논리적 단위가 성공적으로 끝났고, 데이터베이스가 다시 일관된 상태에 잇으며 이 트랜잭션이 행한 갱신 연산이 완료된 것을 트랜잭션 관리자에게 알려주는 연산
• 작업을 완료한다.
• SQL 명령어로 수행된 결과를 실제 물리적 디스크로 저장하는 SQL 명령
• 모든 DML 문장을 수행한 후 작업을 완료할 때 반드시 필요하다.

DML 문장을 수행한 후 테이블의 정보를 검색해보면 문장의 수행이 반영된 것을 볼 수 있다. 하지만, 이것은 메모리상에서만 처리된 것이다. COMMIT 명령을 수행해야지만 실제 하드디스크에 저장된다.

예를 들어 '한글 2010' 프로그램에서 문서작업을 하고 있다고 하자. 문서를 작성하는 것이 실시간으로 화면에 보여지고 프린트로 출력도 가능하다. 하지만 저장하지 않은 상태에서 프로그램을 껏다가 다시 작업하던 파일을 열면 지금까지 작업한 내용은 남아 있지 않다. 예와 같은 이치로 COMMIT을 하지 않고 데이터베이스를 종료했다가 다시 키면 이전에 했던 DML 작업은 안한 것과 같다. COMMIT 명령을 해야만 실제 디스크에 저장되고 데이터베이스를 껏다가 다시 키더라도 남아 있다.

② ROLLBACK

사용:

rollback;

• 트랜잭션의 실행을 취소하였음을 알리는 연산자로 트랜잭션이 수행한 결과를 원래의 상태로 원상 복귀시키는 연산
• 작업을 취소한다.
• 모든 DML 문장을 수행한 후 작업을 취소할 때 반드시 필요하다.

오라클은 작업이 발생하면 작업 이전 정보를 undo tablespace 라는 임시 저장 공간을 이용하여 저장하고 있는다. undo tablespace는 자동으로 관리되며 사용자는 이들 정보를 직접 볼 수 없으며 단지 트랙잭션에만 사용된다. 그래서 ROLLBACK을 실행하면 DML 작업에 의해서 변경된 정보를 이전 정보로 환원할 수 있다.

③ SAVEPOINT

사용:

savepoint savepoint_name;

• 현재 트랜잭션 내에 저장점을 만든다.

ROLLBACK을 실행하면 transaction 시작 전인 원래의 상태로 되돌아 간다. 그러나 트랜잭션 내에 SAVEPOINT를 이용하여 저장점을 만들어 놓으면 ROLLBACK TO savepoint_name 명령으로 SAVEPOINT를 지정해 놓은 지점으로 되돌아 갈 수 있다. 여기서 주의할 것은 ROLLBACK으로 돌아간다고 해서 저장이 된다고 착각하지 말자. 당연히 COMMIT 명령을 실행해야만 실제디스크에 저장된다.

TCL 실습은 DML 문장을 다룰 때 같이 해보도록 하겠다.