개발 공부/Oracle

[Oracle] - 트랜잭션 제어와 세션

징_ 2023. 12. 7. 17:30

* 트랜잭션

- 트랜잭션이란?

: 하나의 작업 또는 연관성이 높은 작업수행을 위해 더 이상 분할할 수 없는 최소 수행 단위 (어떤 기능 한 가지를 수행하는 SQL문 덩어리

 

 

 

=> 모두 하거나(ALL)

 

 

 

 

=> 모두 하지 않음(NOTHING)

 

 

-------------트랜잭션---------------
-- A 계좌 잔액을 0원으로 변경
UPDATE ACCOUNT
   SET BALANCE = BALANCE - 1000000
 WHERE ACCNO = 'A 계좌번호';
 
-- A 계좌 잔액을 100만원으로 변경
UPDATE ACCOUNT
   SET BALANCE = BALANCE + 1000000
 WHERE ACCNO = 'B 계좌번호';
 ----------------------------------

=> 하나의 UPDATE문만 실행될 수 없다. 모두 실행되거나 모두 실행되지 않아야 함.

  • 한 개 이상의 데이터 조작 명령어(DML)로 이루어짐
  • ALL OR NOTHING : 하나의 트랜잭션 내에 있는 여러 명령어를 한 번에 수행하여 작업을 완료하거나 아예 모두 수행하지 않은 상태(모든 작업 취소)
  • TCL (Transaction Control Language) : 트랜잭션을 제어하는 명령어

 

 

 

 

* 트랜잭션을 제어하는 명령어(TCL)

: 데이터 조작을 영구히 반영(COMMIT)하거나 작업 전체를 취소(ROLLBACK)하는 명령어

 

 

- 트랜잭션 취소 - ROLLBACK

: 모든 작업 수행을 취소하는 명령어

 

<예시>

-----------------------트랜잭션------------------------
INSERT INTO DEPT_TCL VALUES(50, 'DATABASE', 'SEOUL');
UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 40;
DELETE FROM DEPT_TCL WHERE DNAME = 'RESEARCH'; --20 삭제
------------------------------------------------------

=> ROLLBACK 전

ROLLBACK;

=> ROLLBACK 후

 

 

 

- 트랜잭션 영원히 반영 - COMMIT

: 수행한 트랜잭션 명령어를 데이터베이스에 영구히 반영하는 명령어

  • COMMIT명령어 사용 기점으로 ROLLBACK 명령어가 소용 없음
COMMIT;

 

 

 

 

 

* 세션과 읽기 일관성의 의미

- 세션이란?

: 데이터베이스 접속을 시작으로 접속을 종료하기까지 전체 기간

  • 하나의 세션에 여러개의 트랜잭션 존재
  • 세션이 여러 개 : DB에 접속하여 사용 중인 연결이 여러 개

ex) 오라클 데이터베이스 토드로 SCOTT 계정 접속 / 명령어 프롬프트로 SQL* PLUS로 SCOTT 계정 접속

 

 

 

- 읽기 일관성의 중요성

: 특정 세션에서 수행하는 데이터의 변경이 확정되기 전까지, 다른 세션에서는 본래의 데이터를 보여줌

 

<예시>

세션 1은 오라클 세션 2는 SQL * PLUS 세션은 총 2개

 

  • - DEPT_TCL 테이블 생성
 CREATE TABLE DEPT_TCL
     AS SELECT * FROM DEPT;

 

  • DEPT_TCL 테이블 조회
SELECT * FROM DEPT_TCL;

 

- 오라클

- SQL * PLUS

 

  • 오라클에서 50번 부서 삭제
-- 오라클에서 삭제
DELETE FROM DEPT_TCL
 WHERE DEPTNO = 50;
 -- 조회 
SELECT * FROM DEPT_TCL;

 

- 오라클

=> 50번 부서 삭제

 

- SQL * PLUS

=> 그대로임

 

 

  • 오라클에서 커밋

: SQL* PLUS 결과 값 바뀜

=> 50번 부서 삭제됨

 

 

 

 

 

* 수정 중인 데이터 접근을 막는 LOCK 

- LOCK이란?

: 데이터 잠금 형상으로 조작 중인 데이터를 다른 세션은 조작할 수 없도록 접근을 보류시키는 것

 

 

- LOCK 개념

- HANG(행)

: 특정 세션에서 데이터 조작 완료될 때까지 다른 세션에서 해당 데이터 조작을 기다리는 현상

 

<예시>

  • DEPT_TCL 테이블 생성
 CREATE TABLE DEPT_TCL
     AS SELECT * FROM DEPT;

 

  • DEPT_TCL 테이블 조회
SELECT * FROM DEPT_TCL;

 

- 오라클

- SQL * PLUS

 

  • 오라클에서 40번 부서 삭제
DELETE FROM DEPT_TCL
 WHERE DEPTNO = 40;

 

- 오라클

 

- SQL * PLUS

=> 커밋 전

=> 커밋 후 

 

  • 오라클에서 30번 부서 데이터 변경
UPDATE DEPT_TCL 
   SET LOC = 'SEOUL'
 WHERE DEPTNO = 30;

 

- 오라클

- SQL * PLUS

=> 커밋 전

 

  • SQL * PLUS에서 30번 부서 이름 변경
 UPDATE DEPT_TCL 
   SET DNAME = 'DATABASE'
 WHERE DEPTNO = 30;

 

- SQL * PLUS

=> 오라클 커밋 전이라서 아무런 동작이 일어나지 않음 (행 현상)

=> 오라클 커밋 후에 30번 부서의 지역 'SEOUL'로 변경되고 SQL * PLUS에 쓴 실행문으로 인해 부서이름 'DATABASE'로 변경됨

 

 

 

 

- LOCK 종류

 

  - 행 레벨 록(row level lock)

: 조작중인 해당 행만 LOCK이 발생

+)WHERE절 없는 UPDATE, DELETE는 모든 행 데이터에 영향을 주는 명령어이기 때문에 전체 행이 LOCK 상태가 됨

 

  - 테이블 레벨 록(table level lock)

: 데이터 조작 명령어(DML)를 사용해 데이터가 변경 중인 테이블에 LOCK 발생

데이터정의어 (DDL)을 통해 테이블 구조 변경 불가

 

  • DML / DDL
DML INSERT, UPDATE, DELETE
DDL CREATE, ALTER, DROP