CS4432: Database Systems II

1
CS4432 Database Systems II

• Database Consistency and Violations?

2
Transactions, etc.

• Crash recovery
• Chapter 17
• Concurrency control
• Chapter 18
• Transaction processing
• Chapter 19

3
Integrity or correctness of data ?

• We would like data in our database to be
  accurate ( correct ) at all times.
• EMP
• How DBMS decides if data is consistent?

4
Integrity or consistency constraints

• Utilize predicates data must satisfy
• Examples
• - x is key of relation R
• - x ? y holds in R
• - Domain(x) Red, Blue, Green
• - no employee should make more than twice the
  average salary

5
Definitions

• Consistent state satisfies all constraints
• Consistent DB DB in consistent state

6
Such constraints may not capture full
correctness

• Example 1 Transaction constraints
• When salary is updated,
• new salary gt old salary
• When account record is deleted,
• balance 0

7
Constraints (as we use here) may not capture
full correctness

• Example 2 Database should reflect real
  world

Reality
DB
8
?in any case, continue with constraints ...

• Observation DB cannot be consistent always
• Example
• Constraint a1 a2 . an TOT
• Action
• Deposit 100 in a2 a2 ? a2 100
• TOT ? TOT 100

9
Example a1 a2 . an TOT (constraint) Deposi
t 100 in a2 a2 ? a2 100 TOT ? TOT
100

• a2
• TOT

. .
. .
. .
50
150
150
. .
. .
. .
1000
1000
1100
10
Transaction a collection of actions that
preserve consistency

Consistent DB
Consistent DB
T
11
Big assumption

• If T starts with consistent state
• AND
• T executes in isolation
• ? T leaves consistent state

12
Correctness (informally)

• If we stop running transaction(s), DB left
  consistent
• Each transaction sees a consistent DB

13
How can constraints be violated?

• Transaction bug
• DBMS bug
• Hardware failure
• e.g., disk crash alters balance of account
• Data sharing
• e.g. T1 give 10 raise to programmers
  T2 change programmers ? systems analysts

14
Will not consider

• How to write correct transactions
• How to write correct DBMS system
• Constraint checking repair

15
How can we prevent/fix violations?

• Chapter 17 due to failures only
• Chapter 18 due to data sharing only
• Chapter 19 due to failures and sharing

16
Chapter 17 Recovery

• First Failure Model

17

• Events Desired
• Undesired Expected
• Unexpected

18
Our failure model

• processor
• memory disk

CPU
D
M
19

• Desired events see product manuals.
• Undesired expected events
• System crash
• - memory lost
• - cpu halts, resets

20
Undesired Unexpected Everything else!

• Examples
• Disk data is lost
• Memory lost without CPU halt
• CPU implodes wiping out universe.
• You name it

21
Is this model reasonable?

• Approach Add low level checks redundancy
  to increase
• probability that model holds
• E.g., Replicate disk storage (stable store)
• Memory parity
• CPU checks

22
Operations reStorage Hierarchy

• Input (x) block containing x ? memory
• Output (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

23
Key problem Unfinished transaction

• Example Constraint AB
• T1 A ? A ? 2
• B ? B ? 2

24

• T1 Read (A,t) t ? t?2
• Write (A,t)
• Read (B,t) t ? t?2
• Write (B,t)
• Output-to-Disk (A)
• Output-to-Disk (B)

A 8 B 8
A 8 B 8
memory
disk
25

• Need atomicity
• execute all actions of a transaction or none
  at all

26

• One solution undo logging (immediate
• 
  modification)
• A la Hansel and Gretel recording their navigation
  through forest via bread crumbs

Must have durable undo logging !!!
27
Undo logging (Immediate modification)

• T1 Read (A,t) t ? t?2 AB
• Write (A,t)
• Read (B,t) t ? t?2
• Write (B,t)
• Output-to-disk (A)
• Output-to-disk (B)

ltT1, startgt ltT1, A, 8gt
A8 B8
A8 B8
ltT1, B, 8gt
ltT1, commitgt
disk
memory
log
28
One complication First disk, then log

• Log is first written in memory
• Not written to disk on every action
• memory
• DB
• Log

A 8 B 8
A 8 16 B 8 16 Log ltT1,startgt ltT1, A, 8gt ltT1,
B, 8gt
29
One complication first log, then disk.

• Log is first written in memory
• Not written to disk on every action
• memory
• DB
• Log

A 8 B 8
A 8 16 B 8 16 Log ltT1,startgt ltT1, A, 8gt ltT1,
B, 8gt ltT1, commitgt
...
ltT1, B, 8gt ltT1, commitgt
30
Undo logging rules

• For every action generate undo log record
  (containing old value)
• (2) Before x is modified on disk,
• log records pertaining to x must be
• on disk (write ahead logging)
• (3) Before commit is written to log on disk,
• all writes of transaction must be
• reflected on disk

31
Recovery rules Undo logging

• (1) Let S set of transactions with ltTi,
  startgt in log, but no
• ltTi, commitgt (or ltTi, abortgt) record in log
• (2) For each ltTi, X, vgt in log,
• in reverse order (latest ? earliest) do
• - write old value from log back to disk
• - if Ti ? S then - write (X, v)
• - output (X)
• (3) For each Ti ? S do
• - write ltTi, abortgt to log

32

• What if failure during recovery?
• No problem! ? Undo idempotent

33
To discuss next

• Redo logging
• Undo/redo logging, why both?
• Checkpoints

34
Redo logging (deferred modification)

• T1 Read(A,t) t t?2 write (A,t)
• Read(B,t) t t?2 write (B,t)
• Output(A) Output(B)

A 8 B 8
A 8 B 8
DB
memory
LOG
35
Redo logging rules

• (1) For every action, generate redo log record
  (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

36
Recovery rules Redo logging

• (1) Let S set of transactions with ltTi,
  commitgt in log
• (2) For each ltTi, X, vgt in log, in forward
• order (earliest ? latest) do
• - if Ti ? S then Write(X, v)
• Output(X) optional

37
Recovery is very, very SLOW !

• Redo log
• First T1 wrote A,B Last
• Record Committed a year ago Record
• (1 year ago) --gt 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)

Crash
...
...
...
...
...
...
40
Key drawbacks

• Undo logging Too many disk IOs
• Redo logging need to keep all modified
  blocks in memory until commit

41
Solution undo/redo logging!

• Combine Undo/Redo Logging, namely
• Update ? ltTi, Xid, New X val, Old X valgt
• page X

42
Rules

• Page X can be flushed before or after Ti commit
• Log record flushed before corresponding updated
  page (WAL)
• Flush at commit (log only)

43
Non-quiescent checkpoint

• L
• O
• G
• for
• undo dirty buffer
• pool pages
• flushed

Start-ckpt active TR Ti,T2,...
end ckpt
...
...
...
...
44
Examples what to do at recovery time?

• no T1 commit
• L
• O
• G

T1,- a
...
Ckpt T1
...
Ckpt end
...
T1- b
...
? Undo T1 (undo a,b)
45
Example

• L
• O
• G

...
T1 a
...
...
T1 b
...
...
T1 c
...
T1 cmt
...
ckpt- end
ckpt-s T1
? Redo T1 (redo b,c)
46
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
start check- point
forward pass
47
Summary

• Consistency of data
• One source of problems failures
• - Logging
• - Redundancy
• Another source of problems Data
  Sharing..... next