[SQL] 트랜잭션 제어와 세션

트랜잭션(transaction)

트랜잭션이랑 더 이상 분할할 수 없는 최소 수행 단위를 뜻하며 계좌 이체와 같이 하나의 작업 또는 밀접하게 연관된 작업을 수행하는 한 개 이상의 데이터 조작 명령어(DML)로 이루어지며 즉, 어떤 기능 한 가지를 수행하는 ‘SQL 구문 덩어리’라고 볼 수 있음.

 

데이터 유실이라는 무시무시한 상황을 막으려면 두 가지 방법을 생각해 볼 수 있는데, 하나는 어떤 상황에서든 두 UPDATE문을 모두 완전히 실행하는 것이고 이것이 불가능하다면 두 UPDATE문을 실행하기 전 상태, 즉 아무런 UPDATE문도 실행하지 않는 상태를 유지할 수 있어야함.

 

하나의 트랜잭션 내에 있는 여러 명령어를 한 번에 수행하여 작업을 완료하거나 아예 모두 수행하지 않는 상태, 즉 모든 작업을 취소합니다.

이러한 특성으로 트랜잭션 의미를 ‘ALL OR NOTHING’ 문장으로 설명하기도 함.

그리고 트랜잭션을 제어할 때 사용하는 명령어를 TCL (Transaction Control Language)이라고 함.

 

트랜잭션은 SCOTT같은 데이터베이스 계정으로 접속하는 순간부터 시작되며, 트랜잭션이 종료되기 전까지 여러 SQL구문을 실행하고 트랜잭션을 제어하는 명령(TCL)을 실행할 때 기존 트랜잭션이 끝남. 그리고 그 후에 새로운 트랜잭션을 다시 시작.

** 정확하게는 TCL명령어 외에 DDL (Data Definition Language), DCL (Data Control Language)명령어를 사용할 때 역시 현재 트랜잭션을 끝내고 새 트랜잭션을 시작하는 효과가 있음.

---

트랜잭션을 제어하는 명령어

하나의 트랜잭션으로 묶은 데이터 조작어(DML)의 수행 상태는 모든 명령어가 정상으로 수행 완료된 상태 또는 모든 명령어가 수행되지 않아 취소된 상태, 이 두가지 상태 뿐.

트랜잭션 제어 명령어는 데이터 조작 상태를 이 두가지 상태 중 하나로 유도하는 명령어.

즉, 데이터 조작을 데이터베이스에 영구히 반영하거나 작업 전체를 취소함.

-- DEPT테이블 복사해서 DEPT_TCL테이블 만들기
CREATE TABLE DEPT_TCL
    AS SELECT *
        FROM DEPT;
-- 확인
SELECT * FROM DEPT_TCL;

-- DEPT_TCL 테이블에 데이터를
-- 입력
INSERT INTO DEPT_TCL VALUES(50, 'DATABASE', 'SEOUL');

-- 수정
UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 40;

-- 삭제
DELETE FROM DEPT_TCL WHERE DNAME = 'RESEARCH';

-- 확인
SELECT * FROM DEPT_TCL;

-- ROLLBACK으로 명령어 실행 취소하기
ROLLBACK;

-- 확인
SELECT * FROM DEPT_TCL;

3개의 데이터 조작어는 중간에 별다른 작업이 없었다면 하나의 트랜잭션에 속하기 때문에 이 모든 작업을 취소하고 싶다면 ROLLBACK(롤백) 명령어를 사용.

그럼 테이블을 생성한 직후의 데이터로 되돌아간 것을 확인, 앞에서 실행한 데이터 조작 관련 명령어 실행을 모두 취소 함.

---

트랜잭션을 영구히 반영하고 싶을 때는 COMMIT

ROLLBACK과 달리 지금까지 수행한 트랜잭션 명령어를 데이터베이스에 영구히 반영할 때는 COMMIT 명령어를 사용.

-- DEPT_TCL 테이블에 데이터를 
-- 입력
INSERT INTO DEPT_TCL VALUES(50, 'NETWORK', 'SEOUL');

-- 수정
UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 20;

-- 삭제
DELETE FROM DEPT_TCL WHERE DEPTNO = 40;

--확인
SELECT * FROM DEPT_TCL;

-- COMMIT으로 명령어 방영하기
-- 커밋
COMMIT;

-- 확인
SELECT * FROM DEPT_TCL;

-- 롤백
ROLLBACK;

-- 확인
SELECT * FROM DEPT_TCL;

** COMMIT 명령어는 지금까지 트랜잭션에서 데이터 조작 관련 명령어로 변경한 데이터를 모두 데이터베이스에 영구히 반영한다. 즉, COMMIT 명령어 사용을 기점으로 이전의 추가,변경,삭제는 취소할 수 없을 말하고 ROLLBACK 명령어 역시 이 시점부터는 소용없으므로 COMMIT은 트랜잭션 작업이 정상으로 수행되었다고 확신할 때 사용해야함.

 

** ROLLBACK으로 작업 취소 지점을 지정할 때 SAVEPOINT 명령어를 사용할 수 있지만 자주 사용하지는 않음.

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SAVEPOINT.html

SQL Language Reference

---

세션(session)

세션은 특정 활동 시간이나 기간을 뜻함.

오라클 데이터베이스에서 세션은 데이터베이스 접속을 시작으로 여러 데이터베이스에서 관련 작업을 수행한 후 접속을 종료하기까지 전체 기간을 의미함.