데이터베이스 SQL - TCL, DCL

트랜잭션 : 데이터베이스의 논리적 연산 단위

- 의미적으로 분할할 수 없는 최소 단위

- 일반적으로 하나의 트랜잭션은 여러 SQL 문장을 포함한다.

- 성공시 모든 연산을 반영, 취소시 모든 연산을 취소한다.

 

트랜잭션 예시)

도서 주문 (재고 수량 감소, 주문 내역 생성, 결제 등)

계좌 이체 (원 계좌의 잔액 감소, 다른 계좌의 잔액 증가 등)

교통카드 충전 (잔액 증가, 결제 등)

 

트랜잭션의 특성 (ACID : Atomicity, Consistency, Isolation, Durability) // 원일고지

원자성 : 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다.

일관성 : 트랜잭션 실행 전의 데이터베이스 내용이 잘못되어 있지 않다면

트랜잭션 실행 이후에도 데이터베이스의 내용에 잘못이 있으면 안된다.

고립성 : 트랜잭션이 실행되는 도중에 다른 트랜잭션이 영향을 받아서는 안된다.

지속성 : 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

 

트랜잭션의 ACID 특성을 보장하기 위해 DBMS는 동시성 제어를 수행한다. (Lock 기반, Timestamp 기반)

 

 

TCL (Transaction Control Language)

> COMMIT 실행 전 상태

UPDATE PLAYER SET HEIGHT = HEIGHT + 10;

- 변경된 내용이 메모리에 임시로 저장된다.

- 현재 사용자는 증가한 HEIGHT 값을 읽을 수 있다.

- 다른 사용자는 증가 전 HEIGHT 값만 읽을 수 있다.

- HEIGHT에는 잠금이 설정되어 다른 사용자는 값을 변경할 수 없다.

 

> COMMIT 실행 후

- 변경된 내용은 DB에 저장된다.

- 변경 내용을 모든 다른 사용자가 볼 수 있다.

- 이전 데이터는 모두 사라진다. (별도로 로그 보관시 복구 가능하다.)

- 관련된 행에 대한 잠금이 해제되어 모든 사용자가 변경할 수 있다.

 

트랜잭션을 제어하기 위한 명령어

- COMMIT : 변경된 내용을 DB에 영구적으로 반영한다.

- ROLLBACK

: 기본적으론 변경된 내용을 버리고 변경 전 상태(마지막 COMMIT)로 복귀한다.

  SAVEPOINT(부분 복귀 위해 지정한 저장점)를 지정한 경우 지정한 저장점까지만 복귀한다.

 

트랜잭션은 SQL문 실행 시 자동 시작되며, COMMIT / ROLLBACK 실행 시 종료된다.

 

자동 커밋 / 자동 롤백

- DDL 문장 수행 시 DDL 수행 전에 자동으로 커밋

- DB를 정상적으로 접속 종료하면 자동 커밋

- 애플리케이션의 이상 종료로 DB와의 접속이 단절되었을 때 자동 롤백

 

 

> ROLLBACK 이후 데이터 상태

COMMIT;

UPDATE PLAYER SET HEIGHT = HEIGHT + 10;
DELETE FROM PLAYER;

ROLLBACK;

변경된 내용이 모두 취소된다.

이전 데이터가 재저장된다.

관련된 행에 대한 잠금이 해제되어 모든 사용자가 변경할 수 있다.

 

> SAVEPOINT

SAVEPOINT PT1;

SAVEPOINT PT1;

SAVEPOINT PT3;

ROLLBACK TO PT1; -- 2번으로 ROLLBACK (2번 이후는 무효가 됨)

 

 

ㅡㅡㅡㅡ

 

DCL (Data Control Language)

 

사용자의 계정 생성/삭제, 권한 부여/회수에 대한 명령어

CREATE USER daram IDENTIFIED BY pw1; -- 유저 생성
ALTER USER daram IDENTIFIED BY new_pw; -- 유저 비밀번호 변경

GRANT CREATE USER TO daram; -- daram에게 유저 만들 권한 부여

REVOKE CREATE USER FROM daram; -- daram에게 유저 만들 권한 회수

DROP USER daram -- 사용자(daram)가 객체를 갖고 있지 않은 경우에만 삭제 발생
DROP USER daram CASCADE; -- 사용자가 생성한 객체도 함께 삭제됨

 

세션 생성 권한 , 오브젝트 권한

-- 세션 생성 권한
GRANT CREATE SESSION TO daram;

-- daram 계정에서 자기 계정의 PLAYER 테이블 조회
GRANT SELECT ON PLAYER TO daram;

-- daram 계정에서 pinkbean 계정의 PLAYER 테이블 조회
GRANT SELECT ON pinkbean.PLAYER TO daram;

 

오라클의 ROLE

일반적으로 사용자 생성 시 CONNECT와 RESOURCE를 통해 기본 권한을 부여한다.

CREATE USER daram IDENTIFIED BY mypw;
GRANT CONNECT, DBA, RESOURCE TO daram;