Database Management Systems CSE530a

1
Database Management Systems CSE530a
2
Transaction Management

• Recovery
• Design Methodology

3
TRANSACTION RECOVERY

• Types of failure for which a DBMS can provide
  damage control
• Transaction failure
• Logical error overflow, resource limit exceeded
• DB System error deadlock
• System crash loss of volatile storage
• Media failure

4
ACID Recovery

• Properties of a transaction
• Atomicity all or nothing
• Consistency complete transformation
• Isolation unseen
• Durability permanent upon completion
• Recovery algorithms have two parts
• Actions taken during normal transactions
• Actions taken after a failure

5
State Transitions
Partially Committed
Committed
Begin
Active
Abort
Abort
Failed
Aborted
6
DBMS Transaction System

Scheduler
Transaction Manager
Buffer Manager
Recovery Manager
File Manager
Access Manager
Db Schema Catalog
Systems Manager
7
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.

8
Data Management
Read
Input X
X
X x
Write
Input X
X
x X
Output? X
Transaction Address Space
Disk buffer
Nonvolatile storage
(variables pincount, dirty)
9
Buffer management

• Steal policy buffer manager writes a buffer to
  disk before a transaction commits (the buffer is
  unpinned)
• Force policy all pages updated by a transaction
  are immediately written to disk when the
  transaction commits
• Steal, no-force policy is the most common used by
  vendors, including DB2 and SQL Server. It is
  known as Algorithms for Recovery and Isolation
  Exploiting Semantics (ARIES). It is preferred
  because it
• Reduces the buffer space needed to store dirty
  pages by concurrent transactions
• Avoids rewrites of dirty pages to disk

10
Recovery Logs

• A log is a sequence of log records filling log
  blocks one at a time
• Typically include Start Transaction, Commit
  Transaction, Abort Transaction, Update Record
  (TransactionUD, data-itemID, OldValue, NewValue)
• Should be in stable storage, preferably
  far-removed from database
• Write Ahead Log (WAL) creates log entries before
  the database is modified

11
Log File
12
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
• 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
• Typically utilize UNDO and REDO operations

13
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.

14
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.
• In the reverse order to which they were written
  to the log

15
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
16
Other Recovery Considerations

• Archiving backup the complete database
• Incremental backups backup only changed data
• Nonquiescent Archiving Make log record at
  beginning and end of archiving

17
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
18
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

19
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

20
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?

21
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?

22
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

23
Design phases

• Conceptual
• Logical
• Physical

24
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!

25
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

26
Implementation

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

27
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.