SQL 트랜잭션, 세션, 사용자 관리 설명 및 예제

※ 트랜잭션 : 더 이상 쪼개질 수 없는 최소 수행단위
- SQL문장들의 집합
- 논리적인 작업의 단위
- 처리 순서는 중요하지 않으나 두 개의 UPDATE문이
  모두 정상적으로 실행되어야 함

※ 트랜잭션 특징(ACID)
- 원자성 : 전부 수행 OR 전부 수행X
- 일관성 :  트랜잭션 수행 전, 후 항상 일관된 데이터베이스 상태 유지
- 격리성 : 트랜잭션이 완료될 때까지 중간에 결과 접근X
- 지속성 : 영구 보존

※ COMMIT 연산

- 트랜잭션 수행이 성공적으로 완료되었음을 선언하는 연산

- COMMIT 연산 실행 시 트랜잭션 수행 결과가 데이터베이스에 반영 → 일관된 상태 지속적으로 유지

 

※ ROLLBACK 연산

- 트랜잭션 수행이 실패했음을 선언하는 연산

- 그동안 실행한 연산 결과가 취소되고 트랜잭션 수행 전의 일관된 상태로 돌아감

 

※ TCL(Transaction Control Language)

- 트랜잭션은 DB계정을 통해 접속하는 동시에 시작 

- TCL을 실행할 때 종료 후 새로운 트랜잭션 시작

- DCL, DDL 사용 할 때도 새 트랜잭션이 시작되는 효과

 

※ 세션(SESSION)

- 어떤 활동을 위한 시간이나 기간

- 데이터베이스 접속을 시작으로 여러 관련 작업을 수행한 후 접속을 종료하기까지 전체 기간

- 세션 내부에는 하나 이상의 트랜잭션 존재

- 세션과 관련된 트랜잭션 확인 → 작업표시줄 검색창에 'SQLPLUS'

→ 아이디, 비번 치기(SQL과 동일)

 

※ LOCK

- 특정 세션에서 조작 중인 데이터는 트랜지션이 완료되기 전까지 다른 세션에서 조작될 수 없는 상태

- 조작하는 대상 데이터가 특정 행 데이터일 경우, 해당 행만 LOCK 발생

- WHERE이 없는 UPDATE, DELETE → 전체 행이 LOCK

- 전체 행이 LOCK이어도 INSERT문 수행가능

- 하나의 데이터를 여러 곳에서 동시에 조작하려 할 때 발생할 혼란 최소화

 

※ 사용자 생성

- CREATE USER 사용자 이름

IDENTIFIED BY 패스워드

- 사용 전 시스템 권한 주는 것이 필수 

→ GRANT (객체권한)

    ON (스키마.객체 이름)

    TO (사용자이름/롤이름)

(WITH GRANT OPTION)

 

※ 사용자 권한 

- GRANT : 권한을 줌
- REVOKE : 권한을 뺏음

[예제 1] - 트랜잭션

CREATE TABLE DEPT_TCL
AS SELECT * FROM DEPT;
 
INSERT INTO DEPT_TCL VALUES(50, 'DATABASE', 'SEOUL');
UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 40;
DELETE FROM DEPT_TCL WHERE DNAME = 'RESEARCH';
 
-- ROLLBACK : 명령 취소
ROLLBACK;
 
SELECT * FROM DEPT_TCL;
 
INSERT INTO DEPT_TCL VALUES(50, 'NEWYOTK', 'SEOUL');
UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 20;
DELETE FROM DEPT_TCL WHERE DEPTNO = 40;
INSERT INTO DEPT_TCL VALUES(50, 'NEWYOTK', 'SEOUL');
 
--COMMIT : 변경 값 결과 반영
COMMIT;
 
DELETE FROM DEPT_TCL
WHERE DEPTNO = 50;
SELECT * FROM DEPT_TCL;
 
COMMIT;

 

 

[예제 2] - LOCK

--LOCK : 특정 세션에서 조작 중인 데이터는 트랜지션이 완료되기 전까지 --다른 세션에서 조작될 수 없음
UPDATE DEPT_TCL SET LOC = 'SEOUL'
WHERE DEPTNO = 30;
SELECT * FROM DEPT_TCL;
 
COMMIT; --커밋을 해야 세션 SQLPLUS에서 UPDATE가 됨

 

 

[예제 3] - 사용자 권한

--사용자 생성
CREATE USER C##GLOBAL
    IDENTIFIED BY ORACLE; 
 
GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE
TO C##GLOBAL;
 
--사용자 생성 후 테이블 ㅏ
CREATE TABLE TEMP (
    COL1 VARCHAR(20),
    COR2 VARCHAR(20)
);
 
-- GLOBAL에 권한 부여
GRANT SELECT , INSERT
ON TEMP
TO C##GLOBAL;
 
--GLOBAL에 권한 회수
REVOKE SELECT, INSERT
ON TEMP
FROM C##GLOBAL;
 
SELECT * FROM EMP;