csce 608 – 600 database systems

CSCE 608 – 600 Database Systems

Chapter 17: Coping with System Failures

1

Failure Recovery

Introduction

Undo Logging

Redo Logging

2

3

Operations:

Input (x): block containing x memoryOutput (x): block containing x disk

Read (x,t): do input(x) if necessary t value of x in block

Write (x,t): do input(x) if necessary value of x in block t

4

Key problem Unfinished transaction

Example Constraint: A=B

T1: A A 2

B B 2

T1: Read (A,t); t t2Write (A,t);Read (B,t); t t2Write (B,t);Output (A);Output (B);

A: 8B: 8

A: 8B: 8

memory disk

5

T1: Read (A,t); t t2Write (A,t);Read (B,t); t t2Write (B,t);Output (A);Output (B);

A: 8B: 8

A: 8B: 8

memory disk

1616

6

T1: Read (A,t); t t2Write (A,t);Read (B,t); t t2Write (B,t);Output (A);Output (B);

A: 8B: 8

A: 8B: 8

memory disk

1616

16

failure!

7

8

Need atomicity: execute all actions ofa transaction or none at all

9

One solution: undo logging (immediatemodification)

T: Read (A,t); t t2 A=BWrite (A,t);Read (B,t); t t2Write (B,t);Output (A);Output (B);

A:8B:8

A:8B:8

memory disk log

Undo logging (Immediate modification)

10

T: Read (A,t); t t2 A=BWrite (A,t);Read (B,t); t t2Write (B,t);Output (A);Output (B);

A:8B:8

A:8B:8

memory disk log

Undo logging (Immediate modification)

<T, start>

11

T: Read (A,t); t t2 A=BWrite (A,t);Read (B,t); t t2Write (B,t);Output (A);Output (B);

A:8B:8

A:8B:8

memory disk log

Undo logging (Immediate modification)

16

<T, start>

12

T: Read (A,t); t t2 A=BWrite (A,t);Read (B,t); t t2Write (B,t);Output (A);Output (B);

A:8B:8

A:8B:8

memory disk log

Undo logging (Immediate modification)

16

<T, start><T, A, 8>

13

T: Read (A,t); t t2 A=BWrite (A,t);Read (B,t); t t2Write (B,t);Output (A);Output (B);

A:8B:8

A:8B:8

memory disk log

Undo logging (Immediate modification)

16

<T, start><T, A, 8>

16

14

T: Read (A,t); t t2 A=BWrite (A,t);Read (B,t); t t2Write (B,t);Output (A);Output (B);

A:8B:8

A:8B:8

memory disk log

Undo logging (Immediate modification)

16

<T, start>

<T, B, 8><T, A, 8>

16

15

T: Read (A,t); t t2 A=BWrite (A,t);Read (B,t); t t2Write (B,t);Output (A);Output (B);

A:8B:8

A:8B:8

memory disk log

Undo logging (Immediate modification)

16

<T, start>

16 <T, B, 8><T, A, 8>

16

16

T: Read (A,t); t t2 A=BWrite (A,t);Read (B,t); t t2Write (B,t);Output (A);Output (B);

A:8B:8

A:8B:8

memory disk log

Undo logging (Immediate modification)

16

<T, start>

16 <T, B, 8>

16

<T, A, 8>

16

17

T: Read (A,t); t t2 A=BWrite (A,t);Read (B,t); t t2Write (B,t);Output (A);Output (B);

A:8B:8

A:8B:8

memory disk log

Undo logging (Immediate modification)

16

<T, start>

<T, commit>16 <T, B, 8>

16

<T, A, 8>

16

18

19

One “complication”

Log is first written in memoryNot written to disk on every action

memory

DB

Log

A: 8 16B: 8 16Log:<T,start><T, A, 8><T, B, 8>

A: 8B: 8

20

One “complication”

Log is first written in memoryNot written to disk on every action

memory

DB

Log

A: 8 16B: 8 16Log:<T,start><T, A, 8><T, B, 8>

A: 8B: 8

16BAD STATE

# 1

21

One “complication”

Log is first written in memoryNot written to disk on every action

memory

DB

Log

A: 8 16B: 8 16Log:<T1,start><T1, A, 8><T1, B, 8><T1, commit>

A: 8B: 8

16BAD STATE

# 2

<T, B, 8><T, commit>

...

22

Undo logging rules

(1) For every action generate undo logrecord (containing old value)

(2) Before x is modified on disk, logrecords pertaining to x must beon disk (write ahead logging: WAL)

(3) Before commit is flushed to log, allwrites of transaction must be

reflected on disk

23

Recovery rules: Undo logging

(1) Let S = set of transactions with

<T, start> in log, but no

<T, commit> (or <T, abort>) record in log

(2) For each <T, X, v> in log,

in reverse order (latest earliest) do:

- if T S then

- write (X, v)

- output (X)

(3) For each T S do

- write <T, abort> to log

24

What if failure during recovery?

No problem! Undo idempotent

25

To discuss:

Redo loggingUndo/redo logging, why both?Real world actionsCheckpointsMedia failures

26

Redo logging (deferred modification)

T: Read(A,t); t t2; write (A,t);

Read(B,t); t t2; write (B,t);

Output(A); Output(B)

A: 8B: 8

A: 8B: 8

memory DB LOG

27

Redo logging (deferred modification)

T: Read(A,t); t t2; write (A,t);

Read(B,t); t t2; write (B,t);

Output(A); Output(B)

A: 8B: 8

A: 8B: 8

memory DB LOG

<T, start>

28

Redo logging (deferred modification)

T: Read(A,t); t t2; write (A,t);

Read(B,t); t t2; write (B,t);

Output(A); Output(B)

A: 8B: 8

A: 8B: 8

memory DB LOG

16<T, start>

29

Redo logging (deferred modification)

T: Read(A,t); t t2; write (A,t);

Read(B,t); t t2; write (B,t);

Output(A); Output(B)

A: 8B: 8

A: 8B: 8

memory DB LOG

16<T, start><T, A, 16>

30

Redo logging (deferred modification)

T: Read(A,t); t t2; write (A,t);

Read(B,t); t t2; write (B,t);

Output(A); Output(B)

A: 8B: 8

A: 8B: 8

memory DB LOG

16<T, start>

16<T, A, 16>

31

Redo logging (deferred modification)

T: Read(A,t); t t2; write (A,t);

Read(B,t); t t2; write (B,t);

Output(A); Output(B)

A: 8B: 8

A: 8B: 8

memory DB LOG

16<T, start>

16 <T, B, 16><T, A, 16>

32

Redo logging (deferred modification)

T: Read(A,t); t t2; write (A,t);

Read(B,t); t t2; write (B,t);

Output(A); Output(B)

A: 8B: 8

A: 8B: 8

memory DB LOG

16<T, start>

16<T, commit><T, B, 16><T, A, 16>

33

Redo logging (deferred modification)

T: Read(A,t); t t2; write (A,t);

Read(B,t); t t2; write (B,t);

Output(A); Output(B)

A: 8B: 8

A: 8B: 8

memory DB LOG

16<T, start>

output16

16 16<T, commit><T, B, 16><T, A, 16>

34

Redo logging rules

(1) For every action, generate redo logrecord (containing new value)

(2) Before X is modified on disk (DB),all log records for transaction that

modified X (including commit) must be on disk

(3) Flush log at commit to Disk

35

(1) Let S = set of transactions with<T, commit> in log

(2) For each <T, X, v> in log, in forward

order (earliest latest) do:

- if T S then Write(X, v) Output(X)

optional

Recovery rules: Redo logging

Failure Recovery

Checkpointing

Undo/Redo Logging

36

37

Recovery is very, very SLOW !Redo log:

First T1 wrote A,B Last

Record Committed a year ago Record

(1 year ago) --> STILL, Need to redo after crash!!

... ... ...

Crash

38

Solution: Checkpoint (simple version)

Periodically:(1) Do not accept new transactions(2) Wait until all transactions finish(3) Flush all log records to disk (log)(4) Flush all buffers to disk (DB) (do not discard buffers)

(5) Write “checkpoint” record on disk (log)(6) Resume transaction processing

39

Example: what to do at recovery?

Redo log (disk):

<T1

,A,1

6>

<T1

,com

mit

>

Ch

eck

poin

t

<T2

,B,1

7>

<T2

,com

mit

>

<T3

,C,2

1>

Crash... ... ... ...

...

...

Start from last checkpoint and move forwardin the log file redoing updates for committedtransactions.

40

Key drawbacks:

Undo logging: data must be written to disk immediately after a transaction finishes, which can increase number of disk I/O's

Redo logging: need to keep all modified blocks in memory until transaction commits and log is flushed, which can increase the number of buffers required

41

Solution: undo/redo logging!

Update record in the log has the format

<T, X, new X val, old X val>

42

Rules

Buffer containing X can be flushed to disk either before or after T commits

Log record must be flushed to disk before corresponding updated buffer is (WAL)

43

Recovery with Undo/Redo Logging

1. Redo all committed transactions in order from earliest to latest

handles committed transactions with some changes not yet on disk

2. Undo all incomplete transactions in order from latest to earliest

handles uncommitted transactions with some chnages already on disk

44

Non-quiescent Checkpoint

Simple checkpointing scheme requires system to "quiesce" (reach a point with no active transactions), ensured by preventing new transactions from starting for a while

Avoid this behavior with non-quiescent checkpointing: write a "start checkpoint" record to the log later write an "end checkpoint" record to the log

Details vary depending on whether undo, redo, or undo/redo logging

45

Non-quiescent Checkpoint for Undo/Redo

write "start checkpoint" listing all active transactions to log

flush log to disk write to disk all dirty buffers (contain a changed

DB element), whether or not transaction has committed this implies some log records may need to be written

to disk (WAL) write "end checkpoint" to log flush log to disk

46

Non-quiescent checkpoint for undo/redo

LOG

for undo dirty buffer

pool pagesflushed

start ckptactive T's:T1,T2,...

endckpt

.........

...

47

Recovery process:

Backwards pass (end of log latest checkpoint start)

construct set S of committed transactions undo actions of transactions not in S

Undo pending transactions follow undo chains for transactions in

(checkpoint active list) - S Forward pass (latest checkpoint start end of log)

redo actions of S transactions

backward pass

forward passstart

check-point

48

Examples what to do at recovery time?

no T1 commit

LOG

T1,-a

...Ckpt-s

T1...

Ckptend

...T1-b

...

Undo T1 (undo a,b)

49

Example

LOG ...

T1

a... ...

T1

b... ...

T1

c...

T1

cmt...

ckpt-end

ckpt-sT1

Redo T1: (redo b,c)

50

Media failure (loss of non-volatilestorage)

A: 16

Solution: Make copies of data!

51

Example 1 Triple modular redundancy

Keep 3 copies on separate disksOutput(X) --> three outputsInput(X) --> three inputs + vote

X1 X2 X3

52

Example 2 Redundant writes, Single reads

Keep N copies on separate disksOutput(X) --> N outputsInput(X) --> Input one copy

- if ok, done

- else try another one

Assumes bad data can be detected

53

Example 3: DB Dump + Log

backupdatabase

activedatabase

log

• If active database is lost,– restore active database from backup– bring up-to-date using redo entries in log

54

When can log be discarded?

check-point

dbdump

lastneededundo

not needed formedia recovery

not needed for undoafter system failure

not needed forredo after system failure

log

time

55

Summary

Consistency of dataOne source of problems: failures

- Logging

- RedundancyAnother source of problems:

Data Sharing