6/11 SQL
데이터 무결성과 트랜잭션: 데이터베이스 운영 기본 규칙
01 데이터 무결성과 제약 조건
데이터 베이스에서 무결성과 제약 조건은 반드시 지켜져야하는 약속 입니다.
1. 데이터 무결성
데이터베이스에 저장된 데이터 값과 사용자가 의도한 데이터 값은 일치해야합니다.
기본 규칙이 지켜지지 않으면 데이터 베이스 시스템을 운영하는데 큰 혼란 이 올수있습니다.
데이터 베이스 시스템은 데이터에 접근하거나 데이터를 처리할때마다 부적절한 데이터가 입력되는지 검사하여 데이터의 결점 없음, 즉 무결성을 유지합니다. 이를 지키기 위한 기본 규칙을 데이터 무결성(data integrity)이라고 합니다.
데이터 무결성의 종류
| 유형 | 설명 |
|---|---|
| 개체 무결성 (entity integrity) | 기본 키(primary key)로 선택된 열은 고유해야 하며 null 값을 가질 수 없다. |
| 참조 무결성 (reference integrity) | 기본 키와 외래 키의 관계이다. 외래 키가 있는 테이블의 경우에는 기본 키와 외래 키 간의 관계가 항상 유지됨을 보장한다. 참조하는 외래 키가 존재하면 행은 삭제될 수 없고 기본 키도 변경될 수 없다. |
| 영역 무결성 (domain integrity) | 데이터 형태, 범위, 기본값, 유일성에 관한 제한이다. 주어진 속성 값은 그 속성이 정의된 도메인에 속한 값이어야 한다. 예) 값이 0 이상, YN 값 준수 여부, 기본값은 1 등 |
| 비즈니스 무결성 (business integrity) | 사용자의 업무 규칙에 따른 비즈니스적인 제약 조건이다. 예) 제약 조건, DEFAULT, TRIGGER 등의 사용자 정의 |
2. 제약 조건
제약 조건(constraint)이란 정해 놓은 규칙에 맞는 데이터만 입력 받고 규칙에 어긋나는 데이터는 거부하여 데이터 무결성을 지키는 방법입니다.
-
기본키 제약 조건: UNIQUE + NOT NULL을 만족해야 한다. 또한 테이블을 대표하여 각 행을 유일하게 식별하는 값이야 한다.
-
외래 키 제약 조건 : 열 값이 부모 테이블의 참조 열의 값을 반드시 참조해야 한다. 참조되는 열은 UNIQUE하거나 기본 키이다.
참조되는 테이블을 부모 테이블과 부모키, 참조하는 테이블을 부모 테이블 과 자식키(또는 외래키)라고 부릅니다.
-
유일 키: 중복된 값을 허용하지 않는다. 유일한 값으로 존재해야한다(null 값 허용 가능).
-
NOT NULL : null 값을 허용하지 않는다. 값을 반드시 입력해야한다.
-
CHECK : 범위나 조건 등 지정된 값만 허용한다.
02 트랜잭션: 데이터 처리의 기본 작업 단위
트랜잭션(transaction)이란 데이터베이스의 DML, 즉 삽입, 갱신, 삭제와 관련되 논리적인 작업을 말합니다. 트랜잭션은 DML 실행과 동시성 제어를 위한 중요한 개념입니다.
1. 트랜잭션의 특징
트랜잭션은 데이터베이스의 데이터 무결성이 보장되는 상태에서 DML 작업을 완수하기 위한 기본 작업 단위입니다.
관계형 데이터베이스 시스템은 데이터를 처리할 때 트랜잭션을 통해 정상 종료나 사용자 프로세스 실패나 시스템 실패와 같은 비정상 종료에 대해 데이터의 신뢰성과 일관성을 보장합니다.
일반적으로 DML 실행과 실행에 대한 커밋/롤백 단계까지를 트랜잭션이라고 부르지만, 실무에서는 데이터베이스에서 SELECT 문으로 데이터를 조회하고 DML을 실행하여 종료하는 과정까지를 트랜잭션이라고 부릅니다.
트랜잭션의 특징
| 개념 | 내용 |
|---|---|
| 원자성(Atomicity) | 트랜잭션의 처리가 완전히 끝나지 않았을 경우에는 전혀 이루어지지 않은 것과 같아야 한다(all or nothing). |
| 일관성(Consistency) | 트랜잭션의 실행이 성공적으로 완료되면 데이터베이스는 모순 없이 일관성이 보존된 상태여야 한다. |
| 고립성(Isolation) | 어떤 트랜잭션도 다른 트랜잭션의 부분적 실행 결과를 볼 수 없다 |
| 지속성(Durability) | 트랜잭션이 성공하면 트랜잭션의 결과를 영구적으로 보장해야 한다. |
ACID(트랜잭션의 특징 개념의 앞자리만 따서 부르는이름)
원자성
예시상황:
이체 금액이 10만원이면 10만원이 완전히 이체되거나 전혀 이체되지 않는다는 의미입니다. 5만원까지만 이체되거나 9만원만 부분적으로 이체되는 경우는 없습니다(all or nothing).
일관성
예시상황:
트랜잭션이 완료되면 데이터베이스의 데이터는 일관되게 유지되어야 합니다. 예를 들어 A 계좌에서 10만원이 출금되었으면 B 계좌에는 10만원이 입금되어야 합니다.
고립성
예시상황:
트랜잭션이 완료되지 않은 동안에는 다른 트랜잭션이 참조하거나 변경할 수 없습니다. 예를 들어 ‘가’ 사용자의 A 계좌에서 이체가 실행되는 것에 대해서 ‘나’ 사용자는 관여할 수 없습니다. ‘가’ 사용자의 이체 실행이 완료되지 않으면, ‘나’ 사용자는 최대의 경우 계좌를 조회할 수만 있을 뿐 다른 계좌로 이체 처리를 할 수는 없습니다.
보존성
예시상황:
트랜잭션이 정상적으로 완료되면 해당 데이터는 저장되어 보존되어야 합니다. 보존을 보장함으로써 데이터베이스 시스템의 신뢰성과 일관성을 유지할 수 있으며 장애가 발생했을 때 복구가 가능해집니다.
2. 트랜잭션의 상태변화와 트랜잭션 제어어
트랜잭션의 수행단계
| 상태 | 설명 |
|---|---|
| ➊ 실행(active) | 트랜잭션을 실행 중 |
| ➋ 부분 완료(partially committed) | DML 등 트랜잭션의 명령을 실행한 후의 상태 |
| ➌ 완료(committed = 커밋) | 트랜잭션이 성공적으로 완료 |
| ➍ 실패(failed) | 더 이상 정상적으로 실행될 수 없음을 발견 |
| ➎ 철회(aborted = 롤백) | 트랜잭션이 복원되어 트랜잭션 수행 이전 상태로 돌아감 |
커밋과 롤백
트랜잭션을 확정하는 SQL 개념인 커밋과 롤백에 대해 다시 한 번 살펴보겠습니다. 커밋과 롤백은 트랜잭션 제어어(TCL, Transaction Control Language)라고도 하는데, 커밋은 우리가 워드프로세서에서 문서 작업을 마무리한 다음 파일로 저장하기 위해 ‘저장’ 버튼을 누르는 것과 유사하고, 롤백은 문장이 틀렸을 때 되돌리기를 해서 방금 전 상태로 되돌아가는 것과 유사합니다. 차이점은 데이터베이스에서 커밋은 데이터베이스에 영구적으로 반영되기 때문에 커밋 이전으로 되돌리기가 쉽지 않다는 점입니다.
커밋과 롤백 표
| 개념 | 설명 |
|---|---|
| 커밋(commit) | 트랜잭션의 모든 미결정 데이터를 영구적으로 반영함으로써 트랜잭션을 종료한다(저장과 유사). |
| 롤백(rollback) | 트랜잭션의 모든 미결정 데이터 변경을 포기함으로써 트랜잭션을 종료한다(되돌리기와 유사). |
트랜잭션 제어어는 상황에 따라 수동 혹은 자동으로 실행됩니다. 일반적으로 DML 명령어를 실행할 때는 수동으로 커밋해야 하고 DDL 명령어를 실행할 때는 자동으로 커밋됩니다.
3. 동시성 제어
동시성 제어는 동시에 실행되는 여러 개의 트랜잭션이 작업을 성공적으로 마칠 수 있도록 지원합니다.
은행이나 증권사 등 다중 사용자(multi-user) 환경으로 구성된 기업의 데이터베이스 시스템에서는 반드시 필요한 작업입니다.
지금까지 알아본 트랜잭션은 동시성 제어와 매우 밀접하게 관련되어 있습니다. 다중 사용자 환경에서 발생하는 예를 들어 보겠습니다.
다중 사용자 환경은 여러 명의 사용자가 동시에 데이터베이스에 접근하여 데이터를 조회하거나 조작하는 환경을 말합니다.
여러 명의 사용자가 존재하기 때문에 데이터베이스 안에 있는 데이터에 대한 접근 제어를 정확하게 하지 않으면 부정확한 데이터가 되고 맙니다.
동시성 제어 실패로 인한 대표적인 오류 현상에는 갱신 손실, 불일치 현상, 연쇄 복귀 등이 있습니다.
동시성 제어가 없다면 여러 사용자의 작업으로 인해 위와 같은 사례와 오류 데이터가 발생합니다. 다중 사용자의 작업에 의해 오류 데이터가 발생하지 않게 하는 것이 동시성 제어입니다. 동시성 제어를 위한 대표적인 기법은 다음과 같습니다.
동시성 제어 기법
| 제어 기법 | 설명 |
|---|---|
| 락킹(locking) | 트랜잭션이 데이터에 잠금(lock)을 설정하면 다른 트랜잭션은 해당 데이터에 대해 잠금이 해제(unlock)될 때까지 접근/수정/삭제가 불가하다. |
| 타임스탬프(timestamp) | 시스템에서 생성하는 고유 번호인 타임스탬프를 트랜잭션에 부여함으로써 트랜잭션 간의 접근 순서를 미리 정한다. |
| 적합성(validation) 검증 | 먼저 트랜잭션을 수행하고 트랜잭션을 종료할 때 적합성을 검증하여 데이터베이스에 최종 반영한다. |
데이터베이스 시스템은 다음과 같이 트랜잭션 직렬화(serialization)를 보장하는 동시성 제어를 통해 트랜잭션을 정상적으로 수행합니다.
데이터 처리 작업을 할 때는 트랜잭션과 동시성 제어의 개념을 인지하고 DML 명령어를 실행하여 신중하게 작업하기 바랍니다.
사실 동시성 제어는 데이터베이스 시스템이 자동으로 수행하므로 사용자가 동시성 제어에 직접 관여할 일은 없습니다. 동시성 제어는 다중 사용자와 트랜잭션을 제어하기 위한 관계형 데이터베이스의 기본 원리라고 알아 두면 됩니다.
