Document Sample
lecture14 Powered By Docstoc
					Transactions and Recovery

  Database Systems Lecture 14
       Natasha Alechina
                In This Lecture
• Transactions
• Recovery
  • System and Media Failures
• Concurrency
  • Concurrency problems
• For more information
  • Connolly and Begg chapter 20
  • Ullman and Widom 8.6

   Transactions and Recovery
• A transaction is an action, or a series of
  actions, carried out by a single user or an
  application program, which reads or updates
  the contents of a database.

   Transactions and Recovery
• A transaction is a            • Transactions are the
  ‘logical unit of work’          unit of recovery,
  on a database                   consistency, and
   • Each transaction does        integrity as well
     something in the           • ACID properties
                                  •   Atomicity
   • No part of it alone
     achieves anything of         •   Consistency
     use or interest              •   Isolation
                                  •   Durability

    Transactions and Recovery
  Atomicity and Consistency
• Atomicity                    • Consistency
  • Transactions are             • Transactions take the
    atomic – they don’t            database from one
    have parts                     consistent state into
    (conceptually)                 another
  • can’t be executed            • In the middle of a
    partially; it should not       transaction the
    be detectable that             database might not
    they interleave with           be consistent
    another transaction

   Transactions and Recovery
      Isolation and Durability
• Isolation                     • Durability
   • The effects of a              • Once a transaction
     transaction are not             has completed, its
     visible to other                changes are made
     transactions until it           permanent
     has completed                 • Even if the system
   • From outside the                crashes, the effects of
     transaction has either          a transaction must
     happened or not                 remain in place
   • To me this actually
     sounds like a
     consequence of

    Transactions and Recovery
       Example of transaction
• Transfer £50 from                Atomicity - shouldn’t take
  account A to account B             money from A without
                                     giving it to B
                                   Consistency - money isn’t
  A = A - 50                         lost or gained
  Write(A)                         Isolation - other queries
  Read(B)                            shouldn’t see A or B
  B = B+50                           change until completion
  Write(B)                         Durability - the money
                                     does not go back to A

     Transactions and Recovery
   The Transaction Manager
• The transaction              • Locks or timestamps
  manager enforces               are used to ensure
                                 consistency and
  the ACID properties            isolation for
  • It schedules the             concurrent
    operations of                transactions (next
    transactions                 lectures)
  • COMMIT and                 • A log is kept to ensure
    ROLLBACK are used            durability in the event
    to ensure atomicity          of system failure (this

   Transactions and Recovery
• COMMIT signals the           • ROLLBACK signals
  successful end of a            the unsuccessful end
  transaction                    of a transaction
  • Any changes made by          • Any changes made by
    the transaction should         the transaction should
    be saved                       be undone
  • These changes are            • It is now as if the
    now visible to other           transaction never
    transactions                   existed

   Transactions and Recovery
• Transactions should          • Prevention is better
  be durable, but we             than cure
  cannot prevent all             • Reliable OS
  sorts of failures:             • Security
  •   System crashes             • UPS and surge
  •   Power failures               protectors
  •   Disk crashes               • RAID arrays
  •   User mistakes            • Can’t protect against
  •   Sabotage                   everything though
  •   Natural disasters

   Transactions and Recovery
          The Transaction Log
• The transaction log           • The log is stored on
  records the details of          disk, not in memory
  all transactions                • If the system crashes
   • Any changes the                it is preserved
     transaction makes to       • Write ahead log rule
     the database
                                  • The entry in the log
   • How to undo these              must be made before
     changes                        COMMIT processing
   • When transactions              can complete
     complete and how

    Transactions and Recovery
               System Failures
• A system failure             • At various times a
  means all running              DBMS takes a
  transactions are               checkpoint
  affected                       • All committed
  • Software crashes               transactions are
  • Power failures                 written to disk
                                 • A record is made (on
• The physical media               disk) of the
  (disks) are not                  transactions that are
  damaged                          currently running

   Transactions and Recovery
         Types of Transactions





                   Last Checkpoint         System Failure

     Transactions and Recovery
             System Recovery
• Any transaction that         • Transactions of type
  was running at the             T1 need no recovery
  time of failure needs        • Transactions of type
  to be undone and               T3 or T5 need to be
  restarted                      undone and
• Any transactions               restarted
  that committed since         • Transactions of type
  the last checkpoint            T2 or T4 need to be
  need to be redone              redone

   Transactions and Recovery
        Transaction Recovery
UNDO and REDO: lists of transactions

UNDO = all transactions running at the last checkpoint
REDO = empty

For each entry in the log, starting at the last checkpoint
  If a BEGIN TRANSACTION entry is found for T
       Add T to UNDO
  If a COMMIT entry is found for T
       Move T from UNDO to REDO

    Transactions and Recovery
          Transaction Recovery
                       Checkpoint                           Failure

UNDO: T2, T3
                                  Last Checkpoint
                                  Active transactions: T2, T3

      Transactions and Recovery
          Transaction Recovery
                       Checkpoint                       Failure

UNDO: T2, T3, T4
                                       T4 Begins
                                       Add T4 to UNDO

      Transactions and Recovery
          Transaction Recovery
                       Checkpoint                       Failure

UNDO: T2, T3, T4, T5
                                            T5 begins
                                            Add T5 to UNDO

      Transactions and Recovery
          Transaction Recovery
                       Checkpoint                    Failure

UNDO: T3, T4, T5
                                            T2 Commits
                                            Move T2 to REDO

      Transactions and Recovery
          Transaction Recovery
                       Checkpoint               Failure

UNDO: T3, T5
                                            T4 Commits
REDO: T2, T4
                                            Move T4 to REDO

      Transactions and Recovery
   Forwards and Backwards
• Backwards recovery           • Forwards recovery
  • We need to undo              • Some transactions
    some transactions              need to be redone
  • Working backwards            • Working forwards
    through the log we             through the log we
    undo any operation by          redo any operation by
    a transaction on the           a transaction on the
    UNDO list                      REDO list
  • This returns the             • This brings the
    database to a                  database up to date
    consistent state

   Transactions and Recovery
                 Media Failures
• System failures are          • Media failures (disk
  not too severe                 crashes etc) are
  • Only information since       more serious
    the last checkpoint is        • The data stored to
    affected                        disk is damaged
  • This can be recovered         • The transaction log
    from the transaction            itself may be
    log                             damaged

   Transactions and Recovery
• Backups are needed           • Backups frequency
  to recover from                • Frequent enough that
  media failure                    little information is
  • The transaction log            lost
    and entire contents of       • Not so frequent as to
    the database is                cause problems
    written to secondary         • Every day (night) is
    storage (often tape)           common
  • Time consuming, and        • Backup storage
    often requires down

   Transactions and Recovery
Recovery from Media Failure
• Restore the database         • If the transaction log
  from the last backup           is damaged you can’t
• Use the transaction            do step 2
  log to redo any                 • Store the log on a
  changes made since                separate physical
                                    device to the
  the last backup                   database
                                  • The risk of losing both
                                    is then reduced

   Transactions and Recovery
• Large databases are           • If we don’t allow for
  used by many people             concurrency then
  • Many transactions to be       transactions are run
    run on the database           sequentially
  • It is desirable to let         • Have a queue of
    them run at the same             transactions
    time as each other             • Long transactions (eg
  • Need to preserve                 backups) will make
    isolation                        others wait for long

    Transactions and Recovery
      Concurrency Problems
• In order to run              • This leads to several
  transactions                   sorts of problems
  concurrently we                 • Lost updates
  interleave their                • Uncommitted updates
  operations                      • Incorrect analysis
• Each transaction             • All arise because
  gets a share of the            isolation is broken
  computing time

   Transactions and Recovery
                      Lost Update

T1                  T2           • T1 and T2 read X,
                                   both modify it, then
Read(X)                            both write it out
X = X - 5                          • The net effect of T1
                                     and T2 should be no
                    Read(X)          change on X
                    X = X + 5      • Only T2’s change is
Write(X)                             seen, however, so the
                    Write(X)         final value of X has
COMMIT                               increased by 5

     Transactions and Recovery
          Uncommitted Update

T1                  T2           • T2 sees the change
                                   to X made by T1, but
Read(X)                            T1 is rolled back
X = X - 5                          • The change made by
                                     T1 is undone on
Write(X)                             rollback
                    Read(X)        • It should be as if that
                    X = X + 5        change never
                    Write(X)         happened

     Transactions and Recovery
          Inconsistent analysis

T1                  T2           • T1 doesn’t change
                                   the sum of X and Y,
Read(X)                            but T2 sees a change
X = X - 5                          • T1 consists of two
                                     parts – take 5 from X
Write(X)                             and then add 5 to Y
                    Read(X)        • T2 sees the effect of
                    Read(Y)          the first, but not the
                    Sum = X+Y        second
Y = Y + 5
     Transactions and Recovery
                   Next Lecture
• Concurrency
  • Locks and resources
  • Deadlock
• Serialisability
  • Schedules of transactions
  • Serial & serialisable schedules
• For more information
  • Connolly and Begg chapter 20
  • Ullman and Widom 8.6

   Transactions and Recovery