SQLD - 과목2. (4) TCL

1. 트랜잭션 개요

▷ 트랜잭션 ( TRANSACTION )

    : 데이터베이스의 논리적 연산단위이다.

      밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작이다.

      하나의 트랜잭션에 하나 이상의 SQL문장이 포함된다.

      분할할 수 없는 최소의 단위이다. = ALL or NOTHING 의 개념이다.

 

트랜잭션의 예시로 계좌이체 ( 두 개의 업데이트 작업 ) 는 하나의 논리적인 작업 단위를 구성하는 세부적인 연산들의 집합이다.

데이터베이스 응용 프로그램은 트랜잭션의 집합으로 정의한다.

 

 

 

SELECT 문장은 직접적인 트랜잭션의 대상이 아니다.

그러나, SELECT FOR UPDATE 등 배타적 LOCK 을 요구하는 SELECT 문장은 트랜잭션의 대상이 될 수 있다.

 

 

 

트랜잭션의 특성

 

 

※ LOCKING

 

문에 좌물쇠를 채우듯 하는 것을 잠금 (LOCKING) 이라고 한다.

트랜잭션의 특성(특히 원자성) 충족하기 위해 데이터베이스는 다양한 레벨의 잠금 기능을 제공한다.

잠금은 기본적으로 트랜잭션이 수행하는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한하는 기법이다.

 

* LOCKING 접근

잠금이 걸린 데이터는 잠금을 실행한 트랜잭션만 독점적으로 접근할 수 있다.

다른 트랜잭션으로부터 간섭이나 방해를 받지 않음이 보장된다.

 

* LOCKING 해제

잠금이 걸린 데이터는 잠금을 수행한 트랜잭션만이 해제할 수 있다.

 

 

 

 

 

 

 

---

2. COMMIT

▷ COMMIT

    : 입력/수정/삭제한 자료에 대해서 문제가 없을 때 COMMIT 명령어를 통해서 트랜잭션을 완료한다.

 

 

※ COMMIT 이나 ROLLBACK 이전의 데이터 상태

  (1) 단지 메모리 Buffer 에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구 가능.

  (2) 현재 사용하는 SELECT 문장으로 결과를 확인 가능.

  (3) 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없음.

  (4) 변경된 행은 잠금(LOCKING) 이 설정되어서 다른 사용자가 변경할 수 없음.

 

 

INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, NAME) VALUES ('123','K','KIM');
# 1개의 행이 만들어졌다.

COMMIT;
# 커밋이 완료되었다.

 

COMMIT 명령어는 삽입/수정/삭제 문장 이후 변경 작업이 완료되었음을 데이터베이스에 알려준다.

 

※ COMMIT 이후의 데이터 상태

  (1) 데이터에 대한 변경 사항이 데이터베이스에 반영됨.

  (2) 이전 데이터는 영원히 잃어버리게 됨.

  (3) 모든 사용자는 결과를 볼 수 있음.

  (4) 관련된 행에 대한 잠금(LOCKING) 이 풀리고, 다른 사용자들이 행을 조작 가능.

 

 

---

※ Oracle 의 COMMIT

  (1) DML 을 실행하면, DBMS 가 트랜잭션을 내부적으로 실행함.

  (2) DML 문장 수행 후 사용자가 임의로 COMMIT 혹은 ROLLBACK 을 수행해 주어야 트랜잭션이 종료됨.

 

※ SQL Server 의 COMMIT

  (1) AUTO COMMIT 모드이기 때문에 DML 수행 후 사용자가 COMMIT 이나 ROLLBACK을 처리할 필요가 없음.

  (2) DML 구문이 성공이면 자동으로 COMMIT 됨.

  (3) 오류가 발생하면 자동으로 ROLLBACK 처리됨.

---

※ SQL Server 의 트랜잭션 3가지 방식

 

  (1) AUTO COMMIT SQL Server 의 기본 방식.

      DML, DDL 을 수행할 때마다 DBMS 가 트랜잭션을 컨트롤하는 방식.

 

  (2) 암시적 트랜잭션 (Oracle 과 같은 방식)

      트랜잭션 시작은 DBMS 가 처리하고 트랜잭션의 끝은 사용자가 명시적으로 COMMIT 또는 ROLLBACK 으로 처리함.

      인스턴스 단위 또는 세션 단위로 설정 가능.

      > 인스턴스 단위 - 서버 속성 창의 연결화면에서 기본 연결 옵션 중 암시적 트랜잭션에 체크 해주면 설정됨.

      > 세션단위 - 세션 옵션 중 SET IMPLICIT TRANSACTION ON 을 사용하면 설정됨.

 

  (3) 명시적 트랜잭션 

      트랜잭션의 시작, 끝을 모두 사용자가 명시적으로 지정하는 방식.

      BEGIN TRANSACTION 으로 트랜잭션을 시작,

      COMMIT [TRANSACTION 생략 가능] 또는 ROLLBACK [TRANSACTION 생략 가능] 으로 트랜잭션을 종료.

      ROLLBACK 구문을 만나면 최초의 BEGIN TRANSACTION 시점까지 모두 ROLLBACK 이 수행됨.

 

 

 

 

---

3. ROLLBACK

▷ 롤백 (ROLLBACK)

    : 테이블 내 입력/수정/삭제한 데이터에 대해 COMMIT 이전에는 변경 사항을 취소할 수 있음.

      데이터베이스에서는 롤백(ROLLBACK) 기능을 사용.

      롤백은 데이터 변경 사항이 취소되어 데이터의 이전 상태로 복구되고,

      관련된 행에 대한 잠금(LOCKING) 이 풀리고 다른 사용자들의 데이터 변경이 가능.

      

 

INSERT INTO PLAYER (PLAYER_ID, TEAM_ID) VALUES ('123','K');
# 1개의 행이 만들어졌다.

ROLLBACK;
# 롤백이 완료되었다.

 

 

 

※ SQL Server 의 ROLLBACK

  (1) AUTO COMMIT 모드이 기본 방식이므로 임의적으로 ROLLBACK 을 수행하기 위해 명시적으로 트랜잭션을 선언해야 함.

 

 

SQL Server

BEGIN TRAN
INSERT INTO PLAYER (PLAYER_ID, TEAM_ID) VALUES ('123','K');
ROLLBACK;

 

---

※ ROLLBACK 이후의 데이터 상태

  (1) 데이터에 대한 변경 사항이 취소 됨.

  (2) 이전 데이터는 다시 재저장 됨.

  (3) 관련된 행에 대한 잠금(LOCKING) 이 풀리고, 다른 사용자들이 행을 조작 가능.

 

 

 

※ COMMIT 과 ROLLBACK 사용 후의 효과

  (1) 데이터 무결성 보장.

  (2) 영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능.

  (3) 논리적으로 연관된 작업을 그룹핑하여 처리 가능.

 

 

 

 

 

 

---

4. SAVEPOINT

▷ 저장점 (SAVEPOINT)

    : SAVEPOINT 를 정의하면, ROLLBACK 할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아닌,

      현 시점에서 SAVEPOINT 까지 트랜잭션의 일부만 롤백 가능하다.

 

      복잡한 대규모 트랜잭션에서 에러가 발생했을 때 SAVEPOINT 까지의 트랜잭션만 롤백하고, 실패한 부분에 대해서 다시 실행 가능.

      복수의 저장점 (SAVEPOINT) 를 정의 가능하며, 동일이름으로 저장점을 정의했을 때는 나중에 정의한 저장점이 유효하다.

 

 

SAVEPOINT SVPT1;
// SVPT1 이라는 저장점을 정의

ROLLBACK TO SVPT1;
// 저장점까지 롤백

ROLLBACK 에 SAVEPOINT 명을 부여하여 실행하면, 저장점 설정 이후에 데이터 변경에 대해서만 원래 상태로 되돌아 간다.

 

 

 

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

ROLLBACK TRANSACTION SVTR1;

 

(예제)

// 1. SAVEPOINT 지정
SAVEPOINT SVPT;

// 2. PLAYER 테이블에 있는 데이터를 수정
UPDATE PLAYER SET WEIGHT = 100;

// 3. 롤백 이전에 설정한 저장점까지 실행
ROLLBACK TO SVPT;

 

---

ROLLBACK 원리 (Oracle 기준)

 

특정 저장점까지 롤백하면 그 저장점 이후에 설정한 저장점(SAVEPOINT) 는 무효가 된다.

즉, 'ROLLBACK TO A' 를 실행한 시점에서 저장점 A 이후에 정의한 저장점 B 는 존재하지 않는다.

저장점 지정 없이 "ROLLBACK" 을 실행하면, 반영되지 않은 모든 변경 사항을 취소하고 트랜잭션 시작 위치로 돌아간다.

 

 

 

(예제)

// 1. 새로운 트랜잭션 시작
INSERT INTO PLAYER(PLAYER_ID, TEAM_ID) VALUES ('123','K');

// 2. SAVEPOINT A, B 를 지정
SAVEPOINT SVPT_A;

UPDATE PLAYER SET TEAM_ID = 'U';
SAVEPOINT SVPT_B;


// 3. SAVEPOINT B 까지 ROLLBACK 수행
ROLLBACK TO SVPT_B;

// 4. 최초 시점까지 ROLLBACK 수행
ROLLBACK;

 

 

 

 

 

---

★ 복습 

▷ 트랜잭션

 

* COMMIT 과 ROLLBACK 의 목적 

   : 테이블에 데이터의 변경을 발생시키는 입력/수정/삭제 수행 시 변경되는 데이터의 무결성을 보장하는 것.

 

 

* COMMIT

   : " 변경된 데이터를 테이블이 영구적으로 반영해라"

 

* ROLLBACK

   : " 변경된 데이터가 문제가 있으니 변경 전 데이터로 복귀하라"

 

---

 

* SAVEPOINT / SAVE TRANSACTION  

   : "데이터 변경을 사전에 지정한 저장점(SAVEPOINT) 까지만 롤백(ROLLBACK) 하라"

 

---

 

* Oracle의 트랜잭션

   : 트랜잭션의 대상이 되는 SQL 문장을 실행하면 자동으로 시작된다.

     COMMIT 또는 ROLLBACK 을 실행한 시점에서 트랜잭션이 종료된다.

 

     ( 단, 다음의 경우에는 COMMIT 과 ROLLBACK 을 실행하지 않아도 자동으로 종료 )

      ① DDL 문장 ( CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등 ) 을 실행하면 그 전후 시점에 자동으로 COMMIT 된다.

      ② DML 문장 이후에 커밋없이 DDL 문장이 실행되면 DDL 수행 전에 자동으로 COMMIT 된다.

      ③ 데이터베이스를 정상적으로 접속을 종료하면 자동으로 트랜잭션이 COMMIT 된다.

      ④ 애플리케이션의 이상 종료로 데이터베이스와의 접속 단절이 되면 트랜잭션이 자동으로 ROLLBACK 된다.

 

 

 

* SQL Server의 트랜잭션

   : DBMS 가 트랜잭션을 컨트롤 하는 방식인 AUTO COMMIT 이 기본 방식이다.

 

    ( 단, 다음의 경우에는 COMMIT 과 ROLLBACK 을 실행하지 않아도 자동으로 종료 )

      ① 애플리케이션의 이상 종료로 데이터베이스(인스턴스) 와의 접속 단절이 되면 트랜잭션이 자동으로 ROLLBACK 된다.

 

 

 

 

 

 

---