- 81 Views
- Uploaded on
- Presentation posted in: General

Chapter 21 Recoverable Techniques

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

**1. **Chapter 21 Recoverable Techniques

**2. **19-1 21-2 Strategy for recovery
Catastrophic failure
Restore a past copy of the database
Reconstruct a more current state by reapplying or redoing committed transaction operations
Noncatastrophic failure
Reverse the changes by undoing some operations
Redo some operations to restore a consistent state

**3. **19-2 21-3 Deferred update (NO-UNDO/REDO Algorithm)
Do not update the database until after a transaction reaches its commit point
Before commit transaction workspace (buffers)
During commit log. database

**4. **19-2 21-4 Immediate update (UNDO/REDO Algorithm)
update by some operations before a transaction reaches its commit point.

**5. **19-3 21-5 DBMS cache: hold database items
?directory ?flush ?dirty bit
Flushing a modified data item
In-place updating
? data items are written in the same disk location
BFIM?AFIM
? single copy of each database block
Shadowing new items are written at different disk location
In-place updating
REDo-type log entry: after image (AFIM)
UNDo-type log enty : before image (BFIM)
WRITE-AHEAD LOGGING (WAL)
BFIM ?log ?disk
BFIM is replaced

**6. **19-3.1 21-6

**7. **19-3.2 21-7

**8. **19-4 21-8

**9. **19-5 21-9

**10. **19-6 21-10 Deferred Update Protocol (NO-UNDO / REDO recovery algorithm) A transaction cannot change the database until it reaches its commit point.
A transaction does not reach its commit point until all its update operations are recorded in the log and log is force-written to disk. (WAL protocol)

**11. **19-6 21-11 Single User Environment RDU_S (recovery using deferred update in a single-user environment) Apply the REDO operation to all the write_item operations of committed transactions from the log in the order in which they were written to the log.
Restart the active transaction (at most one)

**12. **19-7 21-12

**13. **19-8 21-13

**14. **19-9 21-14 Multi user Environment the locks remain until commit point Example
concurrency control:
(strict & serializable schedules) two-phase locking
Recovery process: deferred update
The greater the degree of concurrency, the more time consuming the task of recovery
RDU_M (recovery using deferred update in a multi user environment)
REDo all the WRITE operations of the committed transactions from the log in the order they were written into log
The active transactions are effectively canceled and must be resubmitted.

**15. **19-10 21-15

**16. **19-10.1 21-16

**17. **19-11 21-17

**18. **19-12 21-18

**19. **19-12.1 21-19

**20. **19-13 21-20 UNDO (WRITE_OP) Examine the log entry [write_item, T, X, old_value, new_value] set X to old_value
When more than one write_item is undone, proceed in the reverse order from the order in which the operations were written in the log.

**21. **19-14 21-21 UNDO/REDO Immediate Update with Concurrent Execution Concurrency Control Protocol- strict two phase locking protocol
a transaction is not allowed to read or write an item unless the transaction writing the item has committed (or aborted) deadlock?rollback?undo

**22. **19-14 21-22 UNDO/REDO Immediate Update with Concurrent Execution (Cont.) PROCEDURE RIU_M
Use two lists of transactions
Committed transactions
Active transactions
Undo all the write_item_operations of the active transactions in the reverse order in which they were written into the log.
Redo all the write_item operations of the committed transactions from the log in the order in which they were written into the log. vs. redo from the end of the log (redo the last update of each item X)

**23. **19-15 21-23 Shadow Paging Database is made up of a number of fixed-size disk pages. (n)
A page table with n entries is constructed
Current page table Shadow page table (not updated copy) for each transaction

**24. **19-15 21-24

**25. **19-16 21-25

**26. **10-8 21-26 Security in Statistical Databases Statistical databases – a database, such as a census database, that
(a) contains a large number of individually sensitive records, but
(b) is intended to supply only statistical summary information to its users, not information referring to some specific individual.
The only permissible queries – (statistical query)
Apply COUNT, SUM, AVERAGE to some subset of the
records in the database
Problem: deduction of confidential information by inference.

**27. **10-9 21-27

**28. **10-9 21-28 Assume some (authorized) user U knows from
Outside sources that Able is a programmer and is male.
Q1.
SELECT COUNT(*)
FROM STATS
WHERE SET=‘M’ AND OCCUPATION =‘PROGRAMMER’
Q2.
SELECT SUM(SALARY), SUM(TAX)
FROM STATS
WHERE SEX=‘M’ AND
OCCUPATION=‘PROGRAMMER’

**29. **10-10 21-29

**30. **10-11 21-30

**31. **10-12 21-31

**32. **10-13 21-32

**33. **10-14 21-33

**34. **10-14/15

**35. **10-15 21-35

**36. **10-15 21-36 Summary A general tracker “almost always” exists, and is usually both easy to find and easy to use.
Security in a statistical database is a real problem
Data swapping,…

**37. **3-1 21-37 Logic as a Data Model
(1) boss (E, M) :– manages (E,M).
(2) boss (E, M) :– boss (E,N) & manages(N,M).

**38. **3-1 21-38 The meaning of logical rules Proof-theoretic interpretation the set of facts derivable from given, or database facts, using the rules in the “forward” direction only. backward direction: ( q :– p. and ? q ) ? ? p forward direction: p ? q infer left sides (consequents, or conclusions) from right sides (antecedents or hypotheses)

**39. **3-2 21-39

**40. **3-2 21-40

**41. **3-3 21-41 Minimal model
Suppose r(1) is true, while r(x) is false for X?1.
That is, r(1) is a database instance. M1 = { r(1), q(1), p(1), q(2), p(2), p(3) } is a possible world
M2 = { r(1), q(1), p(1)} is also a consistent model M3 = { r(1), q(1), p(1), q(2), p(2) }…

**42. **3-4 21-42 Computational definitions provide an algorithm for “ executing” them to tell whether a potential fact is true.
Prolog: left-to-right computation rule depth-first search rule 1. p(x) :– q(x) 2. q(x) :– r(x) 3. r(1).

**43. **3-5 21-43 The Datalog Data Model datalog- a version of prolog suitable for database systems
Datalog does not allow function symbol in arguments. (I.E., only variable and constants are allowed)
The “meaning” of datalog programs follows the model-theoretical point of view. The underling mathematical model for datalog-relational model predicate symbols in datalog-relations

**44. **3-6 21-44 The Datalog Data Model (Cont.) In datalog, there are two ways relations can be defined: (a) extensional database (EDB) (b) intensional database (IDB)
In relational model, all relations are EDB relations.
Example
boss (E,M) :– manages (E, M)
boss (E,M) :– boss (E, N) & manages (N,M)
manages (b, a). manages (c, a). manages (d, b). manages (e, b). (1) and (2) -IDB (3) -EDB, I.E. manages relation.

**45. **3-7 21-45 Atomic formulas (Atoms) Predicate symbols with a list of arguments (variable or constant) predicate of arity K p(K). An atomic formula denotes a relation.
Select for equality between a constant and the component, e.g. customers (joe, Address, Balance) = s$1= joe (CUSTOMERS)
Selecting for equality between components that have the same variable. e.g. includes(X, Item, X) = s$1=$3 (INCLUDES)

**46. **3-8 21-46 Clauses and Horn Clauses Literal: an atomic formula or a negated atomic formula
Clause: a disjunction of literals
Horn clause:
A single positive literal -- fact
One or more negative literals with no positive literal -- integrity constraint
A positive literal and one or more negative literals -- rule

**47. **3-9 21-47 Logic program - a collection of Horn Clauses Sibling(X,Y) :– parent(X,Z) & parent(Y,Z) & X?Y

**48. **3-10 21-48

**49. **3-11 21-49 Dependency Graphs and Recursion Node: predicate
Link: predicate p ? predicate q
(1) sibling(X,Y) :– parent(X,Z) & parent(Y,Z) & X?Y sibling parent
A logic program is recursive if its dependency graph
has one or more cycles.
All the predicates that are on one or more cycles are said to be
recursive predicates.

**50. **3-12 21-50

**51. **3-13 21-51 Safe Rules source of infiniteness Variable that appears only in a built_in predicate. biggerThan(X,Y) :– X > Y.
Variable that appears only in the head of a rule loves(X,Y) :– lover(Y).

**52. **3-14 21-52 Definition limited variable Any variable that appears as an argument in an ordinary predicate of the body is limited.
Any variable X that appears in a subgoal X=a or a=X, where a is a constant, is limited.
Variable X is limited if it appears in a subgoal X=Y or Y=X, where Y is a variable already know to be limited.

**53. **3-15 21-53

**54. **3-16 21-54 Evaluating Nonrecursive Rules Nonrecursive datalog rules?relational algebra expressions
Order the nodes of the dependency graph P1,…Pn so that if there is an arc Pi?Pj then i < j.
Compute the relation for the predicates of P1,…,Pn in that order.
cousin(X,Y) :– parent(X,Xp) & parent(Y,Yp) & sibling(Xp,Yp).

**55. **3-17 21-55 The computation of the relation for Pi For each rule r, with Pi at the head, compute the relation corresponding to the body of the rule. Natural join of the relations corresponding to various susbgoals of r. Because rules are nonrecursive, and predicates are evaluated in some order, the relations for the subgoals are already evaluated.
Compute the relation for Pi itself by
Projecting the relations for each Pi’s rules onto components corresponding to the variables of the head, and
Taking the union over all rules with Pi in the head.

**56. **3-18 21-56

**57. **3-19 21-57

**58. **3-20 21-58 Algorithm 3.1 Computing the Relation for a Rule Body, Using Relational Algebra Operations. Input:
The body of a datalog rule r, which consists of
subgoals S1,…Sn.
For each Si = Pi (Ai1,…,Aiki), there is a
relation Ri already computed.
Output:
An expression of relational algebra,
EVAL(r, R1,…Rn)

**59. **3-21 21-59

**60. **3-21 21-60

**61. **3-22 21-61

**62. **3-23 21-62 Rectified Rules the rules for predicate P are rectified if all their heads are identical, and of the form P(X1,X2,…,Xk) for distinct variables X1,…,Xk
Method:
Introduce new variables for each of the arguments of the head predicate
Introduce built-in subgoals into the body to enforce the constraints the head predicate formerly enforced

**63. **3-24 21-63

**64. **3-25 21-64 Computing the Relations for Nonrecursive Rules Algorithm 3.2 Evaluating Nonrecursive Rules Using Relational Algebra Operation
Input: A nonrecursive datalog program and a relation for each EDB predicate appearing in the program
Output: For each IDB predicate P, and expression of relation algebra for P in terms of relations R1,…,Rm for EDB predicate

**65. **3-26 21-65

**66. **3-27 21-66

**67. **3-27/28 21-67

**68. **3-28 21-68

**69. **3-28 21-69

**70. **3-29 21-70 Computing the Meaning of Recursive Rules recursive datalog program Consider a datalog program with given EDB relations R1,…,Rk and with IDB relations P1,…,Pm to be computed.
For each i, 1= i = m, we can express the set of provable facts for the predicate Pi by Pi := EVAL(Pi, R1,…,Rk,P1,…,Pm) where EVAL is the union of EVAL-RULE for each of the rules for Pi.
Datalog equations: equations derived from a datalog program

**71. **3-30 21-71

**72. **3-31 21-72

**73. **3-31?1 21-73 Fixed point a solution for relation corresponding to the IDB predicates of equations.

**74. **3-31?1 21-74 Least fixed point equations P1,…,Pm correspond to IDB predicates P1,…,Pm relations R1,…,Rk are assigned to EDB predicates r1,…,rk
A fixed point for R1,…,Rk assigns to P1,…,Pm particular relations P1(1) ,…,Pm(1) s.t. the equations are satisfied.
Given S1 = P1(1),…,Pm(1) and S2 = P1(2),…,Pm(2)
If Pi(1) is a subset of Pi(2) for 1= i = m, S1 = S2. S0 is a least fixed point of a set of equations w.r.t. R1,…,Rk if for any solution S, S0 = S.

**75. **3-31?2 21-75

**76. **3-31?3 21-76

**77. **3-31?4 21-77 Solving Recursive Datalog Equations Algorithm 3.3 Evaluation of Datalog Equations for i := 1 to m do Pi := Ř; repeat for i := 1 to m do Qi := Pi ; /*save old values Pi’s*/ for i := 1 to m do Pi := EVAL(Pi,R1,…,Rk,Q1,…,Qm);
until Pi := Qi for all i, 1= i = m;
output Pi’s

**78. **3-31?5 21-78

**79. **3-31?6 21-79

**80. **3-31?7 21-80 Incremental Evaluation of Least Fixed Points Incremental tuple3 – the tuple3 found on previous round r :– S1,S2,…,Sn. (Built-in predicates are excluded) R1,…,Rn: current relations associated with subgoals S1,...,Sn. ?R1,…, ?Rn: in cremental relations. (the sets of tuples added to R1,…,Rn on the most recent round)
Incremental relation for r = EVAL-RULE-INCR(r, R1,…,Rn, ?R1,…, ?Rn) = U1=i=n EVAL-RULE(r,R1,…,Ri-1, ?Ri, RiH,…,Rn)

**81. **3-31?8 21-81

**82. **3-31?8 21-82

**83. **3-31?9 21-83

**84. **3-31?10 21-84 Semi-Naďve Evaluation Algorithm for i := 1 to m do begin ?Pi := EVAL(Pi,R1,…,Rk,Ř,…, Ř); Pi := ?Pi end;
repeat for i := 1 to m do begin ? Qi := ?Pi; /* save old ?P’s */ for i := 1 to m do begin ? Pi := EVAL-INCR(Pi,R1,…,Rk,P1,…,Pm, ?Q1,…, ?Qm); ? Pi := ? Pi - Pi end; for i := 1 to m do begin Pi := Pi ? ? Pi until ? Pi=Ř for all i; output Pi’s.

**85. **3-31?11 21-85