[SQL/oracle] 트랜젝션 관리 (COMMIT/SAVEPOINT/ROLLBACK)

트랜젝션 관리

개념

• 데이터베이스에서는 데이터의 일관성과 무결성을 보장하기 위해서는 반드시 트랜잭션 관리가 필요하다.
• 트랜잭션이란 데이터베이스 내에서 하나의 그룹으로 처리되어야 하는 명령문들을 모아 놓은 작업 단위이다.
• 예를들어 그룹이 하나 이상의 SQL문장으로 구성되어 있다고 가정할 경우 4개의 SQL문장이 하나의 그룹으로 묶여진 작업단위의 경우 4개의 SQL문장이 완전히 처리되거나 All-OR-Nothing방식으로 처리된다.
  • 이것은 트랜잭션 내의 모든 처리대상 명령문들이 반드시 완전히 수행되어야 하며
  • 만약, 모두가 완전이 수행되지 않고, 어느 한 문장이라도 에러가 발생한다면, 트랜잭 묶인 전체 명령문은 모두 취소가 된다.
• 트랜잭션의 형식? 한 트랜잭션을 프로그래밍 언어나 데이터 조작 언어로 개발된 사용자 프로그램 소스에 표기할 때에는 일반적으로 Begin_transaction과 End_transaction으로 나뉘어 트랜잭션 그룹 내에서 처리되어야 할 명령문들을 기술한다.

 

• 트랜잭션의 단위
  • 시스템에서 장애 발생 시 복구 작업을 하게 될 때 사용한다.
  • 병행 제어 시 처리되는 논리적인 작업 단위에 사용한다.
  • 주의할 점! 각 트랜잭션이 데이터베이스 내에서 일관성과 무결성을 유지하기 위해서는 여러개의 트랜잭션 단위를 잘 정의해야 한다.(여러개의 트랜잭션 단위를 잘 정의해야 하는데 이는 프로젝트 설계자 및 어플리케이션 개발자의 책임이다.)
• 예) 부모님 P계좌에서 자식 C계좌로 용돈을 100,000원 이체하는 하나의 트랜잭션에 들어가는 4개의 문장을 정의
  • [1], [2]번까지 잘 실행되다가 [3]번 작업 시 소프트웨어가 중단되거나 하드웨어가 고장이 발생한다면? [2]번까지의 모든 작업을 취소하고 트랜잭션 작업 전 데이터베이스 초기 상태로 돌아가게 된다.

[1]. Select문 - 부모님 P계좌 잔액 검색, 자식 C계좌 잔액 검색 
[2]. Update문 - 부모님 P계좌에서 100,000원 인출 
[3]. Update문 - 자식 C계좌에 100,000원 입금 
[4]. Select문 - 부모님 P계좌 잔액 검색, 자식 C계좌 잔액 검색

• 트랜잭션 명령어
  • COMMIT
  • SAVEPOINT
  • ROLLBACK

 

1) COMMIT과 ROLLBACK

• COMMIT
  • 모든 작업을 정상적으로 처리하겠다고 확정하는 명령어이다.
  • 트랜젝션의 처리 과정을 데이터베이스에 반영하기 위해서, 변경된 내용을 모두 영구 저장한다.
  • COMMIT 수행하면, 하나의 트랜젝션 과정을 종료하게 된다.
  • TRANSACTION(INSERT, UPDATE, DELETE)작업 내용을 실제 DB에 저장한다.
  • 이전 데이터가 완전히 UPDATE된다.
  • 모든 사용자가 변경한 데이터의 결과를 볼 수 있다. 

    

• ROLLBACK
  • 작업 중 문제가 발생했을 때, 트랜젝션의 처리 과정에서 발생한 변경 사항을 취소하고, 트랜젝션 과정을 종료시킨다.
  • 트랜젝션으로 인한 하나의 묶음 처리가 시작되기 이전의 상태로 되돌린다.
  • TRANSACTION(INSERT, UPDATE, DELETE)작업 내용을 취소한다.
  • 이전 COMMIT한 곳까지만 복구한다.
• 트랜젝션 작업 중 하나라도 문제가 발생하면, 모든 작업을 취소해야 하기 때문에 하나의 논리적인 작업 단위로 구성해 놓아야 한다.
  • 문제가 발생하면, 논리적인 작업의 단위를 모두 취소해 버리면 되기 때문이다. 

    

• COMMIT 명령어와 ROLLBACK 명령어의 장점
  • 데이터 무결성이 보장된다.
  • 영구적으로 변경하기 전에 데이터의 변경사항을 확인할 수 있다.
  • 논리적으로 연관된 작업을 그룹화할 수 있다.

---

• COMMIT 추가 설명
  • TRANSACTION(INSERT, UPDATE, DELETE)작업 내용을 실제 DB에 저장한다.
  • 이전 데이터가 완전히 UPDATE된다.
  • 모든 사용자가 변경한 데이터의 결과를 볼 수 있다.
• ROLLBACK 추가 설명
  • TRANSACTION(INSERT, UPDATE, DELETE)작업 내용을 취소한다.
  • 이전 COMMIT한 곳까지만 복구한다.

---

• 자동 COMMIT되는 경우
  • SQL*PLUS가 정상 종료된 경우
  • DDL과 DCL 명령문이 수행된 경우
• 자동 ROLLBACK되는 경우
  • SQL*PLUS가 비정상 종료된 경우
  • 정전이 발생했거나 컴퓨터 DOWN된 경우

 

2) 자동 COMMIT

• DDL문에는 CREATE, ALTER, DROP, RENAME, TRUNCATE등이 있는데 이들 모두는 자동으로 COMMIT을 실행한다.

 

• 예 1) 실습에 사용할 테이블을 생성하는 쿼리문

SQL> DROP TABLE DEPT02;
SQL> CREATE TABLE DEPT02 AS SELECT * FROM DEPT;

• 예 2) 부서 번호가 40번인 부서를 삭제한다.

SQL> DELETE FROM DEPT02 WHERE DEPTNO = 40;
SQL> SELECT * FROM DEPT02;

• 예 3) 부서테이블(DEPT)과 동일한 내용을 갖는 새로운 테이블(DEPT05)을 생성한다.

SQL> CREATE TABLE DEPT05 AS SELECT * FROM DEPT;
SQL> ROLLBACK;
SQL> SELECT * FROM DEPT02; 

 ROLLBACK 명령을 수행하였지만, DELETE된 정보가 되살아나지 않는 이유는 CREATE 명령문이 수행되면서 자동으로 COMMIT이 되었기 때문이다.

• 예 1) 실습에 사용할 테이블을 생성하는 쿼리문

SQL> DROP TABLE DEPT03;
SQL> CREATE TABLE DEPT03 AS SELECT * FROM DEPT;

• 예 2) 부서테이블(DEPT03)에서 부서 번호가 20번인 부서를 삭제

SQL> DELETE FROM DEPT03 WHERE DEPTNO = 20;
SQL> SELECT * FROM DEPT03; 

• 예 3) TRUNCATE문을 실행시키되 테이블명을 일부러 잘못 적어서 에러를 유도한 후, ROLLBACK명령문을 수행

SQL> TRUNCATE TABLE DEPTOX;
SQL> ROLLBACK;
SQL> SELECT * FROM DEPT03; 

 TRUNCATE문이 수행(성공이든 실패든)되면서 자동으로 COMMIT이 발생하였으므로, 되살릴 수 없다.

 

3) SAVEPOINT

• 현재의 트랜젝션을 작게 분할하는 명령어이다.
• 저장된 SAVEPOINT는 ROLLBACK TO SAVEPOINT문을 사용하여 표시한 곳까지 ROLLBACK 할 수 있다.
• 여러 개의 SQL문 실행을 수반하는 트랜젝션의 경우, 사용자가 트랜젝션 중간 단계에서 SAVEPOINT를 지정할 수 있다.
• 이 SAVEPOINT는 차후 ROLLBACK과 함께 사용하여 현재 트랜젝션 내의 특정 SAVEPOINT까지 ROLLBACK 할 수 있게 된다. 

  

   첫번째 COMMIT과 두번째 COMMIT사이(2, 3, 4번의 SQL문)가 한 트랜젝션이다. 한 트랜젝션 중간 중간에 SAVEPOINT를 두어 SAVEPOINT를 지정한 위치로 ROLLBACK가 가능하다.

 

 

 

출처 :

https://wikidocs.net/4095

https://wikidocs.net/4096

https://wikidocs.net/4097

https://wikidocs.net/4098