[SQLD 과목2 1장-4절] TCL (Transaction Control Language)

1. 트랜잭션 개요

트랜잭션이란?

• DB의 논리적 연산단위
• 밀접히 관련되어 분리될 수 없는 한 개 이상의 DB 조작
• 하나의 트랜잭션은 하나 이상의 SQL 문장으로 구성되어 있음
• 트랜잭션은 분할할 수 없는 최소의 단위이므로 전부 적용되거나 전부 취소된다

 

트랜잭션 용어

• COMMIT: 올바르게 반영된 데이터를 DB에 반영시키는 것
• ROLLBACK: 트랜잭션 시작 이전의 상태로 되돌리는 것
• SAVEPOINT: 저장점

 

TCL(TRANSACTION CONTROL LANGUAGE)란 위의 3가지 명령어를 컨트롤하는 명령을 의미한다.

 

트랜잭션의 특성

특성	설명
원자성 (atomicity)	트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다. (ALL OR NOTHING)
일관성 (consistency)	트랜잭션이 실행되기 전의 DB 내용이 잘못 되어 있지 않다면 트랜잭션이 실행된 이후에도 DB의 내용에 잘못이 있으면 안 된다.
고립성 (isolation)	트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안 된다.
지속성 (durability)	트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 DB의 내용은 영구적으로 저장된다.

 

 

 

2. COMMIT

입력한 자료나 수정한 자료에 대해서 또는 삭제한 자료에 대해서 문제가 없을 때 COMMIT 명령을 통해 트랜잭션을 완료할 수 있다.

 

COMMIT이나 ROLLBACK 이전의 데이터 상태는 다음과 같다.

• 단지 메모리 버퍼에만 영향을 받았기 때문에 데이터 변경 이전 상태로 복구 가능
• 현재 사용자는 SELECT 문장으로 결과 확인 가능
• 다른 사용자는 현재 사용자가 수행한 명령의 결과 확인 불가
• 변경된 행은 잠금(LOCKING)이 설정되어서 다른 사용자가 변경 불가

 

COMMIT 이후의 데이터 상태는 다음과 같다.

• 데이터에 대한 변경 사항이 DB에 반영된다.
• 이전 데이터는 영원히 잃어버리게 된다.
• 모든 사용자는 결과를 볼 수 있다.
• 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.

 

[Oracle의 COMMIT vs SQL Server의 COMMIT]

Oracle은 DML을 실행하는 경우 DBMS가 트랜잭션을 내부적으로 실행하며, DML 문장 수행 후 사용자가 임의로 COMMIT 혹은 ROLLBACK을 수행해 주어야 트랜잭션이 종료된다.

SQL Server는 기본적으로 AUTO COMMIT 모드이기 때문에 DML 수행 후 사용자가 COMMIT이나 ROLLBACK을 처리할 필요가 없다.

 

SQL Server에서의 트랜잭션

• AUTO COMMIT

  SQL Server의 기본 방식이며, DML·DDL을 수행할 때마다 DBMS가 트랜잭션을 컨트롤하는 방식이다. 명령어가 성공적으로 수행되면 자동으로 COMMIT을 수행하고 오류가 발생하면 자동으로 ROLLBACK을 수행한다.

   

• 묵시적 트랜잭션

  Oracle과 같은 방식으로 처리된다. 즉, 트랜잭션의 시작은 DBMS가 처리하고 트랜잭션의 끝은 사용자가 명시적으로 COMMIT 또는 ROLLBACK으로 처리한다. 인스턴스 단위 또는 세션 단위로 설정할 수 있다.

   

• 명시적 트랜잭션

  트랜잭션의 시작과 끝을 모두 사용자가 명시적으로 지정하는 방식이다. BEGIN TRANSACTION 으로 트랜잭션을 시작하고 COMMIT 또는 ROLLBACK으로 트랜잭션을 종료한다. ROLLBACK 구문을 만나면 최초의 BEGIN TRANSACTION 시점까지 모두 ROLLBACK이 수행된다.

   

 

 

3. ROLLBACK

테이블 내 입력한 데이터나, 수정한 데이터, 삭제한 데이터에 대하여 COMMIT 이전에 변경 사항을 취소하고 싶을 때 ROLLBACK 기능을 사용한다.

 

 

 

4. SAVEPOINT

저장점(SAVEPOINT)을 정의하면 롤백(ROLLBACK)할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다. 따라서 복잡한 대규모 트랜잭션에서 에러가 발생했을 때 SAVEPOINT까지의 트랜잭션만 롤백하고 실패한 부분에 대해서만 다시 실행할 수 있다.

 

복수의 저장점을 정의할 수 있으며, 동일이름으로 저장점을 정의했을 때는 나중에 정의한 저장점이 유효하다.

 

[Oracle]
SAVEPOINT SVPT1;  -- SVPT1이라는 저장점을 정의한다.

ROLLBACK TO SVPT1; -- 저장점 SVPT1까지 롤백한다. 

 

위와 같이 롤백에 SAVEPOINT 명을 부여하여 실행하면 저장점 설정 이후에 있었던 데이터 변경에 대해서만 원래 데이터 상태로 되돌아가게 된다.

 

SQL Server는 SAVE TRANSACTION을 사용하여 동일한 기능을 수행할 수 있다.

[SQL Server]
SAVE TRANSACTION SVTR1;

ROLLBACK TRANSACTION SVTR1;

 

[주의] 일단 특정 저장점까지 롤백하면 그 저장점 이후에 설정한 저장점이 무효가 된다.