db설계

윤휘영·2024년 11월 23일

1. 정규화

1.1 이상현상

삭제이상: 투플 삭제 시 저장된 다른 정보까지 연쇄적으로 삭제됨 -> 연쇄삭제(triggered deletion) 문제 발생
삽입이상: 투플 삽입 시 특정 속성에 해당하는 값이 없어 NULL을 입력해야 함-> NULL값 문제 발생
수정이상: 투플 수정 시 중복된 데이터의 일부만 수정됨-> 일관성 없음(inconsistency) 문제 발생

1.2 함수 종속성

속성 A의 값을 알면 속성 B의 값이 유일하게 정해지는 의존 관계를 'B는 A에 종속한다'라고 한다. 이 관계는 A $\to$ B로 나타내며, A는 B의 결정자라고 하며, B는 종속 속성이라 한다.
'학생번호 $\to$ 주소'와 같이 왼쪽 속성의 각 값에 대하여 오른쪽 속성의 값이 유일하게 결정될 때 '함수적으로 종속한다(functionally dependent)라고 한다.
릴레이션의 속성 간에 함수적으로 종속하는 성질을 함수 종속성이라 한다.
함수 종속성 규칙
X, Y, Z가 릴레이션 R에 포함된 속성의 집합이라고 할 때, 함수 종속성에 관한 다음과 같은 규칙이 성립한다.
- 부분집합 규칙: $if\space\space Y \sube X, \space then\space X\to Y$ , 사소한(trival) 함수적 종속이라고도 한다.
- 증가 규칙: $if\space\space X\to Y, \space then \space XZ\to YZ$
- 이행 규칙: $if\space\space X\to Y\space and\space\space Y\to Z, \space then \space X\to Z$
- 결합 규칙: $if\space\space X\to Y \space and \space X\to Z, \space then \space X\to YZ$
- 분해 규칙: $if \space X\to YZ, \space then \space X\to Y \space and \space X\to Z$
- 유사이행 규칙: $if \space X\to Y \space and \space WY\to Z, \space then \space WX\to Z$
릴레이션 R(K, A1, A2, A3...An)에서 K가 기본키이면 K $\to$ R이 성립한다. 즉 기본키는 릴레이션의 모든 속성에 대한 결정자이다.
이상현상은 기본키가 아니면서 결정자인 속성이 있을 때 발생한다.

1.3 정규화

이상현상이 발생하는 릴레이션을 분해하여 이상현상을 없애는 과정을 정규화라고 한다.
A와 B가 릴레이션 R의 속성이고 A $\to$ B가 성립할 때, B가 A의 속성 전체에 함수 종속하고 부분집합 속성에 함수 종속하지 않을 경우 완전 함수 종속이라고 한다. 반면, A $\to$ B 종속성에서 A의 속성 일부를 제거했는데도 종속성이 여전히 성립하는 경우 부분 함수 종속이라고 한다. 예를 들어, (A1, A2) $\to$ B 종속성에서 A2를 제거했는데도 A1 $\to$ B가 성립한다면 부분 함수 종속이다.
정규형
- 릴레이션의 모든 속성값이 원자값을 가지면 제 1 정규형이라고 한다.
- 릴레이션 R이 제 1 정규형이고, 기본키가 아닌 속성이 기본키에 완전 함수 종속일 때 제 2 정규형이라고 한다. 즉, 복합식별자에서 일반 속성이 주식별자의 일부에만 종속성을 가지지 않는다.
- 릴레이션 R이 제 2 정규형이고, 기본키가 아닌 속성이 기본키에 비이행적으로 종속할 때 제 3 정규형이라고 한다. 즉, 주식별자가 아닌 일반 속성 간에 함수 종속이 없다.
- 릴레이션 R에서 함수 종속성 X $\to$ Y가 성립할 때 모든 결정자 X가 후보키이면 BCNF 정규형이라고 한다.
릴레이션을 분해할 때는 각 릴레이션에 공통 속성을 하나 이상 두어야 한다.
- 무손실 분해: 릴레이션 R을 R1과 R2로 분해할 때, R1 $\Join$ R2 = R이면 무손실 분해이다. 무손실 분해를 위한 조건은 R1 $\cap$ R2 $\to$ R1이나 R1 $\cap$ R2 $\to$ R2를 만족해야 한다. 즉 R1과 R2를 조인하면 R이 되어야 한다. 공통된 속성이 R1의 키이거나 R2의 키면 된다.
릴레이션 분해 과정에서 원래 릴레이션 R의 함수 종속성 집합 F는 분해된 각 릴레이션에 맞게 재분배된다.

1.4 연습문제

1. 2

2. 2

3. 2

4. 3

5. 4

6. 1

7. 4

8. 1

9. 4

10.

클로저 계산
1. 클로저를 속성 집합 $X$ 로 초기화. $X^+ = X$
2. 주어진 함수 종속성 중에서 $X$ 의 현재 클로저에 있는 속성을 결정자로 가진 함수 종속성을 확인

$Y\to Z$ 에서 $Y \sube X^+$ 일 경우, $Z$ 를 $X^+$ 에 추가. 즉 결정자가 클로저에 포함되어 있다면, 종속자를 클로저에 추가.

새로운 속성이 추가되지 않을 때까지 2번 반복

최종적으로 계산된 $X^+$ 는 $X$ 로부터 도달 가능한 모든 속성.

결정자를 기반으로, 결정자가 작은 것부터, 종속자가 많은 것부터.

1) A, B, D
A의 클로저 계산:
시작: {A}
A → BC 적용: {A, B, C}
A → D 적용: {A, B, C, D}
D → A는 이미 포함.
결과: A의 클로저 = {A, B, C, D}

B의 클로저 계산:
시작: {B}
B → A 적용: {B, A}
A → BC 적용: {B, A, C}
A → D 적용: {B, A, C, D}
결과: B의 클로저 = {A, B, C, D}

D의 클로저 계산:
시작: {D}
D → A 적용: {D, A}
A → BC 적용: {D, A, B, C}
결과: D의 클로저 = {A, B, C, D}

2) AC
A의 클로저 계산:
시작: {A}
A → B 적용: {A, B}
B → C 적용: {A, B, C}
결과: A의 클로저 = {A, B, C}

AC의 클로저 계산:
시작: {A, C}
A → B 적용: {A, B, C}
AC → D 적용: {A, B, C, D}
결과: AC의 클로저 = {A, B, C, D}

3) ABD
AB의 클로저 계산:
시작: {A, B}
AB → C 적용: {A, B, C}
CD → E는 적용 불가.
결과: AB의 클로저 = {A, B, C}

ABD의 클로저 계산:
시작: {A, B, D}
AB → C 적용: {A, B, C, D}
CD → E 적용: {A, B, C, D, E}
결과: ABD의 클로저 = {A, B, C, D, E}

4) AC
AC의 클로저 계산:
시작: {A, C}
AC → E 적용: {A, C, E}
C → D 적용: {A, C, D, E}
D → A는 이미 포함.
결과: AC의 클로저 = {A, B, C, D, E}

5) BC
BC의 클로저 계산:
시작: {B, C}
BC → ADE 적용: {A, B, C, D, E}
D → B는 이미 포함.
결과: BC의 클로저 = {A, B, C, D, E}

6) DBC
DBC의 클로저 계산:
시작: {D, B, C}
DC → AE 적용: {A, B, C, D, E}
E → F 적용: {A, B, C, D, E, F}
결과: DBC의 클로저 = {A, B, C, D, E, F}

7) ABD
ABD의 클로저 계산:
시작: {A, B, D}
AD → E 적용: {A, B, D, E}
BE → F 적용: {A, B, D, E, F}
B → C 적용: {A, B, C, D, E, F}
AF → G 적용: {A, B, C, D, E, F, G}
결과: ABD의 클로저 = {A, B, C, D, E, F, G}

11. B->D, C->A, C->B, C->D, D->B

X가 Y를 함수적으로 결정한다면, X의 값이 같다면 Y의 값도 항상 같다. X의 값에 따른 Y의 값은 유일하다.

12.

A	B	C
b	c	h
e	i	f
g	i	f
e	b	a

1) 3, 5, 6
1. 동일한 A값(e)에 대해 C값이 다르므로 성립X
2. 동일한 A값(e)에 대해 B값이 다르므로 성립X
3. 동일한 B값에 대해 C값이 항상 같으므로 성립
4. 동일한 (B, C)값에 대해 A값이 다르므로 성립X
5. 동일한 (A, B)값이 없으므로 함수 종속성이 진공성으로 성립
6. 동일한 (A, C)값이 없으므로 함수 종속성이 진공성으로 성립

2) 2

CD→E (주어진 함수 종속성)
E→A (주어진 함수 종속성): 추이성 규칙에 따라 CD→A를 얻음.
A→C (주어진 함수 종속성): 다시 추이성 규칙에 따라 CD→C를 얻음.
따라서 CD→A와 CD→C를 합쳐서 CD→AC가 성립.
B → D (주어진 종속성)으로 B에서 D를 결정할 수 있음.
하지만, C를 결정하기 위해 B 또는 D로부터 C를 결정할 수 있는 경로가 없음.
C를 결정하려면 A가 필요하지만, BD만으로는 C를 결정할 수 없음.
B→D (주어진 함수 종속성): 확장 규칙에 따라 BC→DC를 얻음.
DC는 CD와 동일하므로, BC→CD가 성립.
A→B (주어진 함수 종속성):확장 규칙에 따라 AC→BC를 얻음.

13.

1) (1, 1, 2), (1, 2, 1)

조인 결과

A	B	C
1	1	1
1	1	2
1	2	1
1	2	2
2	3	3

공통 속성 A가 분해된 릴레이션 중 하나에서 키가 되어야 하지만, 그렇지 못하다.

2) 손실 분해
분해된 릴레이션의 공통 속성인 AC가 AC→R1 또는 AC→R2를 만족하는지 확인한다.

R1의 후보키 확인: AB
R2의 후보키 확인: CD
AC는 R1, R2에서의 키가 아니므로, 손실 분해이다.

3) 손실 분해
분해된 릴레이션의 공통 속성인 AD가 AD->R1 또는 AD->R2를 만족하는지 확인한다.

R1의 후보키 확인:
- A->C로부터 C를 유도
- AB->D로부터 D를 유도
- 따라서 AB의 클로저는 {A, B, C, D}이므로 AB는 후보키
R2의 후보키 확인:
- CD->E(하지만 C가 R2에 없으므로 적용 불가)
- R2에는 유의미한 함수 종속성이 없으므로, 후보키는 ADE 전체
AD는 R1, R2의 키가 아니므로, 손실 분해이다.

14.

후보키 식별: (물품번호, 행사번호)로 모든 속성을 결정할 수 있으므로, 후보 키이다.
현재 정규형 판단: 물품번호→제조사, 스타일은 부분 함수 종속성이므로, 2정규형 위반.
정규화 과정
- 분해
  - R1(물품번호, 제조사, 스타일)
  - R2(물품번호, 행사번호, 가격)
- 각 릴레이션의 후보키
  - R1: 물품번호
  - R2: (물품번호, 행사번호)
- 각 릴레이션의 정규형 판단
  - R1: 모든 비-키 속성이 후보키인 물품번호에 완전히 종속되므로 2NF 만족. 함수 종속성이 후보키로부터 비-키 속성으로의 종속성이므로 3NF 만족. 후보키인 물품번호가 모든 함수 종속성의 결정자이므로 BCNF 만족.
  - R2: 모든 비-키 속성이 후보키인(물품번호, 행사번호)에 완전히 종속되므로 2NF만족. 함수 종속성이 후보키로부터 비-키 속성으로의 종속성이므로 3NF만족. 후보키인 (물품번호, 행사번호)가 모든 함수 종속성의 결정자이므로 BCNF 만족.

후보키 식별:
- C $^+$ = {C}
- C → A ⇒ {C, A}
- C → D ⇒ {C, A, D}
- D → B ⇒ {C, A, D, B}
- CD → E (C와 D 포함) ⇒ {C, A, D, B, E}
- C $^+$ = {A, B, C, D, E}이므로 C는 후보키
현재 정규형 판단:
- 2NF: 후보키가 단일 속성 C이므로 부분 함수 종속성 없음.
- 3NF: C→D, D→B에서, B는 비-키 속성이므로 이는 이행적 함수 종속성. 따라서 3NF 위반.
정규화 과정
- 분해
  - R1(A, C, D, E)
  - R2(B, D)
- 각 릴레이션의 후보키
  - R1: C
  - R2: D
- 각 릴레이션의 정규형 판단
  - R1: 후보키가 단일 속성 C이므로 부분 함수 종속성 없음. 모든 함수 종속성이 후보키로부터 비-키 속성으로의 종속성이므로 3NF 만족. 후보키인 C가 모든 함수 종속성의 결정자이므로 BCNF 만족.
  - R2: 후보키가 단일 속성 D으므로 2NF 만족. 모든 함수 종속성이 후보키로부터 비-키 속성으로의 종속성이므로 3NF 만족. 후보키인 D가 함수 종속성의 결정자이므로 BCNF 만족.

후보키 식별
- (A, D, E) $^+$ = {A, D, E}
- A → B ⇒ {A, B, D, E}
- B → C ⇒ {A, B, C, D, E}
- DE → F ⇒ {A, B, C, D, E, F}
- (A, D, E)는 후보키
현재 정규형 판단:
- 2NF: A→B에서 결정자 A는 후보키의 일부이며, 종속자 B는 비-키 속성이므로 부분 함수 종속성 성립, 2NF 위반
정규화 과정
- 2NF로 정규화: R1(A, B), R2(A, D, E, C, F)
  - B→C가 R1에서 적용될 수 없음
- 3NF로 정규화: R1(A, B), R2(A, D, E, F), R3(B, C)
  - R2에서 ADE가 후보키이지만, 종속성에 DE→F가 있으므로 BCNF 위반
- BCNF로 정규화: R2_1(D, E, F), R2_2(A, D, E)
최종 릴레이션
- R1(A, B)
- R2(B, C)
- R2_1(D, E, F)
- R2_2(A, D, E)

함수 종속성 추론

후보키 확인
- 대출번호와 상환일이 합쳐져야 각 상환 내역을 유일하게 식별 가능
- 따라서 후보키는 (대출번호, 상환일)
함수 종속성 도출
- 대출번호→이름, 주소, 대출액, 대출일
- (대출번호, 상환일)→상환액

현재 정규형 판단

부분 함수 종속성 존재, 2정규형 위반

정규화 과정

분해
- R1(대출번호, 이름, 주소, 대출액, 대출일)
- R2(대출번호, 상환일, 상환액)
각 릴레이션의 정규형 판단
- R1
  - 함수 종속성의 결정자가 후보키이므로 2NF 및 3NF, BCNF 만족
- R2
  - 함수 종속성의 결정자가 후보키이므로 2NF 및 3NF, BCNF 만족

15.

1) 제 2정규형

(H, I)→J, J→M이므로 이행적 함수 종속 존재.

2) 제 1정규형

(D, O)로 모든 속성을 결정할 수 있으므로 기본키.
D→N에서 부분 함수 종속이 존재.

3) 제 1정규형

기본키가 (A, D)인데 A→BC 에서 부분 함수 종속이 존재.

4) 제 1정규형

후보키 탐색-BD의 폐포 찾기
- BD→A
- BD→A, B, D
- B→C
- C→E
- BD $^+$ = {A, B, C, D, E}
- BD는 모든 속성을 결정하므로 기본키.
D→A, B→C에서 부분 함수 종속 존재.

16.

1) {shipname, date}, {voyageID, date}

(1) 후보 키 후보 식별:

후보 키 정의: 모든 속성을 유일하게 식별할 수 있는 최소 속성 집합.

후보 키 후보군:{shipname, date}, {voyageID, date}

(2) 각 후보 키 후보의 폐포 계산:

{shipname, date}의 폐포:
- 시작: {shipname, date}
- 적용:
  - shipname → shiptype ⇒ {shipname, date, shiptype}
  - shipname, date → voyageID, port ⇒ {shipname, date, shiptype, voyageID, port}
  - voyageID → shipname, cargo ⇒ {shipname, date, shiptype, voyageID, port, cargo}
- 모든 속성을 포함하므로 {shipname, date}는 후보 키.
{voyageID, date}의 폐포:
- 시작: {voyageID, date}
- 적용:
  - voyageID → shipname, cargo ⇒ {voyageID, date, shipname, cargo}
  - shipname → shiptype ⇒ {voyageID, date, shipname, cargo, shiptype}
  - shipname, date → voyageID, port ⇒ {voyageID, date, shipname, cargo, shiptype, port}
- 모든 속성을 포함하므로 {voyageID, date}도 후보 키.

(1) 프라임 속성과 비프라임 속성:

프라임 속성: shipname, voyageID, date
비프라임 속성: shiptype, cargo, port

(2) 부분 함수 종속성 확인:

shipname → shiptype: 부분 함수 종속성.
voyageID → shipname, cargo: 부분 함수 종속성.
shipname, date → voyageID, port: 완전 함수 종속성.

(3) 분해:

릴레이션 1: Ship(shipname, shiptype)
- 함수 종속성: shipname → shiptype
- 키: shipname
릴레이션 2: Voyage(voyageID, shipname, cargo)
- 함수 종속성: voyageID → shipname, cargo
- 키: voyageID
릴레이션 3: Shipping(shipname, date, voyageID, port)
- 함수 종속성: shipname, date → voyageID, port
- 키: shipname, date

3)
(1) 릴레이션별 이행적 함수 종속성:

릴레이션 1: Ship(shipname, shiptype)
- 함수 종속성: shipname → shiptype
- 이행적 종속성 없음 → 3NF 만족.
릴레이션 2: Voyage(voyageID, shipname, cargo)
- 함수 종속성: voyageID → shipname, cargo
- 이행적 종속성 없음 → 3NF 만족.
릴레이션 3: Shipping(shipname, date, voyageID, port)
- 함수 종속성:
  - shipname, date → voyageID, port
  - 추가적으로 voyageID → shipname (원래의 FDs에서 가져옴)
- voyageID → shipname은 프라임 속성으로의 이행적 종속 → 3NF 만족.
  
  프라임 속성으로의 이행적 종속
  결정자(왼쪽 값)가 프라임 속성일 때, 종속 대상(오른쪽 값)이 프라임 속성이라면 이 종속성은 3NF 위반이 아님

4)
(1) 릴레이션별 BCNF 만족 여부:

릴레이션 1: Ship(shipname, shiptype)
- 함수 종속성: shipname → shiptype
- shipname은 후보 키 → BCNF 만족.
릴레이션 2: Voyage(voyageID, shipname, cargo)
- 함수 종속성: voyageID → shipname, cargo
- voyageID는 후보 키 → BCNF 만족.
릴레이션 3: Shipping(shipname, date, voyageID, port)
- 함수 종속성:
  - shipname, date → voyageID, port (후보 키)
  - voyageID → shipname (BCNF 위반: voyageID는 후보 키가 아님)

(2) 위반된 함수 종속성에 따른 분해:

릴레이션 3-1: VoyageShip(voyageID, shipname)
- 함수 종속성: voyageID → shipname
- 키: voyageID
- BCNF 만족.
릴레이션 3-2: ShippingDetails(voyageID, date, port)
- 함수 종속성: voyageID, date → port
- 키: voyageID, date
- BCNF 만족.

(3) 최종 릴레이션

Ship(shipname, shiptype)
- 함수 종속성: shipname → shiptype
Voyage(voyageID, shipname, cargo)
- 함수 종속성: voyageID → shipname, cargo
VoyageShip(voyageID, shipname)
- 함수 종속성: voyageID → shipname
ShippingDetails(voyageID, date, port)
- 함수 종속성: voyageID, date → port

17.

1) 제 1정규형

(1) 폐포 계산

{booktitle, authorname}의 폐포:
- 시작: {booktitle, authorname}
- 적용:
  - booktitle, authorname → publisher ⇒ {booktitle, authorname, publisher}
  - booktitle → booktype ⇒ {booktitle, authorname, publisher, booktype}
  - booktype → listprice ⇒ {booktitle, authorname, publisher, booktype, listprice}
  - authorname → authorgroup ⇒ {booktitle, authorname, publisher, booktype, listprice, authorgroup}
- 모든 속성을 포함하므로 {booktitle, authorname}는 후보 키.

(2) 릴레이션의 정규형 판단

제1정규형: 릴레이션은 기본적으로 1NF를 만족한다고 가정.
제2정규형:
- 프라임 속성: booktitle, authorname (후보 키에 포함)
- 비프라임 속성: booktype, listprice, authorgroup, publisher
부분 함수 종속성 확인

booktitle → booktype:
- booktitle는 후보 키의 일부이므로 부분 함수 종속성 존재.
authorname → authorgroup:
- authorname은 후보 키의 일부이므로 부분 함수 종속성 존재.
booktype → listprice:
- booktype은 비프라임 속성이며, 이 경우는 이행적 함수 종속성에 해당.

(1) 정규화 수행

제2정규형(2NF)으로 정규화

릴레이션 1: BookPublisher(booktitle, authorname, publisher)
- 함수 종속성: booktitle, authorname → publisher
- 키: {booktitle, authorname}
릴레이션 2: BookType(booktitle, booktype)
- 함수 종속성: booktitle → booktype
- 키: booktitle
릴레이션 3: AuthorGroup(authorname, authorgroup)
- 함수 종속성: authorname → authorgroup
- 키: authorname

제3정규형(3NF)으로 정규화
- 이행적 함수 종속성 확인: 릴레이션 2에서 booktype → listprice 존재. listprice는 비프라임 속성이고 booktype은 비프라임 속성이므로, 이행적 종속성 존재.
1. 릴레이션 2 수정: BookType(booktitle, booktype)
- 키: booktitle
- 함수 종속성: booktitle → booktype
1. 릴레이션 4: BookTypePrice(booktype, listprice)
- 함수 종속성: booktype → listprice
- 키: booktype

(2) 최종 릴레이션

BookPublisher(booktitle, authorname, publisher)
- 함수 종속성: booktitle, authorname → publisher
- 키: {booktitle, authorname}
BookType(booktitle, booktype)
- 함수 종속성: booktitle → booktype
- 키: booktitle
AuthorGroup(authorname, authorgroup)
- 함수 종속성: authorname → authorgroup
- 키: authorname
BookTypePrice(booktype, listprice)
- 함수 종속성: booktype → listprice
- 키: booktype

(3) 각 릴레이션의 정규형 확인

BookPublisher

키: {booktitle, authorname}
함수 종속성: booktitle, authorname → publisher
비프라임 속성 publisher는 후보 키 전체에 완전 종속.
3NF 및 BCNF 만족.

BookType

키: booktitle
함수 종속성: booktitle → booktype
비프라임 속성 booktype은 후보 키에 완전 종속.
3NF 및 BCNF 만족.

AuthorGroup

키: authorname
함수 종속성: authorname → authorgroup
비프라임 속성 authorgroup은 후보 키에 완전 종속.
3NF 및 BCNF 만족.

BookTypePrice

키: booktype
함수 종속성: booktype → listprice
비프라임 속성 listprice는 후보 키에 완전 종속.
3NF 및 BCNF 만족.

2. 트랜잭션, 동시성 제어, 회복

2.1 트랜잭션

트랜잭션은 DBMS에서 데이터를 다루는 논리적인 작업의 단위다.
트랜잭션은 전체가 수행되거나 전혀 수행되지 않아야 한다(all or nothing).
ACID
- Atomicity: 트랜잭션에 포함된 작업은 전부 수행되거나 전부 수행되지 않아야 한다.
- Consistency: 트랜잭션을 수행하기 전이나 수행한 후나 db는 항상 일관된 상태를 유지해야 한다.
- Isolation: 수행 중인 트랜잭션에 다른 트랜잭션이 끼어들어 변경 중인 데이터 값을 훼손하는 일이 없어야 한다.
- Durability: 수행을 성공적으로 완료한 트랜잭션은 변경한 데이터를 영구히 저장해야 한다.

2.2 동시성 제어

오손 읽기: T1이 READ UNCOMMITTED이고 T2가 READ COMMITTED일 때, T1이 읽기 작업을 한 후, T2가 쓰기 작업을 하고 COMMIT은 하지 않음. 그 후 T1이 읽기 작업을 하고 T2가 ROLLBACK을 하면 결국 T2가 쓰기 작업을 한 데이터는 COMMIT되지 않았음에도 T1은 두 번의 읽기에서 다른 결과를 냄. READ COMMITTED 이상의 격리 수준에서는 방지됨.
반복불가능 읽기: T1, T2 모두 READ COMMITTED일 때, T1이 읽기 작업을 하고, T2가 갱신을 한 후 커밋함. T1이 다시 읽기를 하면 T1은 같은 트랜잭션의 읽기 작업임에도 다른 결과를 냄. REPEATABLE READ 격리 수준 이상에 방지됨.
유령데이터 읽기: T1이 REPEATABLE READ이고 T1이 READ COMMITTED일 때, T1이 읽기 작업을 하고, T2가 삽입을 한 후 커밋함. T1이 다시 읽기를 하면 T1은 같은 트랜잭션의 읽기 작업임에도 다른 결과를 냄. SERIALIZABLE 격리 수준에서만 방지됨.
MySQL에서는 REPEATABLE READ으로도 유령데이터 읽기를 방지 가능. 트랜젝션이 처음 데이터를 읽어 올 때 SNAPSHOT을 구축하여 자료를 가져오고, 그에 따라 다른 세션의 자료가 변경되더라도 동일한 결과를 보여주게 됨.

2.3 회복

즉시갱신 방법: 부분완료되지 않은 트랜잭션의 경우, 부분 완료 전이라도 트랜잭션이 변경한 내용 중 일부가 데이터베이스에 기록될 수 있으므로 UNDO 작업이 필요.
지연갱신 방법: 부분완료되지 않은 트랜잭션의 경우, 데이터베이스에 실제 반영된 내용이 없으므로 UNDO 작업이 불필요.

2.4 연습문제

1. 4

2. 4

3. 4

4. 4

5. 1

6. 1

7. 3

8. 3

9. 4

10. 3

11. 3

12. 4

13.

(1) X = 990, Y = 1010
(2) X = 1100, Y = 1100
(3) T1과 T2가 동시에 X를 읽고 갱신하는 과정에서 T2의 변경 사항이 T1에 의해 덮어씌워짐. 갱신 손실 발생.
(4)

T1	T2
LS(X)
A1 = read_item(X);
LX(X)
A1 = A1 + 100;
write_item(A1→X);
UNLOCK(X)
	LS(X)
	A2 = read_item(X);
	temp = A2 * 0.1;
	LX(X)
	A2 = A2 - temp;
	write_item(A2→X);
	UNLOCK(X)
	LS(Y)
	B2 = read_item(Y);
LS(Y)
B1 = read_item(Y);
LX(Y)
B1 = B1 - 100;
write_item(B1→Y);
UNLOCK(Y)
	B2 = B2 + temp;
	LX(Y)
	write_item(B2→Y);
	UNLOCK(Y)

T1의 X에 대한 작업: LS로 공유락을 걸어 X를 읽음. LX로 베타락을 걸어 값을 수정하고 기록한 후 락 해제.
T2의 X에 대한 작업: T1의 X에 대한 락이 해제된 후 LS로 공유락을 걸어 값을 읽음. 값을 계산한 후 LX로 베타락을 걸어 수정하고 기록, 이후 락 해제.
T1과 T2의 Y에 대한 작업: T1이 먼저 LS(Y)로 공유락을 걸어 값을 읽고, LX(Y)로 베타락을 걸어 수정. T1이 Y에 대한 락을 해제한 후, T2가 LS(Y)로 공유락을 걸어 값을 읽고, 이후 계산 후 LX(Y)로 수정 및 기록, 학 해제
최종 결과: X = 990, Y = 1010

14.

(1) T1이 데이터를 읽은 후, T2가 데이터를 갱신하고 COMMIT으로 확정한 후, T1이 다시 데이터를 읽었으므로 같은 트랜잭션 내에서 다른 읽기 결과가 나온다. 반복 불가능 읽기이다.
(2) REPEATABLE READ 격리 수준을 지정한다.
(3)

T1	T2
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
START TRANSACTION
SELECT age
FROM Users
WHERE id = 1;
	UPDATE Users
	SET age = 21
	WHERE id = 1;
	COMMIT;
SELECT age
FROM Users
WHERE id = 1;

15.

(1) 10100

T1은 READ COMMITTED 격리 수준에서 실행된다 이는 트랜잭션이 커밋된 데이터만 읽을 수 있음을 의미한다.
T2가 UPDATE를 수행하여 bookid = 1의 price를 100 증가시키고 T2의 COMMIT(3번)이 완료된 후 T1의 2번이 실행된다.
따라서 T1의 2번은 T2의 변경 사항을 반영하여 합계를 계산한다.

(2) 고립 수준을 REPEATABLE READ로 변경

트랜잭션 시작 후 처음 읽은 데이터가 트랜잭션 종료까지 변경되지 않도록 보장

(3) 10000

T2의 UPDATE는 트랜잭션이 종료될 때까지 적용되지 않는다.
T1의 2번 SELECT는 T2의 변경 사항을 보지 못한다. T2는 아직 커밋되지 않았으므로 READ COMMITTED 수준에서 읽지 못하기 때문이다.

(4) T2의 COMMIT이 T1의 2번 SELECT 전에 이루어져야 한다.

T2의 UPDATE와 COMMIT을 T1의 트랜잭션 이전에 완료한다.
T1이 T2의 변경 사항을 반영한 데이터를 읽게 된다.

-- T2 먼저 실행
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
UPDATE Book
SET price = price + 100
WHERE bookid = 1;
COMMIT;

-- 이후 T1 실행
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT SUM(price)
FROM Book; -- 10100
SELECT SUM(price)
FROM Book; -- 10100
COMMIT;

16. 발생

+-----+       +-----+       +-----+       +-----+       +-----+
|  T1 | ----> |  a  | ----> |  T4 | ----> |  d  | ----> |  T2 |
+-----+       +-----+       +-----+       +-----+       +-----+
   |                          ^                                   |
   v                          |                                   v
+-----+       +-----+       +-----+       +-----+       +-----+
|  b  | ----> |  T5 | ----> |  c  | ----> |  T2 | ----> |  e  |
+-----+       +-----+       +-----+       +-----+       +-----+
                             ^                                   |
                             |                                   v
                             +-----+       +-----+       +-----+
                             |  g  | ----> |  T3 | ----> |  f  |
                             +-----+       +-----+       +-----+

17.

18.

복구 작업: 체크포인트 이후의 로그에서 각 트랜잭션의 상태를 확인한다.
- T1: COMMIT 완료
- T2: COMMIT 완료
- T3: COMMIT 없음, 그 변경 내용이 디스크에 반영되지 않음.
복구 단계: REDO(T1), REDO(T2)

19.

T3의 변경 내용이 디스크에 반영될 수 있음.
UNDO(T3), REDO(T1), REDO(T2)

20.

장애 시점과 트랜잭션 상태
- T1: COMMIT 완료. 변경 사항 반영.
- T4: COMMIT 완료. 변경 사항 반영.
- T2: 진행 중. 변경 사항 반영 안함.
- T3: 진행 중. 변경 사항 반영 안함.
REDO(T1), REDO(T4)
A = 15, B = 15, C = 10, D = 20

3. 관리와 보안

3.4 연습문제

1. 4

2. 4

3. 4

4. 1

5. 3

6. 2

10.

11.

grant 명령은 db 객체에 대한 권한을 사용자나 role에 부여한다. revoke 명령은 부여된 권한을 회수하거나 제한한다.

12.

GRANT INSERT ON madangdb.order TO mdguest;

GRANT SELECT, DELETE ON madangdb.order TO mdguest2 WITH GRANT OPTION;

GRANT UPDATE ON madangdb.book TO mdguest;

13.

전체 백업은 db 내 모든 데이터를 백업 파일로 복사한다. 복구 시간이 빠르고 모든 데이터를 한 번에 복구 가능하지만, 백업 크기가 크다. 차등 백업은 마지막 전체 백업 이후 변경된 데이터만 백업한다.

윤휘영

이전 포스트

프로젝트

다음 포스트

db설계

1. 정규화

1.1 이상현상

1.2 함수 종속성

1.3 정규화

1.4 연습문제

1. 2

2. 2

3. 2

4. 3

5. 4

6. 1

7. 4

8. 1

9. 4

10.

11. B->D, C->A, C->B, C->D, D->B

12.

13.

14.

15.

16.

17.

2. 트랜잭션, 동시성 제어, 회복

2.1 트랜잭션

2.2 동시성 제어

2.3 회복

2.4 연습문제

1. 4

2. 4

3. 4

4. 4

5. 1

6. 1

7. 3

8. 3

9. 4

10. 3

11. 3

12. 4

13.

14.

15.

16. 발생

17.

18.

19.

20.

3. 관리와 보안

3.4 연습문제

1. 4

2. 4

3. 4

4. 1

5. 3

6. 2

10.

11.

12.

13.

프로젝트

윈도함수

0개의 댓글