Chap. 15  Transactions

*Textbook: Chapter 14(6판), Chapter 17(7판)

Concept

∙ Transaction의 개념:

- A collection of several unit operations on the DB

⇒ A 'single' unit (i.e., 하나의 논리적 작업을 이루는 단위연산들의 집합)

Ex) 계좌 입출금, 이체 (a transfer of funds) 등

T10012-092:	Read a record from accnt 10012

인출 5000원

Write a record into accnt 10012

Ex) A transaction that transfers $50 from accnt. A to accnt. B

Ti:	Read(A);

A = A - 50;

Write(A);

Read(B);

B = B + 50;

Write(B);

- 'All' these operations occur, or 'none' occur in case of failure.

⇒ All or None

∙ ACID properties

① Atomicity(원자성): Either 'ALL' operations of the transaction are reflected properly in the DB, or 'NONE' are.

(트랜잭션의 처리결과는 Commit이거나 Aborted이어야 함)

② Consistency(일관성): Execution of a transaction preserves the 'CONSISTENCY' of the DB.

(트랜잭션은 유효한 상태에서 다른 유효한 상태로 만들어야 함)

③  Isolation(고립성): Even though ‘multiple’ transactions may executed ‘concurrently’, each transaction is unaware of other transactions executing concurrently in the DB.

(여러 개의 트랜잭션이 동시에 수행될 경우, 다른 트랜잭션이 아직 수행이 끝나지 않은 트랜잭션의 ‘중간’ 결과에 접근할 수 없어야 함. 즉, 다른 트랜잭션들은 현재 진행 중인 트랜잭션의 처음 값이나 결과 값만에 접근할 수 있어야 함)

④ Durability(지속성): After a transaction completes successfully, the changes it has made to the DB 'PERSIST', even if there are system failures.

(보존성을 보장해야 함. 즉, 서버에 장애가 발생해도 '이미 처리된' 트랜잭션의 결과는 보존되어야 함.)

- 참조: p.580

∙ How the DBMS provides these properties?

- Transaction의 수행과정: DBMS가 A(C)ID를 보장하여야 함

Aborted

Consistency (올바른 값으로 update할것) -Programmer의 책임

Atomicity(all or none)-DBMS의 책임

Durability(system crash의 경우 recovery를 보장, 즉, ‘commit’된 결과는 처리되어야 함)- DBMS의 책임

Isolation(동시 수행되는 transaction들은 serial-

izable schedule에 의해 serial execution이 보장되어야 함)-DBMS의 책임

<T1: start>

…	redo log file 'write'

undo	update A	→ <A: old-value, new-value>

update B	→ <B: old-value, new-value>

…

‘crash’	<T1: commit>

‘crash’	…

redo	DB 'write'

Committed

1. Consistency:

- The Sum of A and B must be unchanged by the execution of Ti.

⇒ Money cannot be created or destroyed by the trans.

- The DB remains 'consistent' after the trans.

⇒ The responsibility of the appl. programmer who codes the trans.

A: 1000

B: 1000

S1

A: 500

B: 1500

S2

T1

'consistent' state 1

'consistent' state 2

2. Atomicity

- A 'failure' can happen during the execution of Ti (중간에 실패한 경우)

⇒ Power failure, H/W failure, O/S errors and so on.

- Suppose that the failure occurred between Write(A) and Write(B).

⇒ Money is 'destroyed' as a result of the failure.

⇒ Transactions must be guaranteed to be 'completed' or 'never started'.

Ti:	Read(A);

A = A - 50;	'Consistent' state  (A=$100, B=$200, A+B=$300)

Write(A);

Read(B);	'Inconsistent' state (A=$50, B=$200, A+B=$250)

B = B + 50;

Write(B);

'Consistent' state  (A=$50, B=$250, A+B=$300)

- Ensuring atomicity is the responsibility of the DBMS.

⇒ The DBMS has to keep track of the 'old values' of any data.

⇒ The DBMS must 'restore' the 'old values' of the data on which a trans. performs a 'write' operation IF the trans. does NOT complete its execution.

e.g, Undo

- 'Transaction Management' of the DBMS

T1 : Begin Transaction

Read a record

인출 500원

Write a record

End Transaction

3. Isolation

- If several transactions are executed 'concurrently', their operations may interleave in some 'undesirable' way.

⇒ Resulting in an 'inconsistent' state.

Ex) Concurrent access anomalies

- 구좌 123: 예금액 1,000원.

- Two concurrent transactions on 구좌 123.

T1: Read a record          T2:

Read a record

인출 500원

인출 500원

Write a record

Write a record

⇒ What happens?

- The DBMS must provide 'isolation'.

⇒ The concurrent execution of transactions results in a system state that is 'equivalent' to a state that could have been executed 'serially'.

⇒ 'Concurrency Control' of the DBMS

4. Durability:

- Once a transaction commits successfully, all the updates that it carried out on the DB 'persist'. (commit 후에 실패한 경우)

- We assume that a failure may result in loss of data in main memory, NOT on disk.

⇒ 'Recovery' of the DBMS

- The DBMS guarantees durability by ensuring that

ⓐ The updates carried out by the trans. have been written to disk 'before' the trans. completes.

⇒ 'log' files

ⓑ When the DBMS is restarted after the failure, it is possible to 'reconstruct' the updates with sufficient info. about the updates.

e.g., Redo

∙ 5 Transaction States

①Active(동작): The initial state

⇒ The trans. stays in this state while it is executing.

②Partially committed(부분완료): After the final statement <commit> has been executed.

⇒ Since the "actual" update may still be temporarily residing in main memory, it is still possible that it may have to be aborted due to H/W failure. (redo)

e.g., "before checkpoint"

③Committed(완료): After successful completion.

④Failed(실패): After the discovery that normal execution can NO longer proceed.

⑤Aborted(취소): After the transaction has been 'rolled back' and the DB has been 'restored' to its prior state.

⇒ The changes caused by an aborted trans. have to be 'undone'.

- 'All' statements occur, or 'none' occur in case of failure.

⇒ Committed (redo) or Aborted (undo)

Ex) p.585 Fig.14-1

redo

undo

- When aborted, the DBMS has two options;

Case 1: Some internal logical error inside the trans., bad input data, or not-found data (Trans. 자체에 오류가 있는 경우)

⇒ 'Kill' the trans. and correct the error.

Case 2: Some H/W or system S/W problem (Trans. 자체에 문제가 없는 경우)

⇒ 'Restart' the trans. as a new trans. after fixing the problem.

e.g., Redo/Undo

Isolation(고립성)

• Allows multiple transactions to update data concurrently ensuring consistency.

① 처리율과 자원(CPU & Disk) 이용률 향상

⇒ Concurrency increases the 'throughput' (the no. of transactions/sec).

⇒ the CPU and disk 'utilization' increases.

ex) T1, T2, T3: Read (A)

CPU operation

Write (B)

CPU         Table A    Table B

DBMS

T2

T3

T1

T1

A

B

A

cpu

B

A

cpu

B

A

cpu

B

t

T2

T3

② 대기시간 감소: 순차적으로 실행된다면 짧은 transaction이 긴 transaction이 끝날 때까지 오래 기다려야 함

- But, concurrent access anomalies (i.e., inconsistency) can happen.

⇒ DB의 비일관성 발생 가능

Ex)	- Balance: Accnt. A - $1000, Accnt. B - $2000

- T1: Transfer $50 from accnt. A to accnt B

T2: Transfer 10% of the balance from accnt. A to accnt B

T1:	Read(A);	T2:	Read(A);

A = A - 50;	temp = A * 0.1;

Write(A);	A = A - temp;

Read(B);	Write(A);

B = B + 50;	Read(B);

Write(B);	B = B + temp;

Write(B);

<순차적 스케쥴(Serial Schedule)>

① Serial Schedule 1: T1 → T2 (p.589 그림 14.2)

결과: Accnt. A - $855, Accnt. B - $2145

⇒ Consistency is preserved. (total - $3000)

② Serial Schedule 2: T2 → T1 (p.590 그림 14.3)

결과: Accnt. A - $850, Accnt. B - $2150

⇒ Consistency is preserved. (total - $3000)

- For a set of n trans., there exists n! different valid 'serial' schedules.

- The schedule no longer needs to be ‘serial’.

⇒ A time-sharing OS performs a context switch.

⇒ Operations can be interleaved.

⇒ ‘Much larger different valid’ schedules than n!

⇒ ‘Serializable' schedule

<동시 스케쥴(Concurrent Schedule)>

③ Concurrent Schedule 3: p.591 그림 14.4

결과: Equivalent to Schedule 1

⇒ ‘Consistency‘ is preserved.  (total - $3000)

- Question: Are all concurrent schedules valid?

④ Concurrent Schedule 4: p.591 그림 14.5

Accnt. A - $950, accnt. B - $2100

⇒ Inconsistency  (total - $3050)

- If concurrent execution of trans. is left entirely to the OS, many 'invalid' schedules are possible.

⇒ The DBMS needs the Concurrency Control scheme.

⇒ All 'valid concurrent' schedules must be equivalent to a 'serial' schedule

<Ti, Tj, …>.

⇒ ‘serializable’ schedule

∙ Isolation

- Supported by a Concurrency Manager of the DBMS

- Chap 15) 동시성 제어(concurrency control)

Atomicity(원자성)/Durability(지속성)

• Failures(고장/실패)

Trans.

Disk

Trans. Failure

System Crash

Disk Failure

'Recovery' by DBMS from redo log file

'Backup/Restore' by DBA

from Backup Files

DBMS

OS

H/W

- 참조: p.580

① Trans. Failure

□Logical Error: Some internal problem such as 'bad input', 'data not found', 'overflow' or 'resource limit exceeded'.

□System Error: Undesirable state such as 'deadlock'.

⇒ Recovery by DBMS

② System Crash: System halt such as 'H/W problem's, 'bugs in DBMS or OS'.

⇒ Fail-Stop Assumption: The disk contents are NOT corrupted when a system halts.

⇒ Recovery by DBMS

③ Disk Failure: Loss of disk blocks

⇒ Copies on 'other' disks or tapes are used to recover from the failure.

⇒ Backup/Restore by DBA

• Objective of Recovery Scheme

- Fault-tolerance: How the DB can recover from failure?

- Failure 발생 시 복구 후에 시스템을 재시작(restart) 시킬 때

①원자성(atomicity) 보장: 실패한(failed) 트랜잭션의 작업들을 DBMS가 무효화시킴.

⇒ All or Nothing

⇒ Undo

②지속성(durability) 보장: 완결된(partially committed) 트랜잭션의 결과를 DBMS가 DB에 반영될 수 있도록 함.

⇒ Redo

• 'Recovery scheme' consists of two parts.

① Actions taken during 'normal' trans. processing: Log file

⇒ A log contains info. on 'transactions': <Ti, Xj, V1, V2>

□Transaction ID (트랜잭션 식별자)

□Data item (데이터 항목)

□The 'old' record value(이전 값): Required for 'undo'

□The 'new' record value(새로운 값): Required for 'redo'

- 참조: p.669

② Actions taken following a 'failure': Recover DB contents from the log file.

□commit되기 전에 abort된 trans. T: Undo(T)

□commit되었지만 아직 DB에 반영이 되지 않은 trans. T: Redo(T)

⇒ Ensures DB consistency, trans. atomicity and durability.

ex) 참조: p.673

p.674 그림 16.4 : 즉시 갱신 – transation 중간에 checkpoint 때 갱신 발생

• Checkpoint (검사점)

- The DBMS periodically performs checkpoints.

log

log

Checkpoint

log

failure

Checkpoint

• Log file

- The log file contains a 'complete' record of all DB activities.

⇒ As a result, the volume of a file may become unreasonably large.

⇒ When is it safe to 'erase' log records?

① 즉시 갱신(immidiate update): trans. 중간에(checkpoint 때) 기록(DB write).

⇒ 중간 값이 DB write

⇒ undo/redo 해야 하므로 old/new value를 log에 저장

② 지연 갱신(deferred update): trans. commit 후에 모두 한꺼번에 기록.

⇒ 최종 값만 DB write

⇒ undo가 필요 없으므로 new value만 log에 저장

• Atomicity and Durability

- Supported by a Recovery Manager of the DBMS

- Chap 16) 복구(recovery)