Sunny's Tech Blog

트랜잭션 (Transaction) 이란?

데이터베이스의 상태를 변환시키는 하나의 논리적 기능을 수행하기 위한 작업의 단위 또는 한꺼번에 모두 수행되어야 할 일련의 연산들을 의미

트랜잭션의 특징

1. 데이터베이스 시스템에서 병행 제어 및 회복 작업 시 처리되는 작업의 논리적 단위

2. 사용자가 시스템에 대한 서비스 요구 시 시스템이 응답하기 위한 상태 변환 과정의 작업 단위

3. 하나의 트랜잭션은 Commit 또는 Rollback 수행

트랜잭션의 성질

Atomicity(원자성)

1. 트랜잭션의 연산은 데이터베이스에 모두 반영되든지 아니면 전혀 반영되지 않아야 한다.

2. 트랜잭션 내의 모든 명령은 반드시 완벽히 수행되어야 하며, 모두가 완벽히 수행되지 않고 어느하나라도 오류가 발생하면 트랜잭션 전부가 취소되어야 한다.

Consistency(일관성)

1. 트랜잭션이 그 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 변환한다.

2. 시스템이 가지고 있는 고정요소는 트랜잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 한다.

Isolation(독립성,격리성)

1. 둘 이상의 트랜잭션이 동시에 병행 실행되는 경우 어느 하나의 트랜잭션 실행중에 다른 트랜잭션의 연산이 끼어들 수 없다.

2. 수행중인 트랜잭션은 완전히 완료될 때까지 다른 트랜잭션에서 수행 결과를 참조할 수 없다.

Durablility(영속성,지속성)

1. 성공적으로 완료된 트랜잭션의 결과는 시스템이 고장나더라도 영구적으로 반영되어야 한다.

트랜잭션 연산 및 상태

1) Commit연산

- 한 개의 논리적 단위(트랜잭션)에 대한 작업이 성공적으로 끝났고 데이터베이스가 다시 일관된 상태에 있을 때, 이 트랜잭션이 수행한 연산이 완료된 것을 트랜잭션 관리자에게 알려주는 연산

2) Rollback연산

- 하나의 트랜잭션 처리가 비정상적으로 종료되어 데이터베이스의 일관성을 깨뜨렸을 때, 이 트랜잭션의 일부가 정상적으로 처리되었더라도 트랜잭션의 원자성을 구현하기 위해 이 트랜잭션이 행한 모든 연산을 취소(Undo)하는 연산

- Rollback시에는 해당 트랜잭션을 재시작하거나 폐기함

3) Savepoint 연산

- 현재 작업 중인 트랜잭션을 잘게 쪼개는 역할

- 저장점(Savepoint)을 정의하면 Rollback할 때 트랜잭션에 포함된 전체 작업을 Rollback하는 것이 아니라, 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 Rollback

트랜잭션 상태

활동(Active) : 트랜잭션이 실행중인 상태

실패(Failed) : 트랜잭션 실행에 오류가 발생하여 중단된 상태

철회(Aborted) : 트랜잭션이 비정상적으로 종료되어 Rollback 연산을 수행한 상태

부분 완료(Partially Committed) : 트랜잭션의 마지막 연산까지 실행했지만, Commit 연산이 실행되기 직전의 상태

완료(Committed) : 트랜잭션이 성공적으로 종료되어 Commit 연산을 실행한 후의 상태

* 트랜잭션(Transaction)이란?

• 데이터베이스의 상태를 변화시키기 위해 수행하는 작업 단위
• 트랜잭션은 원자성(Atomicity), 일관성(Consistency), 격리성(Isolation), 지속성(Durability)을 보장

* 트랜잭션 격리 수준이란?

• 동시에 여러 트랜잭션이 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있도록 허용할지 말지를 결정하는 것
• `격리성` 과 연관된 개념
• 동시에 실행되는 트랜잭션들은 서로에게 영향을 미치지 않도록 격리해야 함 (ex. 동시에 같은 데이터 수정 못하도록)
• 트랜잭션 간에 격리성을 완벽히 보장하려면 동시에 처리되는 트랜잭션을 차례대로 실행을 해야하는데, 그렇게 하면 처리 성능이 안 좋아진다는 문제점이 발생
• 이러한 문제로 인해 ANSI 표준은 트랜잭션의 격리 수준을 4단계로 나누어 정의

* 격리 수준(Isolation Level) 유형

• READ UNCOMMITTED(커밋되지 않은 읽기)
• READ COMMITTED(커밋된 읽기)
• REPEATABLE READ(반복 가능한 읽기)
• SERIALIZABLE(직렬화 가능)

1) READ UNCOMMITTED (Level 0)

- 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관 없이 다른 트랜잭션이 접근 가능

- 트랜잭션이 처리중이거나, 아직 Commit되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용함

- 예시) 사용자1이 A 데이터를 B 데이터로 변경하는 동안, 사용자2는 아직 완료되지 않은 트랜잭션이지만 B 데이터를 읽을 수 있음

- 트랜잭션에서 처리한 작업이 완료되지 않았음에도 불구하고 다른 트랜잭션에서 볼 수 있게 되는 현상을

더티 리드(Dirty Read)라 하고, 더티 리드가 허용되는 격리 수준이 READ UNCOMMITTED

- READ UNCOMMITTED 격리 수준은 RDBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준. 따라서 MySQL을 사용한다면 최소 READ COMMITTED 이상의 격리 수준을 사용할 것을 권장

2) READ COMMITTED (Level 1)

- 어떠한 트랜잭션에서 데이터를 변경하더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있음

- 온라인 서비스에서 가장 많이 선택되는 격리 수준. 해당 레벨에서는 더티 리드(Dirty Read)와 같은 현상은 발생하지 않음

- 예시) 사용자1이 A 데이터를 B 데이터로 변경하는 동안, 사용자2는 해당 데이터에 접근이 불가능

- NON-REPEATABLE READ라는 문제가 존재

- 한 트랜잭션에서 같은 쿼리를 두 번 수행할 때 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하면서 쿼리의 결과가 상이하게 나타나는 일관성이 깨진 현상

3) REPEATABLE READ (Level 2)

- 트랜잭션 내에서 한 번 조회한 데이터를 반복해서 조회해도 결과는 동일

- 다른 사용자는 트랜잭션 영역에 해당되는 데이터에 대해 수정이 불가능함

- MySQL의 InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준

- PHANTOM READ (PHANTOM ROW) 문제 발생 가능

- 한 트랜잭션내에서 동일한 쿼리를 두 번 수행했는데, 첫 번째 쿼리에서 존재하지 않던 유령(Phantom) 레코드가 두 번째 쿼리에서 나타나는 현상

4) SERIALIZABLE (Level 3)

- 가장 단순한 격리 수준이면서 가장 엄격한 격리 수준

- 동시 처리 성능도 다른 트랜잭션 격리 수준보다 현저히 떨어짐

- 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없음

※ 레벨을 높게 조정할 수록 발생하는 비용이 증가

※ 격리 수준이 높아질수록 MySQL 서버의 처리 성능이 많이 떨어지는 것은 아님

※ READ UNCOMMITTED는 일반적인 데이터베이스에서는 거의 사용하지 않고, SERIALIZABLE 역시 동시성이 중요한 데이터베이스에서는 거의 사용되지 않음

정규화(Normalization)란?

관계형 데이터베이스(RDBMS)의 설계에서 데이터의 중복을 줄이고, 무결성을 향상시키는 기법을 정규화라고 한다.

정규화된 결과를 정규형이라고 하며, 정규형은 기본 정규형과 고급 정규형으로 나뉜다.

• 기본 정규형 : 제1정규형, 제2정규형, 제3정규형, BCNF(보이스/코드 정규형)
• 고급 정규형 : 제4정규형, 제5정규형

정규화의 목적

1. 데이터의 중복을 없애며, 불필요한 데이터를 최소화시킴
2. 무결성을 지키고, 이상 현상을 방지
3. 테이블 구성을 논리적이고 직관적으로 할 수 있음
4. 데이터베이스 구조 확장에 용이해짐

* 이상현상이란?

https://ynsseon.tistory.com/11

정규화 유형

1) 제 1정규화 (1NF)

  - 테이블의 컬럼이 원자값(하나의 값)을 갖도록 테이블을 분리시킨 형태

2) 제 2정규화 (2NF)

  - 제 1정규화를 진행한 테이블에서 완전 함수 종속을 만족하도록 테이블을 분리시킨 형태

  - 완전 함수 종속이란, 기본키의 부분집합이 결정자가 되어선 안된단 것을 의미

* 결정자란, 다른 속성을 고유하게 결정하는 하나 이상의 속성을 의미

▶ 해당 테이블의 기본키는 (환자번호, 진료과) 복합키

▶ (환자번호, 진료과) 는 진료비를 결정

▶ 동시에 기본키인 (환자번호, 진료과)의 부분집합인 `진료과` 가 진료실을 결정하는 결정자

따라서, 테이블을 두개로 분해하여 제 2정규형을 만족시킬 수 있음

3) 제 3정규화 (3NF)

  - 제 2정규화를 진행한 테이블에 대해 이행적 종속을 없애도록 테이블을 분리시킨 형태

  - 이행정 종속이란, A → B 이고 B → C 이면 A → C를 만족한다는 것을 의미

▶ 위와 같은 테이블의 경우는 환자번호 → 진료과 → 진료실의 형태이기 때문에 (환자번호, 진료과) 와 (진료과, 진료실) 형태의 두 개의 테이블로 분리해야 함

▶ 이행적 종속을 제거하는 이유 : 환자번호 1001의 진료과가 피부과로 바뀔 경우 진료실 정보도 변경되어야 하며 데이터 불일치가 발생할 수 있음. 진료실 컬럼의 데이터를 직접 수정해 줄 수도 있지만, 이러한 번거로움을 없애기 위해 제 3정규화를 하는 것

4) BCNF (보이스/코드 정규형)

  - 제 3정규화를 완료한 테이블에서 모든 결정자가 후보키가 되도록 테이블을 분리한 형태

▶ 위의 테이블에서 기본키는 (환자번호, 진료과)이고, 이는 담당자를 결정함

▶ 동시에 담당자는 진료과를 결정함

▶ 담당자는 진료과를 결정하는 결정자이지만, 후보키가 아님 (후보키는 튜플을 유일하게 식별할 수 있는 컬럼이어야 함)

따라서 다음과 같은 테이블 분해를 통해 BCNF를 만족할 수 있음

5) 제 4정규화 (4NF)

  - BCNF를 만족하면서 다치 종속을 제거한 형태

* 하나의 테이블 내의 컬럼 A, B, C가 존재한다고 했을 때 B와 C는 서로 독립적이고, A가 B와 C를 각각 다중 결정하는 것을 다치 종속이라고 함

▶ 개발자마다 자격증 값들이 존재하고, 개발자마다 여러 언어 값들이 존재할 경우 이를 다치 종속 관계라고 함

따라서, 다음과 같은 테이블 분리를 통해 제 4정규형을 만족할 수 있음

6) 제 5정규화 (5NF)

  - 제4정규형을 만족하면서 조인 종속을 제거한 형태

  - 필요한 데이터가 사라지지 않는 무손실 분해가 되고, 필요 없는 데이터가 생기지 않는 비부가적 분해가 된 릴레이션(테이블)은 제 5정규형을 만족함

• 무손실 조인(↔ 무손실 분해) : 하나의 릴레이션을 여러개의 릴레이션으로 분해한 후, 공통 속성으로 다시 조인했을때 데이터 손실 없이 원래의 릴레이션 그대로 복원할 수 있는 경우
• 비부가적 조인(↔ 비부가적 분해) : 조인한 결과에 원본 릴레이션에 없던 데이터가 존재하지 않는 경우

※ 제 5정규형을 만족하게 하면 엔터티가 늘어나 비용이 증가하기 때문에 실제로 적용하는 경우는 드물다

※ 일반적으로 제 3정규형이나 BCNF에 속하도록 릴레이션(테이블)을 분해하여 데이터 중복을 줄이고 이상 현상이 발생하는 문제를 해결한다고 한다

이상 현상(Anomaly)이란?

데이터베이스 정규화를 수행하지 않아, 테이블 간의 중복 데이터가 발생하며 데이터 무결성이 저하되는 현상

* 데이터 무결성이란, 데이터가 항상 정확한 값을 유지하려는 성질 (데이터의 정확성, 일관성, 유효성이 유지되는 것)

이상현상 유형

1) 삽입 이상 (Insertion Anomaly)

  - 특정 데이터가 존재하지 않아, 테이블에 데이터를 삽입할 수 없는 경우

▷ 가정의학과라는 새로운 데이터를 추가하고 싶은데, 관련 환자정보가 없을 경우 테이블에 데이터를 삽입할 수가 없음

▷ 이러한 현상을 삽입 이상이라고 함

2) 삭제 이상 (Deletion Anomaly)

  - 특정 데이터를 지우고 싶을 때, 원하지 않는 데이터까지 함께 삭제해야 하는 경우

▷ 고길동이라는 환자정보를 삭제하고 싶은데 진료과/진료과코드/진료담당자 등을 다른 테이블에 저장하지 않았을 경우, 해당 정보들도 함께 삭제됨

▷ 이러한 현상을 삭제 이상이라고 함

3) 갱신 이상 (Modification Anomaly)

  - 테이블 내의 특정 데이터를 갱신할 때, 정상적으로 변경되지 않아 데이터의 불일치가 발생하는 경우

▷ 피부과의 진료담당자 이교수의 이름을 변경하고 싶을 경우, 10000개가 훨씬 넘는 데이터들 중 해당 값을 찾아서 변경해야 함

▷ 데이터 갱신이 제대로 이루어지지 않아 데이터간의 불일치가 발생할 수 있음

▷ 이러한 현상을 갱신 이상이라고 함

조인(Join)이란?

- 한 데이터베이스 내의 여러 테이블의 레코드를 조합하여 하나의 열로 표현한 것

- 두 개 이상의 테이블이나 데이터베이스를 연결하여 데이터를 검색하는 방법

* 두 개 이상의 테이블을 조인하기 위해서는 적어도 하나 이상의 외래키가 필요함

조인의 필요성

→ 서로 관계 있는 데이터가 여러 테이블로 나뉘어 저장되므로, 각 테이블에 저장된 데이터를 효과적으로 검색하기 위해 조인이 필요

조인의 종류

1) 내부조인 (INNER JOIN)

- 여러 애플리케이션에서 사용되는 가장 흔한 결합 방식이며, 기본 조인 형식으로 간주

- 명시적 조인 표현(explicit)과 암시적 조인 표현(implicit) 존재

  → 명시적

JOIN 키워드를 사용하며, ON 키워드를 조인에 대한 구문을 지정하는데 사용

SELECT * FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID;

→ 암시적

SELECT 구문의 FROM 절에서 콤마를 사용해 단순히 조인을 위한 여러 테이블을 나열

SELECT * FROM employee, department WHERE employee.DepartmentID = department.DepartmentID;

2) 동등조인 (EQUI JOIN)

- 비교자 기반의 조인이며, 조인 구문에서 동등비교만을 사용

- 조인조건에 '=' 연산자가 사용된 조인

3) 자연조인 (NATURAL JOIN)

- INNER JOIN에 속함

- 반드시 두 테이블 간의 동일한 이름, 타입을 가진 컬럼이 필요

- 조인에 이용되는 컬럼은 명시하지 않아도 자동으로 조인에 사용 → 두 테이블에서 동일한 컬럼명을 갖는 컬럼은 모두 조인이 됨

- 두 테이블이 동시에 가지고 있는 컬럼의 값이 전부 같은 것만 골라냄

4) 교차조인 (CROSS JOIN)

- 조인되는 두 테이블에서 곱집합을 반환

- Catesian product, Cross product 라고도 함

- 명시적, 암시적 조인 표현 존재

  → 명시적

SELECT * FROM employee CROSS JOIN department;

  → 암시적

SELECT * FROM employee, department;

5) 외부조인 (OUTER JOIN)

* LEFT OUTER JOIN

- 왼쪽 테이블을 기준으로 조인

- 왼쪽 테이블의 데이터 모두 출력 → 오른쪽 테이블의 컬럼값에 NULL 발생 가능

* RIGHT OUTER JOIN

- 오른쪽 테이블을 기준으로 조인

- 오른쪽 테이블의 데이터 모두 출력 → 왼쪽 테이블의 컬럼값에 NULL 발생 가능

* FULL OUTER JOIN

- 조인을 하는 테이블들의 모든 데이터를 출력

- 합집합

※ MySQL에서는 FULL OUTER JOIN을 지원하지 않으므로, LEFT OUTER JOIN 결과와 RIGHT OUTER JOIN 결과를 UNION 하여 사용

6) 셀프조인 (SELF JOIN)

- 자기자신을 조인하는 것

- 자신이 갖고 있는 칼럼을 다양하게 변형시켜 활용할 때 자주 사용

키(Key)란?

데이터베이스에서 키 (Key) 는 조건에 만족하는 튜플을 찾거나, 순서대로 정렬할 때 다른 튜플들과 구별할 수 있는 기준이 되는 속성

* 튜플이란, 테이블의 행을 의미

유일성과 최소성

유일성 : 하나의 키값으로 하나의 튜플만을 유일하게 식별할 수 있어야 함

→ 문자 그대로 유일해야 한다는 특징

최소성 : 키를 구성하는 속성 하나를 제거하면 유일하게 식별할 수 없도록 꼭 필요한 최소의 속성으로 구성되어야 함

→ 키를 구성하는 속성의 갯수가 최소여야 한다는 특징

키의 종류

* 기본키

- 특정 튜플을 유일하게 구별할 수 있는 속성

- 후보키 중에서도 특별히 선정된 주키(Main Key)

- 유일성 O, 최소성 O

- 중복값 가질 수 없음 (기본키 주요 특징)

- NULL값 가질 수 없음 (기본키 주요 특징)

* 대체키

- 기본키를 제외한 나머지 후보키

- 보조키라고도 함

* 후보키

- 테이블을 구성하는 속성들 중에서 튜플을 유일하게 식별하기 위해 사용되는 속성들의 부분집합

- 유일성 O, 최소성 O

* 슈퍼키

- 유일성의 특성을 만족하는 속성 또는 속성들의 집합

- 최소성은 만족 X

- EX : 환자ID는 고유하기 때문에 슈퍼키가 될 수 있음. 나이, 직업 등은 중복된 값이 존재하기 때문에 슈퍼키가 될 수 없음.

→ (환자ID, 나이, 직업)은 환자ID가 각 튜플을 구별할 수 있기에 슈퍼키가 될 수 있음. 이때 유일성은 만족하지만, 최소성은 만족하지 못함.

* 외래키

- 다른 릴레이션(테이블)의 기본키를 그대로 참조하는 속성의 집합

※ 외래키가 필요한 이유?

for 데이터 무결성

→ 데이터 무결성이란, 데이터가 항상 정확한 값을 유지하려는 성질을 의미한다.

예를 들어 환자 테이블의 환자ID가 변경이 되었는데, 진단기록 테이블의 환자ID가 변경이 되지 않는다면 두 값은 서로 같은 값이어야 하는데 다른 값이 되어버린다. 이를 무결성이 깨졌다고 표현한다.