[SQLD] 2과목 SQL 기본및 활용 - 1장 4절 TCL

[SQLD] 2과목 SQL 기본및 활용 - 1장 4절 TCL

1. 트랜잭션 개요.

- 트랜잭션은 데이터베이스의 논리적인 연산단위이다.

- 트랜잭션이란 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작을 가리킨다.

(분할할 수 없는 최소단위)

- 전부 적용하거나 전부 취소한다. ( ALL OR NOTHING)

- 올바르게 반영된 데이터를 데이터베이스에 반영시키는 것을 COMMIT, 트랜잭션 시작 이전 상태로 되돌리는 것을 ROLLBACK 이라고한다. SAVEPOINT 기능과 함께 TCL로 분류한다.

- 트랜잭션의 대상이 되는 SQL 문은 UPDATE, INSERT, DELETE 등 데이터를 수정하는 DML문이다.

- SELECT 문은 트랜잭션 대상은 아니지만 SELECT FOR UPDATE 등 배타적 LOCK을 요구하는 SELECT 문장은 트랜잭션의 대상이 될 수 있다.

* 트랜잭션의 특성 ACID

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

 

2. COMMIT

- 입력한 자료나 수정한 자료에 대해서 또는 삭제한 자료에 대해서 문제가 없다고 판단되었을 경우 COMMIT 명령어를 통해 트랜잭션을 완료할 수 있다.

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

- 단지 메모리 BUFFER에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구 가능하다.

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

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

- 변경된 행은 잠금이 설정되어 다른사용자가 변경할 수 없다.

*COMMIT 이후의 데이터 상태

- 데이터에 대한 변경사항이 데이터베이스에 반영된다.

- 이전 데이터는 영원히 잃어버리게된다.

- 모든 사용자는 결과를 볼 수 있다.

- 관련된 행에 대한 잠금이 풀리고 다른 사용자들이 행을 조작할 수 있다.

* SQL server의 COMMIT

- 오라클은 DML을 수행할 경우 사용자가 트랜잭션을 종료하기 위해 COMMIT 혹은 ROLLBACK을 수행해야 종료된다.

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

DML 구문이 성공적이면 자동으로 COMMIT되고 오류가 발생할 경우 자동으로 ROLLBACK  처리된다.

* SQL server의 트랜잭션 방식

1) AUTO COMMIT

- SQL server의 기본 방식이며 DML, DDL 수행할 때마다 DBMS 가 트랜잭션을 컨트롤 하는방식이다.

명령어가 성공적으로 수행되면 COMMIT을 수행하고 오류가 발생하면 자동으로 ROLLBACK을 수행한다.

2) 암시적 트랜잭션

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

3) 명시적 트랜잭션

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

BEGIN TRANSACTION (BEGIN TRAN 구문도 가능)으로 시작

COMMIT TRANSACTION (TRANSACTION 생략가능)

ROLLBACK TRANSACTION (TRANSACTION 생략가능) 으로 종료한다.

ROLLBACK 구문을 만나면 최초 BEGIN TRANSACTION 까지 ROLLBACK이 수행된다.

3. ROLLBACK

- 테이블에 입력한 데이터나 수정한데이터 삭제한 데이터에 대하여 COMMIT 이전에는 변경사항을 취소할 수있다.

- 변경사항이 취소되어 데이터의 이전상태로 복구하며 관련된 행에대해 LOCK이 풀리고 다른 사용자들이 데이터를 변경할 수 있다.

* SQL server의 ROLLBACK

- AUTOCOMMIT이 기본방식이므로 임의적으로 ROLLBACK을 수행하기 위해서는 명시적으로 트랜잭션을 선언해야한다.

BEGIN TRAN INSERT INTO PLAYER VALUES( OOO, OOO , OOO ....) ROLLBACK ;

*COMMIT과 ROLLBACK을 수행하며 얻는 효과

- 데이터 무결성 보장

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

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

4. SAVEPOINT

- ROLLBACK할 때 트랜잭션에 포함된 전체 작업을 롤백 하는것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다.

- 복수의 저장점을 지원할 수 있으며 동일한 이름으로 저장할 경우 나중에 정의한 저장점이 유효하다.

- 오라클

SAVEPOINT SVPT1 ; ROLLBACK TO SVPT1 ;

 

- SQL server

SAVE TRANSACTION SVPT1 ; ROLLBACK TRANSACTION SVPT1 ;

 

- ROLLBACK 원리 (오라클)

 

 

 - A  저장점으로 돌린 후 B 저장점으로 되돌릴 수 없다. 특정 지점까지 롤백하면 그 저장점 이후에 설정한 저장점이 무효화되기 때문

(ROLLBACK TO A를 실행한 시점에서 A 이후에 저장한 B는 존재하지않는다.)

* ROLLBACK TO B 후 ROLLBACK TO A는 가능하다.

***트랜잭션 정리

- 해당테이블에 데이터의 변경을 발생시키는 입력, 수정, 삭제 수행시 그 변경되는 데이터의 무결성을 보장하는것이 COMMIT과 ROLLBACK의 목적이다.

- COMMIT은 변경된 데이터를 테이블이 영구적으로 반영해라 라는 의미를 갖음.

- ROLLBACK 변경된 데이터가 문제가있으니 변경 전 데이터로 복귀하라는 의미.

- 저장점은 데이터 변경을 저장점까지만 롤백하라는 의미.

- 오라클의 트랜잭션은 SQL문이 시작되면 자동으로 시작되고 COMMIT 또는 ROLLBACK을 수행할 때 종료된다.

- 다음의 경우에는 COMMIT과 ROLLBACK을 수행하지 않아도 자동으로 트랜잭션이 종료됨.

* CREATE, INSERT, DROP, RENAME, TRUNCATE TABLE 등 DDL이 수행되면 그 전후 시점에 자동으로 커밋된다.

* DML문장 이후 커밋없이 DDL을 수행하면 DDL 수행 전에 자동 커밋된다.

* 데이터메이스를 정상종료하면 자동으로 커밋된다.

* 애플리캐이션의 이상 종료로 데이터베이스와 접속이 단절되었을 때는 트랜잭션이 자동 롤백된다.

* SQL Server의 트랜잭션은 AUTO COMMIT이 기본방식이다.

(애플리케이션의 이상종료로 데이터베이스와 접속이 단절되었을 경우에는 오라클처럼 자동 ROLLBACK된다.)