[SQLD] 15. TCL (Transaction Control Language)

1. 트랜잭션 개요

1. 데이터베이스의 논리적 연산 단위
2. 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작(UPDATE, INSERT, DELETE, SELECT FOR UPDATE)
3. 분할할 수 없는 최소의 단위로 ALL OR NOTHING
4. 예제(은행의 계좌이체)
   1. 아래 두 작업이 모두 완료되어야 계좌 이체가 성공한다.
   2. 둘 중 하나라도 실패할 경우 모든 수정을 취소하여 원 상태로 되돌려야 한다.
      

      

5. COMMIT: 올바르게 반영된 데이터를 데이터베이스에 반영
6. ROLLBACK: 트랜잭션 시작 이전의 상태로 되돌리는 것
7. LOCK: 트랜잭션이 수행되는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한하는 기법
8. 트랜잭션의 특성
   

   

   
   

   

2. COMMIT

1. COMMIT/ROLLBACK 이전의 데이터 상태
   1. 단지 메모리 버퍼에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구 가능하다.
   2. 현재 사용자는 SELECT 문으로 변경된 결과를 확인 가능하다.
   3. 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.
   4. 변경된 행은 잠금(LOCKING)이설정되어 다른 사용자가 변경할 수 없다.
   5. DML문 이후 DDL이 실행되면 DDL 수행전에 자동으로 커밋된다.
   6. 데이터베이스를 정상적으로 접속을 종료하면 자동으로 트랜잭션이 커밋된다.
2. COMMIT 이후의 데이터 상태
   1. 데이터에 대한 변경 사항이 데이터베이스에 반영된다.
   2. 이전 데이터는 영원히 잃어버리게 된다.
   3. 모든 사용자는 변경된 결과를 볼 수 있다.
   4. 관련된 행에 대한 잠금이 풀리고 다른 사용자들도 행을 조작할 수 있게 된다.

가. SQL Server의 COMMIT

1. SQL Server는 기본적으로 AUTO COMMIT 모드이기 때문에 DML 수행후 사용자가 COMMIT/ROLLBACK을 처리할 필요가 없다.
2. DML 구문이 성공이면 자동 COMMIT, 오류가 발생하면 자동 ROLLBACK된다.
3. SQL Server에서의 트랜잭션 방식
   1. AUTO COMMIT
      1. 기본 방식
      2. DML, DDL을 수행할 때마다 DBMS가 트랜잭션을 컨트롤
   2. 암시적 트랜잭션
      1. 오라클과 같은 방식으로 처리(트랜잭션 시작은 DBMS가 처리하고 끝은 사용자가 COMMIT/ROLLBACK을 통해 처리)
      2. 인스턴스 단위 혹은 세션 단위로 설정 가능
   3. 명시적 트랜잭션
      1. 트랜잭션의 시작과 끝을 모두 사용자가 명시적으로 지정하는 방식
      2. BEGIN TRAN(SACTION)으로 트랜잭션을 시작하고 COMMIT (TRANSACTION)/ROLLBACK (TRANSACTION)으로 트랜잭션을 종료

3. ROLLBACK

1. 테이블내 조작한 데이터에 대하여 COMMIT이전에 변경 사항을 취소하는 명령
2. 데이터 변경사항이 취소되고 관련된 행에 대한 잠금이 풀리고 다른 사용자들이 데이터를 변경할 수 있게 된다.
3. 애플리케이션의 이상 종료료 데이터베이스와의 접속이 단절되면 트랜잭션이 자동 롤백된다.(두 벤더 모두)

가. SQL Server의 ROLLBACK

1. 기본적으로 AUTO COMMIT이기 때문에 ROLLBACK을 수행하기 위해서는 명시적으로 트랜잭션을 선언해야 한다.
   

   

4. SAVEPOINT

1. 롤백시 전체 트랜잭션을 롤백하는 것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백 가능
2. 일부 툴에서는 지원이 안될 수 있다.
3. 여러개의 저장점을 정의할 수 있으며 동일이름으로 정의했을때 나중에 정의한 저장점이 유효하다.
4. 구문
   

   

5. ROLLBACK 원리
   1. 특정 저장점까지 롤백하면 그 이후 설정한 저장점은 무효가 된다.
   2. 저장점 지정 없이 ROLLBACK을 수행하면 모든 트랜잭션의 변경사항이 취소된다.
      

      

6. 예제
   1. 새로운 트랜잭션을 시작하기 전에 PLAYER 테이블의 데 이터 건수와 몸무게가 100 인 선수의 데이터 건수를 확인한다.
      

      

      
      

      

   2. 새로운 트랜잭션을 시작하고 SAVEPOINT A와 SAVEPOINT B를 지정한다. (툴에 AUTO COMMIT 옵션 이 적용되어 있는 경우 해제함)
      

      

   3. SAVEPOINT B 저장점까지 롤백 (ROLLBACK)을 수행하고 롤백 전후 데이터를 확인해본다，
      

      

      
      

      

   4. SAVEPOINT A 저장점까지 롤백 (ROLLBACK)을 수행하고 롤백 전후 데이터를 확인해본다.
      

      

   5. 트랜잭션 최초 시점까지 롤백 (ROLLBACK)을 수행하고 롤백 전후 데이터를 확인해본다.
      

      

       

 

[출처]

wiki.gurubee.net/display/STUDY/4.TCL%28TRANSACTION+CONTROL+LANGUAGE%29