[SQLD] SQL 기본 1-4. TCL (Transaction Control Language)

[4] TCL (Transaction Control Language)

1. 트랜잭션 개요

트랜잭션 : DB의 논리적 연산단위 -> 분리될 수 없는 한 개 이상의 데이터베이스 조작, 즉 분할 할 수 없는 최소의 단위

-> 논리적인 작업 단위를 구성하는 세부적인 연산들의 집합

->원자성 : ALL OR NOTHING

 

TCL : COMMIT커밋, ROLLBACK롤백, SAVEPOINT저장점

 

트랜잭션의 대상 : DML (데이터를 수정하는 UPDATE, INSERT, DELETE) + 배타적 LOCK을 요구하는 SELECT문

 

트랜잭션의 특성

원자성 : ALL OR NOTHING

일관성 : 트랜잭션 실행 전 내용이 올바르면 트랜잭션 이후에도 올바른 내용

고립성 : 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.

지속성 : 트랜잭션이 성공적으로 수행되면 해당 DB의 내용은 영구적으로 저장된다.

 

잠금 (LOCKING) : 트랜잭션이 수행하는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한하는 기법

-> 잠금이 걸린 데이터는 잠금을 실행한 트랜잭션만 독점적으로 접근 가능하며, 잠금이 걸린 데이터는 잠금을 수행한 트랜잭션만이 해제할 수 있다.

 

---

2. COMMIT

COMMIT : 트랜잭션의 완료

 

메모리 BUFFER에 영향을 받기 때문에, 데이터의 변경 이전 상태로 복구 가능

현재 사용자는 SELECT문으로 결과 확인 가능

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

변경된 행은 잠금이 설정되어 다른 사용자가 변경 불가

 

COMMIT은 DML문 이후 변경 작업 완료를 DB에 알려주기 위해 사용되며

COMMIT이 실행되면

데이터에 대한 변경 사항이 DB에 반영

이전 데이터는 영원히 잃어버린다.

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

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

 

---

3. ROLLBACK

ROLLBACK : COMMIT 이전에 변경 사항을 취소하는 방법

데이터 변경 사항이 취소되어 이전 상태로 복구되며, 관련된 잠금이 풀리고 다른 사용자들이 데이터의 변경이 가능하다.

 

COMMIT과 ROLLBACK : 데이터 무결성 보장, 변경 전 데이터 변경 사항 확인 가능, 연관된 작업을 그룹핑해 처리 가능

 

4. SAVEPOINT

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

 

-> 롤백하고 실패한 부분에 대해서만 다시 실행 가능

-> 복수의 저장점 정의 가능, 동일 이름일 경우 나중의 저장점이 유효

 

SAVEPOINT A; -> ROLLBACK TO A;

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

트랜잭션 시작 -> ROLLBACK

 

1. 저장점 지정 후 PLAYER 테이블에 데이터 입력 후 롤백을 이전에 실행한 저장점까지 시행

INSERT

 

SAVEPOINT A -> ROLLBACK TO A

 

2. 저장점 지정 후 PLAYER 테이블에 있는 데이터 수정 후 롤백을 이전에 설정한 저장점까지 실행

UPDATE

 

SAVAPOINT B -> ROLLBACK TO B

 

3. 저장점 지정 후 PLAYER 테이블에 있는 데이터 삭제 후 롤백을 이전에 설정한 저장점까지 실행

DELETE

 

현재 위치

 

-> 저장점 A로 되돌리고 나서 다시 B와 같이 미래 방향으로 돌아갈 수 없다.

-> 특정 저장점까지 롤백하면 그 저장점 이후에 설정한 저장점이 무효가 된다.

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

-> 저장점 지정 없이 ROLLBACK을 실행할 경우 반영안된 변경 사항 취소 후 트랜잭션 시작 위치로 되돌아간다.

 

EX 1. 새로운 트랜잭션 시작 전 PLAYER 테이블의 데이터 건수와 몸무게가 100인 선수의 데이터 건수 확인

EX 2. 새로운 트랜잭션 시작하고 SAVEPOINT A와 SAVEPOINT B를 지정한다.

EX CASE 1. SAVEPOINT B 저장점까지 롤백하고 롤백 전후 데이터 확인

EX CASE 2. SAVEPOINT A 저장점까지 롤백하고 롤백 전후 데이터 확인

EX CASE 3. 트랜잭션 최초 시점까지 롤백 수행하고 롤백 전후 데이터 확인

 

 

커밋과 롤백의 목적 : 데이터의 변경 수행[입력, 수정, 삭제]시 데이터의 무결성 보장하는 것

 

트랜잭션 : 트랜잭션의 대상이 되는 SQL 문장을 실행하면 자동으로 시작, COMMIT 또는 ROLLBACK을 실행한 시점에서 종료

 

커밋과 롤백 실행 없이 자동으로 트랜잭션 종료되는 경우

1. DDL 문장 실행 시 그 전후 시점에 자동으로 커밋

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

3. DB의 정상 종료시 자동 트랜잭션 커밋

4. APP의 이상종료로 DB 접속 단절시 트랜잭션 자동 롤백