트랜잭션

(1) 트랜잭션

 

1. 트랜잭션의 개념

- 인가받지 않은 사용자로부터 데이터를 보장하기 위해 DBMS가 가져야 하는 특성이자, 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위이다. 

 

2. 트랜잭션의 특성

특성	설명	주요기법
원자성 (Atomicity)	트랜잭션을 구성하는 연산 전체가 모두 정상적으로 실행되거나 모두 취소되어야 하는 성질 트랜잭션의 연산 전체가 성공 또는 실패되어야 하는 성질	Commit/Rollback 회복성 보장
일관성 (Consistency)	시스템이 가지고 있는 고정요소는 트래잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 하는 성질	무결성 제약조건 동시성 제어
격리성=고립성 (Isolation)	동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 한다는 성질	Read Uncommitted Read Committed Repeatable Read Serializable
영속성 (Durability)	성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장되어야 하는 성질	회복기법

 

3. 트랜잭션의 상태 변화

• 활동 상태(Active) : 초기 상태, 트랜잭션이 실행 중일 때 가지는 상태
• 부분 완료 상태(Partially Committed) : 마지막 명령문이 실행된 후에 가지는 상태
• 완료 상태(Committed) : 트랜잭션이 성공적으로 완료된 후 가지는 상태
• 실패 상태(Failed) : 정상적인 실행이 더 이상 진행될 수 없을 때 가지는 상태
• 철회 상태(Aborted) : 트랜잭션이 취소되고 데이터베이스가 트랜잭션 시작 전 상태로 환원된 상태

 

4. 트랜잭션 제어

트랜잭션 제어언어는 TCL(Transaction Control Language)이라고 하며, 트랜잭션의 결과를 허용하거나 취소하는 목적으로 사용되는 언어를 지칭한다.

 

• 커밋(COMMIT) : 트랜잭션 확정(트랜잭션을 메모리에 영구적으로 저장하는 명령어)
• 롤백(ROLLBACK) : 트랜잭션 취소(트랜잭션 내역을 저장 무효화시키는 명령어)
• 체크포인트(CHECKPOINT) : 저장 시기 설정(ROLLBACK을 위한 시점을 지정하는 명령어)

 

5. 병행 제어(일관성 주요 기법)

- 병행 제어는 다수 사용자 환경에서 여러 트랜잭션을 수행할 때, 데이터베이스 일관성 유지를 위해 상호 작용을 제어하는 기법이다.

- 데이터베이스의 공유를 최대화한다.

- 시스템의 활용도를 최대화한다.

- 데이터베이스의 일관성을 유지한다.

- 사용자에 대한 응답시간을 최소화한다.

 

[1] : 병행 제어 미보장 시 문제점

• 갱신 손실(Lost Update) : 먼저 실행된 트랜잭션의 결과를 나중에 실행된 트랜잭션이 덮어쓸 때 발생하는 오류
• 현황 파악오류(Dirty Read) : 트랜잭션의 중간 수행 결과를 다른 트랜잭션이 참조하여 발생하는 오류
• 모순성(Inconsistency) : 두 트랜잭션이 동시에 실행되어 데이터베이스의 일관성이 결여되는 오류
• 연쇄복귀(Cascading Rollback) : 복수의 트랜잭션이 데이터 공유 시 특정 트랜잭션이 처리를 취소할 경우 트랜잭션이 처리한 곳의 부분을 취소하지 못하는 오류

 

[2] : 병행 제어 기법의 종류

 

로킹 : 같은 자원을 액세스하는 다중 트랜잭션 환경에서 DB의 일관성과 무결성을 유지하기 위해 트랜잭션의 순차적 진행을 보장하는 직렬화 기법, 로킹의 특징은 다음과 같음

• 데이터베이스, 파일, 레코드 등은 로킹 단위가 될 수 있음
• 로킹 단위가 작아지면 데이터베이스 공유도가 증가
• 로킹 단위가 작아지면 로킹 오버헤드가 증가
• 한꺼번에 로킹할 수 있는 객체의 크기를 로킹 단위라고 함

낙관적 검증 : 트랜잭션이 어떠한 검증도 수행하지 않고 일단 트랜잭션을 수행하고, 트랜잭션 종료 시 검증을 수행하여 데이터베이스에 반영하는 기법

타임 스탬프 순서 : 트랜잭션과 트랜잭션이 읽거나 갱신한 데이터에 대해 트랜잭션이 실행을 시작하기 전에 타임 스탬프를 부여하여 부여된 시간에 따라 트랜잭션 작업을 수행하는 기법

다중버전 동시성 제어(MVCC) : 트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교하여 직렬가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는 기법

 

6. 데이터베이스 고립화 수준

- 고립화 수준은 다른 트랜잭션이 현재의 데이터에 대한 무결성을 해치지 않기 위해 잠금을 설정하는 정도이다. 

• Read Uncommitted : 한 트랜잭션에서 연산(갱신) 중인(아직 커밋되지 않은) 데이터를 다른 트랜잭션이 읽는 것을 허용하는 수준, 연산(갱신) 중인 데이터에 대한 연산은 불허
• Read Committed : 한 트랜잭션에서 연산(갱신)을 수행할 때, 연산이 완료될 때까지 연산 대상 데이터에 대한 읽기를 제한하는 수준, 연산이 완료되어 커밋된 데이터는 다른 트랜잭션이 읽는 것을 허용
• Repeatable read : 선행 트랜잭션이 특정 데이터를 읽을 때, 트랜잭션 종료 시까지 해당 데이터에 대한 갱신, 삭제를 제한하는 수준
• Serializable read : 선행 트랜잭션이 특정 데이터 영역을 순차적으로 읽을 때, 해당 데이터 영역 전체에 대한 접근을 제한하는 수준

 

7. 회복 기법

- 회복 기법은 트랜잭션을 수행하는 도중 장애로 인해 손상된 데이터베이스를 손상되기 이전의 정상적인 상태로 복구시키는 작업이다.

 

[1] : 회복 기법 종류

- 로그 기반 회복 기법인 지연 갱신 회복 기법, 즉각 갱신 회복 기법, 체크 포인트 회복 기법, 그림자 페이징 회복 기법이 있다. 

 

• 자연 갱신 회복 기법(Deffered Update) : 트랜잭션이 완료되기 전까지 데이터베이스에 기록하지 않는 기법
• 즉각 갱신 회복 기법(Immediate Update) : 트랜잭션 수행 중 갱신 결과를 바로 DB에 반영하는 기법
• 체크 포인트 회복 기법(Checkpoint Recovery) : 장애 발생 시 검사점 이후에 처리된 트랜잭션에 대해서만 장애 발생 이전의 상태로 복원시키는 회복 기법
• 그림자 페이징 회복 기법(Shadow Paging Recovery) : 데이터베이스 트랜잭션 수행 시 복제본을 생성하여 데이터베이스 장애 시 이를 이용해 복구하는 기법

 

(2) DDL

- 데이터 정의어는 데이터를 정의하는 언어로서 '데이터를 담는 그릇을 정의하는 언어'이다.

- 테이블과 같은 데이터 구조를 정의하는 데 사용되는 명령어들로 특정 구조를 생성, 변경, 삭제, 이름을 바꾸는 데이터 구조와 관련된 명령어들을 데이터 정의어라고 부른다. 

 

[1] : DDL의 대상

1. 도메인 : 하나의 속성이 가질 수 있는 원자값들의 집합, 속성의 데이터 타입과 크기, 제약조건 등의 정보
2. 스키마 : 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조, 스키마는 외부/개념/내부 3계층으로 구성되어 있음
   1. 외부 스키마 : 사용자나 개발자의 관점에서 필요로 하는 데이터베이스의 논리적 구조, 사용자 뷰를 나타냄, 서브 스키마라 불림
   2. 개념 스키마 : 데이터베이스의 전체적인 논리적 구조, 전체적인 뷰를 나타냄, 개체 간의 관계, 제약조건, 접근 권한, 무결성, 보안에 대해 정의
   3. 내부 스키마 : 물리적 저장장치의 관점에서 보는 데이터베이스 구조, 실제로 데이터베이스에 저장될 레코드의 형식을 정의하고 저장 데이터 항목의 표현 방법, 내부 레코드의 물리적 순서 등을 표현
3. 테이블 : 데이터 저장 공간
4. 뷰 : 하나 이상의 물리 테이블에서 유도되는 가상의 테이블
5. 인덱스 : 검색을 빠르게 하기 위한 데이터 구조

 

1. 테이블

- 테이블은 데이터를 저장하는 항목인 필드들로 구성된 데이터의 집합체이다.

- 하나의 DB 내에 여러 개의 테이블로 구성될 수 있고, 릴레이션 혹은 엔티티라고도 불린다.

 

▼ 테이블 관련 용어

• 튜플/행 : 테이블 내의 행을 의미하며 레코드라고도 함, 튜플은 릴레이션에서 같은 값을 가질 수 없음
• 애트리뷰트/열 : 테이블 내의 열을 의미, 열의 개수를 디그리라고 함
• 식별자 : 여러 개의 집합체를 담고 있는 관계형 데이터베이스에서 각각의 구분할 수 있는 논리적인 개념
• 카디널리티 : 튜플의 개수
• 차수 : 애트리뷰트의 개수
• 도메인 : 하나의 애트리뷰트가 취할 수 있는 같은 타입의 원자값들의 집합

 

2. 뷰

- 뷰는 논리 테이블로서 사용자에게 테이블과 동일하다.

- 아래 그림에서 '테이블 A'와 '테이블 B'는 물리 테이블을 의미하고, '뷰 C'는 두 개의 테이블을 이용하여 생성한 뷰를 의미한다.

- 뷰는 '테이블 A'와 같은 하나의 물리 테이블로부터 생성 가능하며, 다수의 테이블 또는 다른 뷰를 이용해 만들 수 있다.

- 뷰와 같은 결과를 만들기 위해 조인 기능을 활용할 수 있으나, 뷰가 만들어져 있다면 사용자는 조인 없이 하나의 테이블을 대상으로 하는 단순한 질의어를 사용할 수 있다.

 

▼ 뷰의 특징

• 논리적 데이터 독립성 제공 : 데이터베이스에 영향을 주지 않고 애플리케이션이 원하는 형태로 데이터에 접근 가능
• 데이터 조작 연산 간소화 : 애플리케이션이 원하는 형태의 논리적 구조를 형성하여 데이터 조작 연산을 간소화
• 보안 기능 제공 : 특정 필드만을 선택해 뷰를 생성할 경우 애플리케이션은 선택되지 않은 필드의 조회 및 접근 불가
• 뷰 변경 불가 : 뷰 정의는 ALTER 문을 이용하여 변경할 수 없음

 

뷰의 목적

- 뷰를 사용하는 주된 이유는 단순 질의어를 사용할 수 있기 때문이다.

- FROM 절에 있는 하나의 뷰를 통해 뷰를 구성하는 복수의 테이블을 대체하는 단순성에 그 의의가 있다.

- 테이블의 중요 데이터 일부만을 제공할 수 있는 장단점이 있다. 

 

▼ 뷰의 장점과 단점

<장점>

- 논리적 독립성 제공 : 뷰는 논리 테이블

- 사용자 데이터 관리 용이 : 복수 테이블에 존재하는 여러 종류의 데이터에 대해 단순한 질의어 사용이 가능

- 데이터 보안의 용이 : 중요 보안 데이터를 저장 중인 테이블에는 접근 불허, 해당 테이블의 일부 정보만을 볼 수 잇는 뷰에는 접근을 허용, 보안 데이터에 대한 접근 제어 가능

 

 

<단점>

- 뷰 자체 인덱스 불가 : 인덱스는 물리적으로 저장된 데이터를 대상으로 하기에 논리적 구성인 뷰 자체는 인덱스를 가지지 못함

- 뷰 정의 변경 불가 : 뷰의 정의를 변경하려면 뷰를 삭제하고 재생성

- 데이터 변경 제약 존재 : 뷰의 내용에 대한 삽입, 삭제, 변경 제약이 있음

 

 

3. 인덱스

- 인덱스는 검색 연산의 최적화를 위해 데이터베이스 내 값에 대한 주소 정보로 구성된 데이터 구조이다.

- 인덱스는 데이터를 빠르게 찾을 수 있는 수단으로서, 테이블에 대한 조회 속도를 높여 주는 자료 구조이다.

- 인덱스는 테이블의 특정 레코드 위치를 알려 주는 용도로 사용한다.

 

▼ 인덱스의 특징

- 기본 키 컬럼은 자동으로 인덱스가 생성된다. 

- 연월일이나 이름을 기준으로 하는 인덱스는 자동으로 생성되지 않는다.

- 테이블의 컬럼에 인덱스가 없는 경우, 테이블의 전체 내용을 검색한다.

- 인덱스가 생성되어 있을 때 데이터를 빠르게 찾을 수 있다.

- 조건절에 '='로 비교되는 컬럼을 대상으로 인덱스를 생성하면 검색 속도를 높일 수 있다.

 

 

▼ 인덱스의 종류

• 순서 인덱스 : 데이터가 정렬된 순서로 생성되는 인덱스
• 해시 인덱스 : 해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스
• 비트맵 인덱스 : 각 컬럼에 적은 개수 값이 저장된 경우 선택하는 인덱스
• 함수기반 인덱스 : 수식이나 함수를 적용하여 만든 인덱스
• 단일 인덱스 : 하나의 컬럼으로만 구성한 인덱스
• 결합 인덱스 : 두 개 이상의 컬럼으로 구성한 인덱스
• 클러스터드 인덱스 : 기본 키 기준으로 레코드를 묶어서 저장하는 인덱스

 

▼  인덱스의 스캔 방식

• 인덱스 범위 스캔 - 인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후에 리프 블록을 필요한 범위만 스캔하는 방식
• 인덱스 전체 스캔 - 수직점 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식
• 인덱스 단일 스캔 - 수직적 탐색만으로 데이터를 찾는 스캔 방식
• 인덱스 생략 스캔 - 선두 컬럼이 조건 절에 빠졌어도 인덱스를 활용하는 스캔 방식

 

4. DDL 명령어

CREATE : 데이터베이스 오브젝트 생성하는 명령어

ALTER : 데이터베이스 오브젝트 변경하는 명령어

DROP : 데이터베이스 오브젝트 삭제하는 명령어

TRUNCATE : 데이터베이스 오브젝트 내용 삭제하는 명령어

 

[1] : CREATE 명령어

- CREATE TABLE에 들어갈 수 있는 제야곶건으로는 기본 키, 외래 키, UNIQUE, NOT NULL, CHECK, DEFAULT가 있다. 

 

▼ CREATE TABLE 제약조건

1. PRIMARY KEY : 테이블의 기본 키를 정의, 유일하게 테이블의 각 행을 식별
2. FOREIGN KEY : 외래 키를 정의, 참조 대상을 테이블로 명시, 열과 참조된 테이블의 열 사이의 외래 키 관계를 적용하고 설정
3. UNIQUE : 테이블 내에서 얻은 유일한 값을 갖도록 하는 제약조건
4. NOT NULL : 해당 컬럼은 NULL 값을 포함하지 않도록 하는 제약조건
5. CHECK : 개발자가 정의하는 제약조건, 참이어야 하는 조건을 지정
6. DEFAULT : 데이터를 INSERT 할 때, 해당 컬럼의 값을 넣지 않는 경우 기본값으로 설정해 주는 제약조건

 

▼ ALTER TABLE

- 테이블을 수정하는 명령이다.

- 테이블에 필요한 컬럼을 추가하는 문법이다.

- CREATE TABLE의 컬럼에 사용되는 제약조건인 PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT를 ALTER TABLE에서도 사용할 수 있다. 

 

 

[2] : DROP TABLE

-테이블을 삭제하는 명령이다. 

- DROP TABLE의 명령어의 옵션에는 CASCADE와 RESTRICT가 있다.

- CASCADE와 RESTRICT의 경우 외래 키가 걸려 있을 때 해당한다.

 

• CASCADE : 참조하는 테이블까지 연쇄적으로 제거하는 옵션
• RESTRICT : 다른 테이블에 삭제할 테이블을 참조 중이면 제거하지 않는 옵션

 

[3] : TRUNCATE TABLE

- 테이블 내의 데이터들을 삭제하는 명령어다. 

 

 

[4] : VIEW 관련 DDL

1. CREATE VIEW

 - 뷰를 생성하는 명령어

ex) CREATE VIEW 뷰이름 AS 조회쿼리;

 

- 뷰 테이블의 SELECT 문에는 UNION이나 ORDER BY 절을 사용할 수 없다.

- 컬럼명을 기술하지 않으면 SELECT문의 컬럼명이 자동으로 사용된다.

 

2. CREATE OR REPLACE VIEW

- 뷰를 교체하는 명령이다.

- OR REPLACE라는 키워드를 추가하는 것을 제외하고는 CREATE VIEW와 사용 방법이 동일하다.

 

ex) CREATE OR REPLACE VIEW 뷰이름 AS 조회쿼리;

 

 

3. DROP VIEW

ex) DROP VIEW 뷰이름;

 

 

[5] : INDEX 관련 DDL

 

1. CREATE INDEX

- 인덱스를 생성하는 명령이다.

- UNIQUE는 생략 가능하고, 인덱스 걸린 컬럼에 중복 값을 허용하지 않는다.

- 복수 컬럼을 인덱스로 걸 수 있다. 

 

ex) CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ......)

 

 

2. ALTER INDEX

- 인덱스를 수정하는 명령어다.

- 일부 DBMS는 ALTER INDEX를 제공하지 않는다.

- 기존 인덱스를 삭제하고 신규 인덱스를 생성하는 방식으로 사용을 권고한다. 

ex) ALTER [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, .......)

 

3. DROP INDEX
- 인덱스를 삭제하는 명령어이다.

ex) DROP INDEX 인덱스명;

 

 

(3) DML

 데이터 조작어의 개념

- 데이터 조작어는 데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어이다.

- 데이터 조작어의 유형에는 SELECT, INSERT, UPDATE, DELETE가 있다. 

 

유형	동작	설명
SELECT	조회	테이블 내 컬럼에 저장된 데이터를 조회
INSERT	삽입	테이블 내 칼럼에 데이터를 추가
UPDATE	갱신	테이블 내 컬럼에 저장된 데이터를 수정
DELETE	삭제	테이블 내 컬럼에 저장된 데이터를 삭제

 

 

1. SELECT 명령어

- 데이터의 내용을 조회할 때 사용하는 명령어이다. 

- SELECT 절, FROM 절, WHERE 절, GROUP BY 절, HAVING 절, ORDER BY 절로 구성된다.

 

• SELECT 절 : 검색하고자 하는 속성명, 계산식을 기술, 속성명 별칭은 AS를 사용하며 생략 가능함, 2개 이상의 테이블을 대상으로 검색할 때는 '테이블명, 속성명'으로 표현, 술어 부분은 ALL이 기본값
  
  • ALL : 모든 튜플을 검색할 때 사용, SELECT 뒤에 명시하지 않은 경우 ALL로 인식
  • DISTINCT : 중복된 속성이 조회될 경우 그중 한 개만 검색
• FROM 절 : 질의에 의해 검색될 데이터들을 포함하는 테이블명을 기술
• WHERE 절 : 검색할 조건을 기술
• GROUP BY 절 : 속성값을 그룹으로 분류하고자 할 때 사용
• HAVING 절 : GROUP BY에 의해 분류한 후 그룹에 대한 조건 지정
• ORDER BY 절 : 속성값을 정렬하고자 할 때 사용

 

2. 조인

- 조인은 두 개 이상의 테이블을 연결하여 데이터를 검색하는 방법이다.

- 두 릴레이션으로부터 관련된 튜플들을 결합하여 하나의 튜플로 만드는 가장 대표적인 데이터 연결 방법이다. 

 

▼ 논리적 조인 유형(상세)

• 내부 조인 : 공통 존재 컬럼의 값이 같은 경우를 추출하는 기법
• 외부 조인 
  • 왼쪽 외부 조인 : 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터를 추출하는 기법
  • 오른쪽 외부 조인 : 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터를 추출하는 기법
  • 완전 외부 조인 : 양쪽의 모든 데이터를 추출하는 기법
• 교차 조인 : 조인 조건이 없는 모든 데이터 조합을 추출하는 기법
• 셀프 조인 : 자기 자신에게 별칭을 지정한 후 다시 조인하는 기법

 

 

3. 서브쿼리

- 서브쿼리는 SQL문 안에 포함된 또 다른 SQL문이다.

- 서브쿼리의 용도는 알려지지 않은 기준을 위한 검색을 위해 사용한다.

- 메인쿼리와 서비쿼리 관계는 주종 관계로서, 서브쿼리에 사용되는 컬럼 정보는 메인쿼리의 컬럼 정보를 사용할 수 있으나 역으로는 성립하지 않는다. 

 

▼서브쿼리 유형(위치 기준)

• FROM절 서브쿼리 : 서브쿼리가 FROM 절 안에 들어있는 형태, 인라인 뷰라고 불림, 뷰처럼 결과가 동적으로 생성된 테이블 형태로 사용할 수 있음
• WHERE절 서브쿼리 : 서브쿼리가 WHERE절 안에 들어있는 형태, 중첩 서브쿼리라고도 불림

 

4. 집합 연산자

- 집합 연산자는 테이블을 집합 개념으로 보고, 두 테이블 연산에 집합 연산자를 사용하는 방식이다.

- 집합 연산자는 여러 질의 결과를 연결하여 하나로 결합하는 방식을 사용한다.

 

집합 연산자	설명
UNION	중복 행이 제거된 쿼리 결과를 반환하는 집합 연산자
UNION ALL	중복 행이 제거되지 않은 쿼리 결과를 반환하는 집합 연산자
INTERSECT	두 쿼리 결과에 공통적으로 존재하는 결과를 반환하는 집합 연산자
MINUS	첫 쿼리에 있고 두 번째 쿼리에는 없는 결과를 반환하는 집합 연산자

 

(4) DCL

- 데이터 제어어는 데이터베이스 관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 관리자(DBA)가 사용하는 제어용 언어이다.

- 데이터 제어어의 유형에는 GRANT, REVOKE가 있다.

 

• GRANT : 관리자가 사용자에게 데이터베이스에 대한 권한을 부여하는 명령어
• REVOKE : 관리자가 사용자에게 부여했던 권한을 회수하기 위한 명령어