Database Management Systems CSE530a

1
Database Management Systems CSE530a
2
Transaction Management

• Recovery
• Design Methodology

3
Recovery facilities

• Backup mechanism
• Makes periodic backup copies of the database
• Logging facilities
• Keeps track of the current state of transaction
  and database changes
• A checkpoint facility
• Enables updates to the database that are in
  progress to be made permanent.
• A recovery manager
• Allows the system to restore the database to a
  consistent state following a failure.

4
Logging

• Undo Logging (rollback) recover by removing
  transactions until database restored to a
  consistent state (doesnt record NewValue in
  Update Log Record)
• Redo Logging (rollforward) recover by repeating
  transactions until database restored to last
  valid consistent state
• Checkpointing a point of synchronization
  between database and log file. All buffers
  (logs, modified blocks, checkpoint record,
  indicating status of transactions) are Forced to
  stable storage

T1 T2 T3 T4 T5 T6
t0
tf
5
Log File
6
Recovery Techniques

• Depends on the extent of damage
• If extensive, restore the last backup copy and
  reapply update operations of committed
  transactions using the log file.
• If not extensive but in an inconsistent state,
  then undo the changes that caused the
  inconsistency using before- and after-images in
  the log file with one of two methods
• Deferred update
• Immediate update
• Typically utilize UNDO and REDO operations
• ARIES recovery method enhances log records by
  having a log sequence number (LSN) to identify
  log record and LSN assigned to database pages, a
  dirty page table to minimize unnecessary redos,
  and fuzzy checkpointing

7
Recovery MethodsDeferred update Immediate update

• Updates are not written to the database until
  after a transaction has reached its commit point.
• In case of failure before commit,
• UNDO is unnecessary, REDO required
• Use log file to do following
• When a transaction starts, write a transaction
  start record to the log
• When any write operation is performed, write a
  log record containing all the log data specified
  previously. Do not write to the database buffers
  or the database
• When a transaction is about to commit, write a
  transaction commit log record, write all the log
  records for the transaction to disk, and then
  commit the transaction
• If a transaction aborts, write a transaction
  abort log record, ignore the log records for the
  transaction and do not perform the writes.

• Using this approach, updates are applied to the
  database as they occur without waiting to reach
  the commit point.
• In case of failure
• Both UNDO and REDO are necessary
• Use log file to do following
• When a transaction starts, write a transaction
  start record to the log
• When any write operation is performed, write
  record containing the necessary data to the log
  file
• Once the log record is written, write the update
  to the database buffers.
• The updates to the database itself are written
  when the buffers are next flushed to secondary
  storage
• When the transaction commits, write a transaction
  commit record to log.

8
Recovery steps after failureDeferred
update Immediate update

• Starting at the last entry in the log file, go
  back to the most recent checkpoint record
• Any transaction with transaction start and
  transaction commit log records should be redone.
• Use the after-image log records for the
  transaction,
• In the order in which they were written to the
  log
• For any transactions with transaction start and
  transaction abort log records, we do nothing
  since no actual writing was done to the database.
• No transactions need to be undone.

• For any transactions for which both a transaction
  start and transaction commit record appear in the
  log, we redo using the log records to write the
  after-image of updated fields.
• For any transactions for which the log contains a
  transaction start record but not a transaction
  commit record, we need to undo that transaction.
• The log records are used to write the
  before-image of the affected fields, and restore
  the database to its state prior to the
  transactions start.
• Applied in the reverse order to which they were
  written to the log

9
Other Recovery Considerations

• Shadow Paging write records of transactions to
  an unmodified directory
• Archiving backup the complete database
• Incremental backups backup only changed data
• Nonquiescent Archiving Make log record at
  beginning and end of archiving

10
Database System Development Lifecycle

• Many models from which to select
• Typically a cyclical, interactive process

Mission Statement
System Definition
Requirements Analysis
Database Design
Vendor Selection
Maintain
Application Design
Implementation
Load
Test
11
Database Planning Design

• Database Planning
• Mission statement and objectives
• What do you need it to do? (in one paragraph)
• How long should it last?
• What infrastructure (budget, expertise, hardware)
  is needed to support it?
• Database Scope
• Create broad definition of user views
• Consider all potential users
• Present
• Future?
• Consider need for scalability

12
Requirements Collection Analysis

• Perhaps the most important step
• Many methodologies
• Always document
• Written vs visual
• Fact-finding techniques
• Identify users (person or position)
• Identify how data will be used (and importance to
  company)
• Identify all data items to be collected
• Identify authorizations and privileges
• Identify urgency of implementation, consider
  phases

13
Requirements Collection Analysis

• Centralized approach
• Uses a global data model
• Large variations in users induces complexity
• View integration approach
• Uses a local data model
• Later merged to a global data model
• Easier to implement in phases
• Increases risk of redundancy and gaps
• Mixed approach?

14
Database Design

• Differing strategies
• Bottom-up
• Identify the attributes and functional
  dependencies
• Normalize
• Easier for existing data and simple processes
• Top-down
• Identify the entities in the business and their
  relationships
• Translate to the relational model
• Logic-based
• Misconceptions commonly create risk
• Inside-out
• Identify only the major entities and then build
  from there
• A variation of top-down making it easier to begin
• Mixed?

15
Data modeling

• Create a common understanding among everyone
  involved
• Mimic questions and needs in the model
• Semantically analyze values
• Map data samples
• A data model should be
• Structurally valid
• Simple
• Expressible
• Extensible
• Diagrammable
• also nonredundant and shareable

16
Design phases

• Conceptual
• Logical
• Physical

17
Vendor selection

• Create a table of dbms packages with itemized
• Capabilities
• Limitations
• Licensing variations
• Pricing
• Utilize a decision tree?
• Reduce to 2 or 3 products
• Test
• Test
• Test
• Negotiate?
• Buy, hire and implement a go/no go point!

18
Application Design

• Database is one component
• Identify and describe transactions
• User interface design
• utilize RAD tools, paper prototyping
• test and obtain feedback
• Prototyping
• Requirements-based
• Evolutionary-based

19
Implementation

• Create the
• Schema
• Host language
• Embedded SQL
• User views
• Manage data discordance
• Assign privileges

20
The Final Stages

• Data conversion and loading
• Testing
• Use criteria and thresholds
• Modify when needed
• Sometimes start over
• Maintenance
• Monitor performance
• Scale when needed
• Typically as expensive as the creation phase
• Software maintenance often accounts for 50-80
  of software lifecycle costs for legacy systems
• Krishnan, MS. A Decision Model for Software
  Maintenance. Information Systems Research.
  200415(4)396-412.