Docstoc

lecture14

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
                   Transactions
• 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
                    Transactions
• 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
     database
                                  •   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
     atomicity…

    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
  Read(A)
                                   Consistency - money isn’t
  A = A - 50                         lost or gained
  Write(A)                         Isolation - other queries
                     transaction
  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
                                 lecture)


   Transactions and Recovery
    COMMIT and ROLLBACK
• 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
                        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
T1

            T2

       T3

                                 T4

                                      T5



                   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
 T1
              T2
         T3
                                  T4
                                         T5
                       Checkpoint                           Failure


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

      Transactions and Recovery
          Transaction Recovery
 T1
              T2
         T3
                                  T4
                                         T5
                       Checkpoint                       Failure


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

      Transactions and Recovery
          Transaction Recovery
 T1
              T2
         T3
                                  T4
                                       T5
                       Checkpoint                       Failure


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

      Transactions and Recovery
          Transaction Recovery
 T1
              T2
         T3
                                  T4
                                       T5
                       Checkpoint                    Failure


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

      Transactions and Recovery
          Transaction Recovery
 T1
              T2
         T3
                                  T4
                                       T5
                       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
• 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
    time

   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
                    Concurrency
• 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
                                     periods


    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
                    COMMIT

     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
ROLLBACK
                    COMMIT

     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
Read(Y)
Y = Y + 5
Write(Y)
     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

				
DOCUMENT INFO