TRANSACTIONS RECOVERY

1
TRANSACTIONS RECOVERY
Pamela Quick
2
Requirements for Database Consistency
Recovery
System failures, either of hardware or software,
must not result in an inconsistent database. A
transaction must execute in its entirety or not
at all.
3
Transaction as a Logical Unit of Work

• Most DBMS manage all data modification commands
  (i.e single DB operations) as transactions
  
• More efficient to logically group database
  operations into user-defined (i.e. programmer)
  transactions
• An application program may correspond to a single
  transaction or a group of transactions
• Most database programming languages indicate the
  boundaries of a transaction with

BEGIN TRANsact ltstatementsgt COMMIT TRANsact
4
An Example Transaction
SHIPMENT
PART
sno pno qty s1 p1 300 s1 p2 200 s1 p3 400 s2 p1 30
0 s2 p2 400 s3 p2 200 s4 p2 200
pno pname colour weight city totqty p1 nut red 12
London 600 p2 bolt green 17 Paris 1000 p3 screw bl
ue 17 Rome 400 p4 screw red 14 London p5 cam blue
12 Paris p6 cog red 19 London
S5 p1 1000

• / Create a new shipment /
• begin transaction new_shipment
• whenever db_access_error go to undo
• insert into SHIPMENT values ("s5","p1", "1000")
• update PART set (totqtyPART.totqty 1000)
  where PART.pno"p1"
• go to end
• undo rollback transaction
• end commit transaction

5
Execution of Transaction

• To the user the transaction appears as a single
  atomic operation but in fact there are two
  (INSERT and UPDATE)
• The INSERT operation 'violates' the general rule
  that
• totqtySUM (SHIPMENT.qty)
• The UPDATE operation restores the database
  integrity
• The sequence of database operations and other
  program statements taken together as a logical
  unit (transaction) transforms a consistent state
  of the database into another consistent state,
  without necessarily preserving consistency at all
  intermediate points

6
Transaction as a Recovery Unit

• If an error or hardware/software crash occurs
  between the 'begin' and 'commit' statements, the
  database will be inconsistent
• A DBMS ensures that if a transaction executes
  some updates and then a failure occurs before the
  transaction reaches normal termination, then
  those updates are undone.
• The statements COMMIT and ROLLBACK (or their
  equivalent) ensure

Transaction Atomicity A transaction must execute
in its entirety or not at all

• Recovery is effected by using a log of
  transactions and applying the techniques of
  write-ahead log and checkpointing

7
Commit and Rollback
Programmer Defined
Automatically Handled

• when grouping many db operations into
  transactions
• to handle specific system errors or violation of
  user defined integrity constraints
• when a program represents more than one
  transaction

• when no logical transaction is defined

• A commit or rollback operation establishes the
  boundary between two consecutive transactions -
  the database is in a consistent state
• There can be no nesting of transactions

8
The Need for Recovery Control

• When a transaction is submitted for execution to
  a DBMS the system is responsible for making sure
  that either
• all operations in the transaction are
  successfully completed and their effect is
  permanently recorded in the database
• the transaction has no effect whatsoever on the
  database or on other transactions
• A transaction T fails if some of its operations
  are applied to the database while others are not

9
Types of Failures

• System crash due to hardware or software
  errors, resulting in loss of main memory
• Application software errors, such as logical
  errors in the program accessing the db, which
  cause one or more transactions to fail
• Natural physical disasters such as flood. Fire,
  earthquake or power failures
• Carelessness or unintentional destruction of data
  or facilities by operators or users
• Media failures such as head crash or unreadable
  media, resulting in loss of part of secondary
  storage
• Sabotage or intentional corruption or destruction
  of data,hardware or software facilities

10
Read and Write Operations of a Transaction

• read_item(X) reads a database item named X into
  a program variable also named X. Execution of
  the command includes the following steps
• find the address of the disk block that contains
  item X
• copy that disk block into a buffer in the main
  memory
• copy item X from the buffer to the program
  variable named X
• write_item(X) writes the value of program
  variable X into the database item named X.
  Execution of the command includes the following
  steps
• find the address of the disk block that contains
  item X
• copy that disk block into a buffer in the main
  memory
• copy item X from the program variable named X
  into its current location in the buffer
• store the updated block in the buffer back to
  disk (this step updates the database on disk)

11
Transaction States

• For recovery purposes the system needs to keep
  track of when a transaction starts, terminates
  and commits.
• BEGIN_TRANSACTION marks the beginning of a
  transaction execution.
• READ or WRITE specify read or write operations
  on the database items that are executed as part
  of a transaction
• END_TRANSACTION specifies that the above named
  two operations have ended and marks the end limit
  of transaction execution
• COMMIT_TRANSACTION signals a successful end of
  the transaction. Any updates executed by the
  transaction can be safely committed to the
  database and will not be undone.

12
Additional Operations

• ROLLBACK (or ABORT) signals that the transaction
  has ended unsuccessfully. Any changes that the
  transaction may have applied to the database must
  be undone
• UNDO similar to ROLLBACK but it applies to a
  single operation rather than to a whole
  transaction
• REDO/ ROLLFORWARD specifies that certain
  transaction operations must be redone to ensure
  that all the operations of a committed
  transaction have been applied successfully to the
  database

13
Recovery facilities

• A DBMS should provide the following to assist in
  recovery

• A backup mechanism, which makes periodic copies
  of the database
• Logging facilities, which keep track of the
  current state of transactions and database
  changes
• A checkpoint facility, which enables updates to
  the db that are in progress to be made permanent
• A recovery manager, which allows the system to
  restore the db to a consistent sate following
  failure

14
System Log

• sometimes called after image or redo-log
• The system maintains a log in order to enable
  recovery from transaction failures. It keeps
  track of all transaction operations affecting the
  values of database items.
• The log is kept on disk so that it is not
  affected by failures except for disk and
  catastrophic failures. The only protection
  against those kind of failures is regular back-up.

15
Entries in the System Log

• For every transaction a unique transaction-id is
  generated by the system.
• start_transaction, transaction-id the
  starting of execution of the transaction
  identified by transaction-id
• write_item, transaction-id, X, old_value,
  new_value the transaction identified by
  transaction-id changes the value of database
  item X from old_value to new_value
• read_item, transaction-id, X ( optional in
  some protocols) the transaction identified by
  transaction-id reads the value of database item
  X.
• commit, transaction-id the transaction
  identified by transaction-id has completed all
  accesses to the database successfully and its
  effect can be recorded permanently (committed)

16
Commit Points of a Transaction

• A transaction reaches its commit point when all
  operations accessing the database are completed
  and the result has been recorded in the log. It
  then writes a commit, transaction-id.
• If a system failure occurs, then the search of
  the log will reveal the transactions that have
  written a start_transaction, transaction-id, a
  commit, transaction-id into the log, but have
  not recorded a write_item, transaction-id, X,
  old_value, new_value into the log. Only these
  transactions are considered in the recovery
  process and their effect on the database can be
  redone from log entries.

17
Checkpoints in the System Log

• CheckpointThe point of synchronisation between
  the database and the transaction log file. All
  buffers are force-written to secondary storage.
• A checkpoint record is written periodically
  into the log when the system writes out to the
  database on disk the effect of all WRITE
  operations of committed transactions.
• All transactions whose commit, transaction-id
  entries can be found in the system log will not
  require their WRITE operations to be redone in
  the case of a system crash.
• Limits the amount of searching and processing
  that is required on the log file

18
Actions Constituting a Checkpoint

• Taking a checkpoint consists of the following
  actions
• temporary suspension of transaction execution
• forced writing of all updated database blocks in
  main memory buffers to disk
• writing a checkpoint record to the log and
  force writing the log to disk
• resuming of transaction execution

19
Recovery from Transaction Failures

• The database s restored to some state from the
  past so that a correct state -close to the time
  of failure- can be reconstructed from the past
  state.
• (a) if damage is due to catastrophic failure,
  then a past copy of the database that was dumped
  to archival storage is restored. It reconstructs
  a more current state by redoing committed
  transaction operations from the log up to the
  time of failure.
• (b) when the database has become inconsistent due
  to noncatastrophic failure the changes that
  caused the inconsistency are reversed by undoing
  the operations (it may also be necessary to redo
  legitimate changes which were lost). No need for
  a complete archival copy of the database. The
  entries kept in the system log are consulted
  during recovery.

20
Recovery Techniques

• Deferred Update there is no actual update of the
  database until after a transaction reaches its
  commit point and then updates are recorded in the
  database.
• Immediate Update the database may be updated by
  some operations of a transaction before it
  reaches its commit point.

21
Deferred Update Protocol

• (1) A transaction cannot change the database
  until it reaches its commit point.
• (2) A transaction does not reach its commit
  point until all its update operations are
  recorded in the log and the log is force written
  to disk.

22
Write-Ahead Log Protocol(for Immediate Update)

• (1) A transaction cannot update the physical
  database until all UNDO-type log records for that
  transaction -up to that point in time- have been
  force written to disk.
• (2) A transaction is not allowed to complete the
  processing of commit until all REDO-type and
  UNDO-type log records for that transaction have
  been force written to disk.

23
Transaction Rollback

• If a transaction -for whatever reason- fails
  after updating the database it may be necessary
  to roll back the transaction. Any data item
  values that have been changed by the transaction
  must be returned to their old values. The log
  entries are used to recover the old values of
  data items that must be rolled back.

24
Shadow Paging an alternative to log-based
Recovery Technique

• The scheme uses a number of fixed-size disk
  pages, say n, for recovery purposes. A page table
  with n entries is constructed where the ith page
  table entry points to the ith database page on
  disk.
• When a transaction begins executing, the current
  page table is copied into a shadow page table
  which is then saved and during transaction
  execution it is never modified.
• To recover from a failure, the state of the
  database before transaction execution is
  available through the shadow page table and that
  state is recovered by reinstating the shadow page
  table to become the current page table once more.
  
• Advantages overhead of log file maintenance
  removed, faster recovery (no undo/redo)
• Disadvantages data fragmentation requiring
  garbage collection

25
Two-phase Commit Protocol(for Multidatabase
transactions)

• PHASE 1 When all participating database signal
  the global recovery manager that the part of the
  multidatabase transaction involving each has
  concluded then the manager sends a "prepare for
  commit" signal. Each participating database will
  force write all log records to disk and send a
  "ready to commit" message. If force writing fails
  OR local transaction cannot commit then the
  database sends a "cannot commit" message.If
  manager receives no message then a "cannot
  commit" is assumed.
• PHASE 2 If all databases reply "ready to commit"
  then manager sends "commit" message to all
  databases.Each database writes a commit entry
  for the transaction in the log and updates
  permanently the database.If there is one "cannot
  commit" then manager sends "roll back" message to
  all participating databases.
• Because all local effects of the transaction have
  been recorded in the logs of the databases
  recovery from failure is possible.

26
Transactions and recovery - summary

• It is the role of the recovery manager to
  guarantee two of the ACID properties
• Atomicity Durability
• On recovery from failure either all the effects
  of a given transaction are permanently recorded
  in db or none at all
• The situation is complicated by the fact that db
  writing is not an atomic action and it is
  possible for a transaction to have committed but
  for its effects not to have yet permanently
  reached the db
• Use UNDO if crash before transaction not
  committed
• Use REDO if crash after commit but before
  permanently written to db