Transcript Data Modeling - Hiram College

Recovery
CPSC 356 Database
Ellen Walker
Hiram College
(Includes figures from Database Systems by Connolly & Begg, © Addison Wesley 2002)
Recovery
• Ensure that the database is reliable and
consistent in the presence of failure
• Recovery methods depend on failure types
Types of Failure
• System crash -> loss of main memory
• Media failure -> loss of (part of) secondary
storage
• Application software error -> failed
transaction
• Natural physical disaster
• Carelessness
• Sabotage
Database Inconsistencies
• Incomplete (not committed) transaction has
changed database on disk
• Committed transaction’s data not completely
written to disk
• Transaction changed the database, then
aborted
Transactions and Recovery
• Recall ACID
– Atomicity = all or nothing
– Durability = once done, not undone
• A transaction, once permanently recorded in
memory, cannot be undone (or partially
undone) due to failure
• Problem: writing to secondary storage is
interruptible
Write operation (change a salary)
• Find the address of the disk block that
contains the record with the desired primary
key
• Transfer the disk block into a database buffer
in main memory
• Copy the salary from a variable (register) into
the database buffer
• Write the database buffer back to disk
(FLUSH)
Buffer vs. Secondary Storage
• Write buffer to secondary storage
– When buffer becomes full
– When an explicit flush command is given
• If failure occurs when data is in buffer
– If transaction is committed, flush the buffer
(REDO)
– If transaction is not committed, undo any effects
that already made it to disk (UNDO)
Example Transactions & Failure
• Checkpoint (buffers flushed) at tc, failure at tf
• Transactions 4 and 5 should be redone, 1
and 6 should be undone
Managing Buffer
• Dirty bit – set when the buffer has been
changed (i.e. buffer ≠ secondary storage)
• Pin count
– Initialize to 0
– Increment when buffer is requested
– Decrement when system indicates buffer is not
needed anymore (e.g. transaction commits)
– If pin count is 0, buffer is available for replacement
(write back if dirty)
Managing Buffer (continued)
• Steal Policy
– Buffer can be written to disk before transaction
commits
• Force Policy
– Buffer must be written to disk when transaction
commits
• Simplest = no steal, force
• Most common = steal, no-force
– Reduce buffer size; reuse page info in memory
Facilities needed for recovery
• Backup mechanism
– Periodic backups of entire database (timestamped) + log file
• Logging facilities
– Track transactions & database changes
• Checkpoint facility
– Force updates in progress to become permanent
• Recovery manager
– Restores system to consistent state after failure
Backup
• Recovery from catastrophic failure = choose
the latest consistent backup
• Backups generally kept on non-volatile
media, offsite
• Can be complete or incremental
Log File
• Information about transactions used to
undo/redo changes
• Transaction records:
– Id, type (start, insert, update, delete, abort,
commit)
– Item(s) affected
• Before value(s) + after value(s)
• Checkpoint records (later)
Sample Log File
Keeping Log File Safe
• Maintain multiple (2 or 3) separate copies of
file
• At least 1 backup copy on online fast directaccess storage device (e.g. additional
mounted hard-drive) for fast recovery
Checkpoints
• Points of synchronization between database
and log file (all in secondary storage)
• At each checkpoint
– Write all log records in main memory to secondary
storage
– Write modified blocks in database to secondary
storage
– Write a checkpoint record to the log file (all active
transactions identified)
• Recovery needed only back to last
checkpoint
Recovery Techniques
• After extensive damage (e.g. head crash)
– Restore last backup copy
– Reapply committed transactions using log (if log is
still available)
• In case of limited inconsistency
– Undo transactions that caused inconsistency
– Redo transactions as needed
– “Deferred update” or “immediate update”
Deferred Update
• Write ‘transaction start’ record to log
• Update log (but not DB) for each write
operation
• Write ‘transaction commit’ record to log
• Write log to disk
• Perform writes to DB according to log
Consequences of Deferred Update
• Aborted transaction
– No effort needed; just ignore log’s write entries
• Database failure
– Start at most recent checkpoint
– Log is already available on disk; redo write
operations according to log
– Transactions with both start & commit in log are
redone
– Writes redone in order written to log
Immediate Update
• Write transaction start record to log
• For each write operation, write the record to
the log first, then perform the write to the DB
buffers
• Write to disk when DB buffers are flushed
• When transaction commits, write commit
record to log
Consequences of Immediate Update
• Log always updated before DB (write-ahead
log protocol)
• If transaction aborts, use log information to
undo it
• In case of failure, any transaction without
commit record in log needs to be undone
• If there are both transaction start and
transaction commit records for a transaction,
that transaction needs to be redone