[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 기본 #4

<목차>

• 트랜잭션 개요

• COMMIT

• ROLLBACK

• SAVEPOINT

---

<< TCL (Transaction Control Language) >>

* COMMIT

   : SQL문의 결과를 DB에 반영시키는 작업

* ROLLBACK

   : SQL문의 결과를 DB에 반영시키지 않고 트랜잭션 시작 이전의 상태로 되돌리는 작업.

* SAVEPOINT

   : ROLLBACK의 시점을 정할 수 있는 지점
* 트랜잭션의 대상이 되는 SQL문은 DML문(INSERT, UPDATE, DELETE, SELECT)
* SELECT 문 중에서도 SELECT FOR UPDATE 등 배타적 LOCK을 요구하는 SELECT 문은 트랜잭션의 대상이 됨

---

 

1. 트랜잭션 개요

1. 정의
   * 데이터베이스의 상태를 변환시키는 하나의 논리적 기능을 수행하기 위한 일련의 연산.
   

2. 특징
   * 하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함.
   * 트랜잭션은 그 자체로 하나의 논리적인 단위이므로 더 이상 분할할 수 없음.---> 그렇기 때문에 전부 적용하거나 전부 취소. (All or Nothing)

3. 예시
   * 내 계좌에서 상대의 계좌로 10,000원을 송금하는 경우

   1. 작업 A. 내 계좌에서 -10,000, 작업 B. 상대 계좌에서 +10,000. 작업 A과 B는 분할 할 수 없고, 일부만 적용할 수 없음.

   2. 작업A + 작업B를 더해서 하나의 논리적 기능을 수행하는 단위로 정의 : 트랜잭션

4. 특성

   1. 원자성 (Atomicity)

      * 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되거나 모두 실행되지 않아야 하는 성격.

      * 원자성을 충족하기 위해 DB는 다양한 레벨의 잠금(LOCK) 기능을 제공.

      * LOCK은 기본적으로 트랜잭션이 수행하는 동안 다른 트랜잭션이 처리 중인 데이터에 동시에 접근하지 못하도록 제한.

      * COMMIT/ROLLBACK 작업 후 LOCK이 해제. (LOCK을 수행한 트랜잭션이 해제 가능)

   2. 일관성 (Consistency)

      * 트랜잭션 작업 처리의 결과가 항상 일관성이 있어야 하는 성격.

   3. 고립성 (Isolation)

      * 트랜잭션이 실행되는 도중, 다른 트랜잭션의 영향을 받을 수 없는 성격.

   4. 지속성 (Durability)

      * 트랜잭션이 성공적으로 수행되면 그 결과는 영구적으로 반영되어야 한다는 성격.

       

 

2. COMMIT

* DML 문이 수행된 후에 영향받은 데이터에 문제가 없다고 판단이 되면 COMMIT 명령어를 통해 DB에 반영하여 트랜잭션을 완료.

 

* COMMIT/ROLLBACK 이전의 데이터 상태는 아래와 같다.

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

2. 현재 사용자는 SELECT문으로 결과를 확인할 수 있음.

3. 다른 사용자는 현재 사용자가 수행한 명령의 결과를 확인할 수 없음.

4. 변경된 행은 LOCK이 설정되어 다른 사용자가 변경할 수 없음.

* COMMIT 이후의 데이터 상태는 아래와 같다.

1. 데이터에 대한 변경이 DB에 반영.

2. 이전 데이터는 모두 잃게 됨.

3. 모든 사용자가 결과를 확인할 수 있음.

4. 영향받은 행의 LOCK 이 해제되어 다른 사용자가 조작할 수 있게 됨.

 

* Oracle VS SQL Server

1. Oracle

   * DBMS가 트랜잭션을 내부적으로 실행하며 DML 문장 수행 후 사용자가 임의로 COMMIT/ROLLBACK 처리를 해주어야 트랜잭션이 종료.

   * 일부 툴에서는 AUTO COMMIT 옵션으로 변경 가능.

2. SQL Server

   * 기본적으로 AUTO COMMIT 모드이기 때문에 DML 수행 후 임의로 COMMIT/ROLLBACK 처리 할 필요가 없음.

   * 에러가 발생하지 않으면 COMMIT, 에러가 발생하면 ROLLBACK

   * 트랜잭션 방식

      - AUTO COMMIT : 기본 방식.

      - 암시적 트랜잭션 : Oracle과 같은 방식. 사용자가 COMMIT 명시.

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

 

3. ROLLBACK

DML 문이 수행된 후에 영향받은 데이터에 대해서 COMMIT 이전에는 변경 사항을 다시 트랜잭션 실행 이전으로 되돌릴 수 있는 기능.

 

* ROLLBACK 이후의 데이터 상태는 아래와 같다.

1. 데이터 변경 사항이 모두 취소되며 영향받은 행에 대한 LOCK이 해제.

2. 다른 사용자들이 데이터를 조작할 수 있게 됨.

3. DML에 의해 변경되기 전의 데이터는 다시 재저장.

* ROLLBACK / COMMIT 을 통해 얻는 효과

1. 데이터 무결성 보장. 

2. 영구적인 변경 전에 데이터의 변경 사항 미리 확인 가능.

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

* 다양한 상황에서의 자동 ROLLBACK / COMMIT

1. DML 문 이후 COMMIT이나 ROLLBACK 없이 DDL(CREATE, DROP, ALTER, TRUNCATE, RENAME) 문을 실행하면 자동으로 COMMIT 됨 (Oracle)

2. DB를 정상적으로 접속 종료하면 자동으로 COMMIT 됨.

3. 애플리케이션의 이상 종료로 DB와의 접속이 단절되었을 때는 자동으로 ROLLBACK 됨.

 

4. SAVEPOINT

 * ROLLBACK 할 때, 트랜잭션에 포함된 전체 작업을 취소하는 것이 아닌, 특정 시점까지 일부만 취소할 때 사용하는 명령어. 

1. 특징

   * 복수의 SAVEPOINT 정의 가능.

   * 동일한 이름으로 SAVEPOINT를 정의했을 때는 나중에 정의한 것이 유효.

2. 예시 - Oracle

   SAVEPOINT SVPT1;    -- 저장점 정의
   ROLLBACK TO SVPT1;  -- 저장점으로 롤백

3. 예시 - SQL Server

   SAVE TRANSACTION SVTR1;     -- 저장점 정의
   ROLLBACK TRANSACTION SVTR1; -- 저장점으로 롤백

4. ROLLBACK 원리 - Oracle 기준

   

   * 특정 시점으로 롤백하면 그 시점 이후의 저장점은 무효처리. (A로 갔다가 B로 갈 수 없음.)

 

 

 

출처

http://www.dbguide.net/db.db?cmd=view&boardUid=148192&boardConfigUid=9&categoryUid=216&boardIdx=134&boardStep=1

데이터 전문가 지식포털 DBGuide.net

질문은 댓글로 남겨주시면 되겠습니다. 감사합니다.