Db2 practice 8 solution PDF

Preview

Summary

Exercise 17.1: Suppose that the consistency constraint on the database is 0 <= A <= B. Tell whether each of the following transactions preserves consistency. a) A := A+B; B := A+B -> preserves: at the end of transaction from A -> A+B, from ...

Description

Exercise 17.1.1: Suppose that the consistency constraint on the database is 0 A+B, from B -> A+2B b) B := A+B; A := A+B -> doesn't preserve: A -> 2A+B, B -> A+B c) A := B+1; B := A+1 -> preserves: A -> B+1, B -> B+2 Basic operations: Input (x): system reads block containing x into memory Output (x): system writes block containing x to disk Read (x,t): read x into transaction's local variable t (input(x) if necessary) Write (x,t): write value of t into x in memory (input(x) if necessary) t:= ... give new value to local variable t Exercise 17.1.2: For each of the transactions of Exercise 17.1.1, add the read- and write-actions to the computation and show the effect of the steps on main memory and disk. Assume that initially A = 5 and B = 10. t s M-A M-B D-A D-B --------------------------------------------READ(A,t) 5 5 5 10 READ(B,s) 5 10 5 10 5 10 t:=t+s 15 10 5 10 5 10 WRITE(A,t) 15 10 15 10 5 10 s:=t+s 15 25 15 10 5 10 WRITE(B,s) 15 25 15 25 5 10 OUTPUT(A) 15 25 15 25 15 10 OUTPUT(B) 15 25 15 25 15 25

Exercise 17.2.1: Show the undo-log records for each of the transactions (call each T) of Exercise 17.1.1, assuming that initially A = 5 and B = 10. Log records: , , , Rules of 1. write LOG] 2. write frequent 3. write LOG]

UNDO log: log entries to disk (Write Ahead Log) [ ... + FLUSH modified data elements to disk [output(X)] (-> problem: too output) COMMIT log entry to log file on disk [ + FLUSH

Exercise 17.2.4: The following is a sequence of undo-log records written by two transactions T and U:







Describe the action of the recovery manager, including changes to both disk and the log, if there is a crash and the last log record to appear on disk is: (a) -> WRITE(A,10), OUTPUT(A), , , FLUSH LOG (b) -> WRITW(A,11), OUTPUT(A), WRITE(C,30), OUTPUT(C), WRITE(A,10) OUTPUT(A), , FLUSH LOG (c) -> WRITE(E,50), OUTPUT(E), WRITE(A,11), OUTPUT(A), WRITE(C,30), OUTPUT(C), WRITE(A,10) OUTPUT(A), , FLUSH LOG (d) -> Do nothing

Checkpoint in UNDO LOG (simple) Decide the Checkpoint Wait till all active transaction finish (no new transactions) + FLUSH LOG Checkpoint in UNDO LOG (non-quiecent) + FLUSH LOG Wait till all active transaction finish (new transactions can work) + FLUSH LOG For Recovery: Last - pair (-> we don't need the log before ) If we find -> first Exercise 17.3.1: Show the redo-log records for each of the transactions (call each T) of Exercise 17.1.1, assuming that initially A = 5 and B = 10. -> Log records: , , , , Rules of REDO log: 1. write log entries to disk (Write Ahead Log) [ ... + FLUSH LOG] 2. write COMMIT log entry to log file on disk [ + FLUSH LOG] 3. write modified data elements to disk [output(X)] (-> problem: too late output) Exercise 17.3.3: Repeat Exercise 17.2.4 for redo logging.







Describe the action of the recovery manager, including changes to both disk and the log, if there is a crash and the last log record to appear on disk is: (a) -> Do nothing (b) -> WRITE(B,20), OUTPUT(B), WRITE(D,40) OUTPUT(D), WRITE(B,21) OUTPUT(D), , FLUSH LOG (c) -> same as in b) (d) -> WRITE(B,20), OUTPUT(B), WRITE(D,40) OUTPUT(D), WRITE(B,21) OUTPUT(D), , FLUSH LOG, WRITE(A,10), OUTPUT(A), WRITE(C,30) OUTPUT(C), WRITE(A,11), OUTPUT(A), WRITE(E,50), OUTPUT(E), , FLUSH LOG Checkpoint (in REDO LOG) + FLUSH LOG Write dirty buffers of transactions committed before START CKPT to disk + FLUSH LOG For Recovery enough: last - pair First Exercise 17.4.1: Show the undo/redo-log records for each of the transactions (call each T) of Exercise 17.1.1, assuming that initially A = 5 and B = 10. -> Log records: , , , ,

Rules of UNDO/REDO log: 1. write log entries to disk (Write Ahead Log) can be written before OUTPUT or after OUTPUT Exercise 17.4.3: The following is a sequence of undo/redo-log records written by two transactions T and U: ; ; ; ; ; ; ; ; .

Describe the action of the recovery manager, including changes to both disk and the log, if there is a crash and the last log record to appear on disk is: (a) -> undo steps for T and U (b) -> undo steps for T and redo steps for U (c) -> undo steps for T and redo steps for U (d) -> redo steps for T and U Checkpoint (in UNDO/REDO LOG) + FLUSH LOG Write all dirty buffers to disk + FLUSH LOG For Recovery according to UNDO or REDO rules...