메뉴 건너뛰기

bysql.net

제4절_TCL(TRANSACTION_CONTROL_LANGUAGE)

2011.09.20 19:49

노랑배 조회 수:8548

제 4절 TCL(TRANSACTION CONTROL LANGUAGE)


1. 트랜잭션 개요

  • 트랜잭션이란?
    • 데이터베이스의 논리적 연산단위
    • 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작을 의미한다.
    • 하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함된다.
    • 트랜잭션은 분할할 수 없는 최소의 단위이다.
    • 따라서, 전부 적용하거나 전부 취소
    • 즉, 트랜잭션은 ALL OR NOTHING

  • 트랜잭션을 컨트롤하는 TCL (TRANSACTION CONTROL LANGUAGE)
    • 커밋(COMMIT) : 올바르게 반영된 데이터를 데이터베이스에 반영시키는 것
    • 롤백(ROLLBACK) : 트랜잭션 시작 이전의 상태로 되돌리는 것
    • 저장점(SAVEPOINT) 

  • 트랜잭션 대상이 되는 SQL
    • UPDATE, INSERT, DELETE 등 데이터를 수정하는 DML 문.
    • SELECT FOR UPDATE 등 배타적 LOCK을 요구하는 SELECT 문.
      (참고 : http://blog.naver.com/tyboss?Redirect=Log&logNo=70101836923)

  • 트랜잭션 특성 (표 II-1-14 트랜잭션의 특성)
    • 원자성 : 트랜잭션에서 정의된 연산은 모두 성공하던지 아니면 전혀 실행되지 않아야 한다.(all or nothing)
    • 일관성 : 트랜잭션이 실행되기 전  데이터베이스에 이상이 없다면 실행 후 에도 이상이 없어야 한다.
    • 고립성 : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아선 안된다.
    • 지속성 : 트랜잭션이 성공적으로 수행되면 영구적으로 저장된다.



2. COMMIT(커밋)

  • COMMIT이란?
    • 입력, 수정, 삭제한 자료에 대해서 문제가 없을 경우 COMMIT 명령어를 통해서 트랜잭션을 완료한다.

  • COMMIT이나 ROLLBACK 이전의 데이터 상태
    • 단지 메모리 BRFFER에만 영향주고 데이터 변경 이전 상태로 복구 가능하다.
    • 현재 사용자는 SELECT 문으로 변경 결과를 확인할 수 있다.
    • 다른 사용자는 현재 사용자가 수행한 결과를 볼 수 없다.
    • 변경된 행은 잠금(LOCKING)이 설정되어 다른 사용자가 변경할 수 없다.
  • 커밋 예

<Oracle 예>

INSERT 문 / UPDATE 문 / DELETE 문 수행 후


COMMIT;

커밋이 완료되었다.


<SQL Server 예>

INSERT 문 / UPDATE 문 / DELETE 문 수행 후


COMMIT;

커밋이 완료되었다.


# COMMIT 명령어는 데이터 변경 작업이 완료되었음을 데이터베이스에 알려 주기 위해 사용한다.

  • COMMIT 이후의 데이터 상태
    • 데이터에 대한 변경 사항이 데이터베이스에 반영된다.
    • 이전 데이터는 영원히 잃어버린다.
    • 모든 사용자가 결과를 볼 수 있다.
    • 관련된 행에 자금(LOCKING)이 풀리고, 다른 사용자가 행을 조작할 수 있다.

  • SQL Server의 COMMIT
    • 기본적으로 AUTO COMMIT 모드
    • DML 구문이 성공이면 자동으로 COMMIT 된다.
    • 오류가 발생할 경우 자동으로 ROLLBACK 된다.

  • SQL Server의 COMMIT

# SQL Server

- 기본적으로 AUTO COMMIT 모드

- DML 구문이 성공이면 자동으로 COMMIT 된다.

- 오류가 발생할 경우 자동으로 ROLLBACK 된다.


# SQL Server에서의 트랜잭션은 기본적으로 3가지 방식으로 이뤄진다.

- AUTO COMMIT : SQL Server의 기본 방식.

- 암시적 트랜잭션

- 명시적 트랜잭션



3. ROLLBACK(롤백)

  • 롤백이란?
    • 테이블 내 입력, 수정, 삭제한 데이터에 대해서 COMMIT 이전에 변경 사항을 취소할 수 있는 기능
    • 데이터 변경 사항 취소, 관련된 행에 대한 잠금이 풀리고 다른 사람용자들이 데이터를 변경할 수 있다.

  • 롤백 예(Oracle)

INSERT 문 / UPDATE 문 / DELETE 문 수행 후


ROLLBACK;

롤백이 완료되었다.

  • 롤백 예(SQL Server)

 -> SQL Server는 AUTO COMMIT이 기본 방식이므로 ROLLBACK을 수행하려면 트랜잭션을 선언해야 한다.

BEGIN TRAN

INSERT 문 / UPDATE 문 / DELETE 문 수행 후


ROLLBACK;

롤백이 완료되었다.

  • 롤백 후 데이터의 상태
    • 데이터에 대한 변경 사항은 취소된다.
    • 이전 데이터는 다시 재저장된다.
    • 관련된 행에 대한 잠금이 풀리고, 다른 사용자들이 행을 조작할 수 있다.

  • COMMIT과 ROLLBACK을 사용함으로써 얻을 수 있는 효과
    • 데이터 무결성 보장
    • 영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능
    • 논리적으로 연관된 작업을 그룹핑하여 처리 가능



4. SAVEPOINT(저장점)

  • SAVEPINT(저장점)이란?
    • 저장점을 정의하면 롤백할때 전체 롤백이 아닌 일부만 롤백할 수 있다.
    • 복수의 저장점을 정의할 수 있다.
    • 동일 이름으로 저장점을 저장시 나중에 정의한 저장점이 유효하다.

  • 저장점 정의하고 저장점으로 롤백하기

<Oracle 예>

SAVEPOINT SVPT1;


ROLLBACK TO SVPT1;


<SQL Server 예>

SAVE TRANSACTION SVTR1;


ROLLBACK TRANSACTION SVTR1;



- 롤백에 저장점을 부여하여 실행하면 저장점 설정 이후의 변경사항은 원래 상태로 되돌아간다.

  • SAVEPOINT 설정 후 롤백(ROLLBACK)이 이뤄지는 범위


- 저장점 A로 되돌리고 나서 다시 B와 같은 미래로 되돌릴 수 없다.

- 특정 저장점까지 롤백하면 그 저장점 이후에 설정한 저장점은 무료가 된다.

- 저장점 없이 롤백하면 모든 변경 사항을 취소한다.