SQL_TCL, 트랜잭션, COMMIT, ROLLBACK, SAVEPOINT

김수경·2024년 1월 16일

SQLD 자격증

목록 보기

10/14

1. TCL

TCL

데이터를 삽임, 삭제, 수정하는 DML의 수행 이후 COMMIT이나 ROLLBACK을 하기 위한 SQL

트랜잭션

논리적인 연산의 단위(분할할 수 업는 최소한의 단위)
밀접히 관련되어 분리될 수 없는 한개 이상의 데이터베이스 조작
ex) 은행 이체 과정(송금하고 받기까지) - 중간에 연결이 끊기면 전체가 취소됨

트랜잭션 특징

원자성(Atomicity)
- 모든 연산은 최소한의 조작 묶음, 하나의 트랜잭션을 완료했다는 것은 모든 연산이 정확하게 수행되었다는 의미
- 과정에서 하나라도 실패하면 실패
일관성(Consistency)
- 트랜잭션의 수행 전 수로 잘못된 내용이 없어야 함
- 돈이 없다면 송금되면 안되고, 돈을 받았는데 돈이 이미지로 저장되는게 아니라 돈이 들어와야 함
고립성/격리성(Isolation)
- 트랜잭션이 다른 트랜잭션의 영향을 받아 잘못되면 안됨
- 각각의 트랜잭션은 개별적으로 동작되어야 함
지속성(Durability)
- 트랜잭션이 성공하면 결과가 영구적으로 유지되어야 함
- 특정시간만 유지되고 다시 돌아가면 안됨

2. COMMIT/ROLLBACK

COMMIT

입력, 수정, 삭제한 자료에 대해 문제가 없다고 판단되었을 때 최종 트랜잭션을 확정하는 TCL 명령어
COMMIT하지 않아도 변경된 데이터의 내용을 SELECT 문을 통해 조회 가능하지만, 다른 사람들은 현재 변경 내용을 확인할 수 없음.
COMMIT하면 변경된 내용이 DB에 저장되며 저장되는 동안 LOCKING됨

-- Oracle

INSERT INTO PLAYER VALUES ('1997035', 'K02', '이운재', 'GK', 182, 82, 1);
COMMIT;

UPDATE PLAYER SET HEIGHT = 100;
COMMIT;

DELETE FROM PLAYER;
COMMIT;

ROLLBACK

트랜잭션중 에러가 발생하거나 잘못된 연산이 이뤄진 경우 가장 최신 상태로 되돌릴 수 있음
COMMIT되지 않은 모든 데이터 변경 사항들이 취소됨
관련된 LOCKING이 풀리면서 다른 사용자들이 데이터를 변경할 수 있음

COMMIT과 ROLLBACK의 장점

데이터의 무결성 보장
영구적인 변경을 하기 전에 데이터 변경 사항 확인 가능
논리적으로 연관된 작업을 묶어(Grouping)처리 가능

COMMIT과 ROLLBACK을 하지 않아도 트랜잭션이 종료되는 경우

DDL 문장 실행 시 자동으로 COMMIT
DML 문장 이후에 COMMIT 없이 DDL 문장이 실행될 때
정상적으로 DB 접속을 종료하면 COMMIT
장애로 인한 비정상 종료로 DB 접속이 단절되었을 경우에는 AUTO ROLLBACK

COMMIT과 ROLLBACK을 사용할 때의 차이

DML의 데이터 삽입, 수정, 삭제 이후 COMMIT or ROLLBACK 전 데이터 상태

DB에 최종 적용 전이기 때문에 변경전 상태로 복구 가능
select 구문으로 삽입, 수정, 삭제 결과 조회 가능
- 작업자는 가능
- 작업자 외 다른 사람은 불가능
변경된 행은 LOCKING 설정으로 인해 다른 사용자가 변경할 수 없음

DML의 데이터 삽입, 수정, 삭제 이후 COMMIT한 상황

데이터의 삽입, 수정, 삭제 내역이 DB에 완전 반영
이전 데이터는 완전히 삭제됨
작업자와 모든 사람은 select 구문으로 동일한 결과 조회
LOCKING이 풀리고 모든 사용자는 특정한 행에 조작 가능함

3. SAVEPOINT

SAVEPOINT

트랜잭션 중간에 savepoint설정하여 처음으로 돌아가는 것이 아닌 설정 지점까지만 되돌림

CREATE TABLE pokemon (
  pm_id NUMBER PRIMARY KEY NOT NULL,
  name VARCHAR2(10) NOT NULL,
  attr VARCHAR2(10) DEFAULT 'normal'
);

-- Oracle

INSERT INTO pokemon VALUES (25, 'Pikachu', 'Electric');

-- 첫 번째 SAVEPOINT의 이름 설정
SAVEPOINT SP1;

UPDATE pokemon SET name = 'Raichu';

-- 두 번째 SAVEPOINT의 이름 설정
SAVEPOINT SP2;

DELETE FROM pokemon;

-- 세 번째 SAVEPOINT의 이름 설정
SAVEPOINT SP3;

-- 두 번째 SAVEPOINT로 롤백
ROLLBACK TO SP2;