Oracle SQL

[SQL] 데이터베이스 트랜잭션

YoonJuHan 2023. 5. 30. 20:47

트랜잭션의 개념

  1. 데이터 일관성을 보장하기 위해 오라클 서버는 트랜잭션이라는 단위를 기반으로 데이터 변경사항을 처리
  2. 데이터를 일관성 있게 변경하는 연관된 DML 문으로 구성
  3. 데이터 일관성 보장
    1. 데이터를 변경할 때
    2. 사용자 프로세스가 중단되거나 시스템 장애가 발생한 경우

 

트랜잭션의 시작과 종료

  1. 단일 트랜잭션
    1. 하나 이상의 DML 문
    2. 하나의 DDL 문이나 DCL 문
  2. 트랜잭션은 세션에서 첫 번째 DML 문이 실행될 때 시작
  3. 트랜잭션이 종료되는 이벤트
    1. COMMIT 또는 ROLLBACK 문이 실행되는 경우
    2. DDL 문이 실행되는 경우
    3. DCL 문이 실행되는 경우
    4. SQL*Plus나 SQL Developer를 종료하는 경우
    5. 시스템에 자앵가 있거나 시스템이 고장난 경우

 

트랜잭션의 종료

  1. 명시적 종료
    1. 사용자가 DML 실행 후에 COMMIT이나 ROLLBACK 명령문을 사용한 경우
    2. (참고) TCL : COMMIT, ROLLBACK 과 같은 트랜잭션을 제어하는 명령어
  2. 암시적 종료
    1. DDL 문 또는 DCL 문의 실행

 

데이터베이스 작업단위의 이해

 

TCL을 사용한 트랜잭션의 명시적 처리

  1. COMMIT
    1. 현재까지 보류 중인 모든 데이터 변경 내용을 데이터베이스에 영구 저장하고 현재 트랜잭션을 종료
  2. ROLLBACK
    1. 현재까지 보류 중인 모든 데이터 변경 내용을 취소하고 현재 트랜잭션을 종료
    2. 변경되었던 데이터는 모두 이전 값을 회복
  3. SAVEPOINT
    1. 현재 트랜잭션 내 특정시점에 이름을 주어 표시하는 명령문
    2. ROLLBACK 시 SAVEPOINT 이름을 지정하여 해당 SAVEPOINT 시점 이후의 변경 내용만 ROLLBACK 가능
  4. COMMIT 및 ROLLBACK 문을 수행하여 논리적으로 관련된 작업을 묶음으로써 데이터 일관성이 보장되고 데이터 변경 내용을 영구 저장하기 전에 미리 볼 수도 있다.

 

  • 실습전 emp300테이블이 비어있으면 다음 명령문으로 다시 데이터를 채움
INSERT INTO emp300
SELECT emp_id, emp_name, salary, dept_id
FROM y_emp
WHERE dept_id =300;

COMMIT;

SELECT * FROM emp300;

 

  • SQL Developer를 두 번 실행하여 각각 insa 사용자로 접속

  1. [세션1] 1097 사원의 급여 수정
  2. [세션1] 사원급여 조회(1097의 변경된 급여 확인)
  3. [세션2] 사원급여 조회(1097의 변경 전 급여 확인)
  4. [세션2] 1081 사원의 급여 수정
  5. [세션2] 1097 사원의 급여 수정 시도 → Waiting
  6. [세션1] ROLLBACK 실행
  7. [세션2] 5의 업데이트 성공 및 1097, 1081 사원의 변경된 급여 확인
  8. [세션1] 1081 사원의 변경 전 급여 확인
  9. [세션2] COMMIT 실행
  10. [세션1] 세션2에서 변경한 1097, 1081 사원의 급여가 확인됨

 

COMMIT 문

  1. 변경 후 COMMIT 문이 수행되기 전 데이터 상태
    1. 데이터는 이전 상태로 복구 될 수 있다. (ROLLBACK)
    2. 데이터를 변경한 세션은 변경된 데이터를 본다.
    3. 변경한 세션을 제외한 모든 사용자는 변경된 데이터를 볼  수 없다.
    4. 관련 행에 LOCK이 수행되어 동시에 다른 사용자가 수정할 수 없다.
  2. COMMIT 문이 수행된 이후의 데이터 상태
    1. 데이터는 이전 상태로 되돌릴 수 없다.
    2. 모든 사용자가 변경된 데이터를 볼 수 있다.
    3. 관련 행의 LOCK이 해제되어 다른 사용자가 행을 수정할 수 있다.

 

  • 트랜잭션 실행하면서 중간에 SAVEPOINT를 여러 개 생성
SELECT * FROM emp300;

UPDATE emp300
SET salary = 600
WHERE emp_id = 1037;

SAVEPOINT s1;

UPDATE emp300
SET salary = 670
WHERE emp_id = 1081;

SAVEPOINT s2;

UPDATE emp300
SET salary = salary*1.2
WHERE emp_id IN (1087,1097);

SAVEPOINT s3;

DELETE FROM emp300
WHERE emp_id = 1019;

 

  • emp300에 SAVEPOINT를 사용하여 트랜잭션을 부분 취소
SELECT * FROM emp300;
ROLLBACK TO s3;

SELECT * FROM emp300;
ROLLBACK TO s2;

SELECT * FROM emp300;

 

  • ROLLBACK 명령은 모든 트랜잭션이 롤백하고 SAVEPOINT가 지워진다.
ROLLBACK;

SELECT * FROM emp300;

 

암시적 트랜잭션의 처리

  1. 명시적 COMMIT 또는 ROLLBACK 없이 자동으로 트랜잭션이 종료되는 경우를 의미
    1. 자동 COMMIT되는 경우
      1. 하나의 DDL 문이 실행되는 경우
      2. 하나의 DCL 문이 실행되는 경우
      3. CONNECT 명령어를 사용하여 세션을 변경하는 경우
      4. SQL*Plus를 정상적으로 종료하는 경우(EXIT 명령어 사용)
    2. 자동으로 ROLLBACK 되는 경우
      1. SQL*Plus 또는 SQL Developer가 비정상적으로 종료되는 경우
      2. 시스템에 장애가 발생한 경우

 

롤백의 원리

  1. UNDO Data
    1. DML의 실행으로 변경된 데이터의 이전 값
  2.  UNDO Datafile
    1. UNDO Data를 트랜잭션 종료 전까지 임시로 보관하는 UNDO Data 전용 데이터 파일
  3. DML 처리 방식
    1. 데이터가 변경되면 테이블의 이전 데이터가 UNDO Datafile로 이동
    2. 테이블에는 변경된 데이터를 저장
  4. 롤백의 원리
    1. 명시적 또는 암시적으로 롤백 실행 시 UNDO Data가 다시 테이블의 변경된 데이터를 덮어씀

 

LOCK의 이해

  1. 데이터베이스 시스템에서 동일한 자원을 액세스하는 트랜잭션 간에 발생할 수 있는 파괴적인 상호 작용을 방지하
    기 위한 하나의 방식
    1. 오라클 LOCK은 자동으로 수행
  2. DML(데이터 조작어) 작업을 수행할 때, 오라클 서버는 DML LOCK을 통해 데이터 동시성을 제공
    1. 다른 사용자가 동시에 동일한 행을 다시 수정하지 못하도록 하며 아직 커밋하지 않은 변경 내용을 다른 사용자가 보지않게 한다
  3. LOCK은 COMMIT 또는 ROLLBACK 될 때까지 유효

 

저작자표시 (새창열림)