SQL 기본 (4) TCL

1. 트랜잭션 개요

• ALL or NOTHING (원자성)
• 원자성 + 일관성 + 고립성 + 지속성

2. COMMIT

• buffer 에만 영향을 받앗기 때문에 데이터의 변경 이전 상태로 복구 가능
• 현재 사용자는 select 문장으로 결과를 확인할 수 있으며, 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없음
• LOCKING 이 설정되어 다른 사용자가 변경할 수 없음
• commit 시, 변경사항이 반영됨
• 이전 데이터는 잃어버리게 됨
• 모든 사용자는 결과를 볼 수 있음
• 관련된 행에 대한 Locking 이 풀리고, 다른 사용자가 행 조작 가능

-- sql server

• ORACLE 은 DML 을 실행하는 경우 DBMS 가 트랜잭션을 내부적으로 실행하며 DML 문장 수행 후 사용자가 임의로 COMMIT 혹은 ROLLBACK 을 수행해주어야 트랜잭션이 종료
• SQL Server 는 기본적으로 AUTO COMMIT 모드이기 때문에, DML 구문이 성공이면 자동으로 COMMIT 이 되고, 오류가 발생할 경우 자동으로 ROLLBACK 처리가 된다.
• AUTO COMMIT
  – SQL Server 의 기본 방식이며, DML, DDL을 수행할 때 마다 DBMS 가 트랜잭션을 컨트롤 하는 방식이다. 명령어가 성공적으로 수행되면 자동으로 COMMIT 을 수행하고, 오류가 발생하면 자동으로 ROLLBACK 을 수행한다.
• 암시적 트랜잭션
  – ORACLE 과 같은 방식으로 처리된다. 트랜잭션의 시작은 DBMS 가 처리하고, 트랜잭션의 끝은 사용자가 명시적으로 COMMIT 또는 ROLLBACK 으로 처리한다. 인스턴스 단위 또는 세션 단위로 설정할 수 있다. 인스턴스 단위로 설정하려면 서버 속성 창의 연결화면에서 기본연결 옵션 중 암시적 트랜잭션에 체크하면 된다. 세션 단위로 설정하기 위해서는 세션 옵션 중 SET IMPLICIT TRANSACTION ON 을 사용하면 된다.
• 명시적 트랜잭션
  – 트랜잭션의 시작과 끝을 모두 사용자가 명시적으로 지정하는 방식이다. BEGIN TRANSACTION (BEGIN TRAN) 으로 트랜잭션을 시작하고, COMMIT 또는 ROLLBACK 으로 트랜잭션을 종료한다. ROLLBACK 수행 시 최초 BEGIN TRANSACTION 시점까지 모두 ROLLBACK 이 수행된다.

3. ROLLBACK

• ROLLBACK 은 데이터 변경 사항이 COMMIT 이전의 상태로 복구

• SQL Server AUTO COMMIT 이 기본 방식이므로, 임의적으로 ROLLBACK 을 수행하려면 명시적으로 트랜잭션을 선언해야 한다.

  데이터에 대한 변경 사항이 취소된다.
  이전 데이터는 다시 재저장된다.
  관련된 행에 대한 LOCKING 이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.
  COMMIT 과 ROLLBACK 을 사용함으로써 다음과 같은 효과를 볼 수 있다.

• 데이터 무결성 보장

• 영구적인 변경을 하기 전 데이터의 변경사항 확인 가능

• 논리적으로 연관된 작업을 그룹핑하여 처리 가능

4. SAVEPOINT

-- ORACLE

SAVEPOINT 저장점명;

-- SQL Server

SAVE TRAN 저장점명;

-- ORACLE

ROLLBACK TO 저장점명;

-- SQL Server

ROLLBACK TRAN 저장점명;