Outline

• Types DB Failures
• Quiz
• Transactions
• Undo Logging

Introduction

• Before the break, we completed our discussion of how query plans are optimized and generated.
• Today, we start a new topic, how to handle system failures

An Overarching Goal

• We would like our DBMS to:
  • Protect data in the case of system failure.
  • Keep the database in a consistent state even if multiple operations are being carried out at the same time.

Possible Kind of Database Errors

• Erroneous Data Entry -- We can try to prevent by type checking.
• Media Failures -- We can try to prevent by using a RAID system or archiving data to tape
• Catastrophic failures -- For example, fires, rogue airplanes, etc. We can try to distribute the database (How Sun recovered 9/11).
• System failures -- These are things like power loss or software errors that cause a particular transaction to be lost.
• Handling system failures are what we are going to focus for the next several lectures.

Transactions

• In order to understand the recovery process from system failure, we need to understand a little about transactions.
• When doing a sequence of operations from a DMBS shell, each query or statement is roughly a transaction.
• In an embedded SQL/JDBC setting, a transaction will be a sequence of database commands up until either a COMMIT or ROLLBACK (abort) command is issued.
• The transaction manager in the DBMS has the job of making sure a transaction is executed correctly.

Jobs of the Transaction Manager

• The Transaction Manager:
  • Issues signals to the log manager so that necessary log records are written.
  • Ensures that concurrently executing transactions do not interfere with each other.

Correct Execution of Transactions

• We will use the term elements to indicate the objects a transaction can manipulate.
• For instance, an element might be a relation, disk block, or individual tuple.
• A database state is a set of values for each of its elements.
• A database is in a consistent state if all the values of the elements satisfy the key and value constraints as well as implicit constraints intended by the DB designer.
• The Correctness Principle is:
  If a transaction executes in the absence of any other transaction or system errors, and it starts with a database in a consistent state, then the database will be in a consistent state when the transaction ends.

Converse of the Correctness Principle

• The converse of the correctness principle is often modified into the following two points which motivate the use of transaction logging:
  1. A transaction should be atomic: All of its operations should be executed or none of them. If only some of a transaction operations are performed, it might leave the DB in an inconsistent state.
  2. Transactions that are executed simultaneously are likely to result in an inconsistent state unless steps are taken to control their interactions.

Quiz

Which of the following statements is true?

1. The main approach to enumerating physical plans we considered in class was bottom up.
2. Selinger Plan Optimization and Branch and Bound Plan optimization are the same thing.
3. Writing all the intermediate results of a query back to disk is called pipelining.

Primitive Operations of a Transaction

• To reason about transactions we abstract out some common transaction operations:
  • INPUT(X) - copy disk block of element `X` to memory. Abbreviation: `I(X)`.
  • READ(X,t) - copy `X` to the transactions local variable `t`. Abbreviation: `R(x,t)`.
  • WRITE(X,t) - copy the value of local variable `t` to database element `X` in memory. Abbreviation: `W(X,t)`.
  • OUTPUT(X) - copy the buffer containing `X` to disk. Abbreviation: `O(X)`.
• Below is a simple example transaction:

  I(A),  R(A,t), t:=t*2, W(A,t),  I(B), R(B,t), t:= t*2, W(B,t), O(A), O(B).
  

Undo Logging

• A log is a sequence of log records, each telling something about the transaction being done.
• The actions of several transactions can interleave.
• Logging occurs while the transaction is running.
• When a crash occurs, the log records will be consulted to figure out what the database was doing at the time of the crash. This will be used to attempt to repair the database.
• One common style of logging is called undo logging.
• If we are using only undo logging, to recover from a crash, we only undo some of the operations that have not committed by the time of the crash and this should restore DB to a consistent state.

Log Records

• What is in a log record?
  • `\langle START \ T\rangle` - indicates transaction `T` has begun.
  • `\langle COMMIT \ T\rangle` - indicates `T` has completed successfully and will change no more DB elements. Any changes to the database made by `T` should appear on disk. Cannot be sure at this point all relevant memory buffers have been flushed.
  • `\langle ABORT \ T\rangle` - Transaction `T` could not be completed successfully. If `T` aborts, the transaction manager must ensure no changes done by `T` should ever appear on disk.
  • Update records of the form `\langle T, X, v\rangle` which says `T` changed the value of `X` from `v` to something new.
• Log records are recorded when `W(X,v)`'s occur not when `O(X)`'s occur.
• The table below shows how the timing of log records writes might interleave with the operations of the example transaction we had before:

  Transaction Op	Value t	Memory Value A	Disk Value A	Memory Value B	Disk Value B	Log Records
  						`\langle START \ T\rangle`
  I(A)		4	4		6
  R(A,t)	4	4	4		6
  t:=t*2	8	4	4		6
  W(A,t)	8	8	4		6	`\langle T, A, 4\rangle`
  I(B)	4	4	4	6	6
  R(B,t)	6	8	4	6	6
  t:= t*2	12	8	4	6	6
  W(B,t)	12	8	4	12	6	`\langle T, B, 6\rangle`
  FLUSH LOG	12	8	4	6	6
  O(A)	12	8	8	12	6
  O(B)	12	8	8	12	12
  						`\langle COMMIT \ T\rangle`
  FLUSH LOG

• In the above, FLUSH LOG says, write to disk any changes of the log not yet on disk.

The Undo-Logging Rules

• If undo-logging is to suffice for the TM to recover from a system failure two things are necessary:
  • (U1) If `T` modifies `X`, then a log of the form `langle T,X,v rangle` must be written before the new value of `X` is written to disk.
  • (U2) If a transaction commits, then its COMMIT log record must be written to disk only after all database elements changed by the transaction have been written to disk, but as soon thereafter as possible.
• So the order a transaction must write stuff to disk is:
  1. log record
  2. changed DB elements
  3. COMMIT log record.
• Since typically we write whole disk blocks at a time, if we are not careful, it is completely possible for two transactions to be modifying the same block.
• Before, we write a block to disk each transaction which has midified it must have its `langle T,X,v rangle` records written.
• This can be difficult to arrange so often if undo locking is used, only one transaction is allowed to access a block at a time. This can be ensured using locking.

Recovering using Undo Logging

• Suppose now a system failure occurs.
• It is the job of the Recovery Manager (RMAN) to get the DB back to some consistent state.
• We will first consider a simple way to do it by looking at the whole log file.
• We will later consider methods based on checkpointing.
• The first step is to divide the transaction into committed and uncommitted transactions.
• If we see a `langle COMMIT\ T\rangle` we know by rule `U2` that the changed values of `T` were previously written to disk.
• If we see a `langle START\ T\rangle` without a `langle COMMIT\ T\rangle` then we know some operations done by `T` might not have been written to disk. So `T` must be undone. We look for records of the form `langle T, X, v\rangle`'s and replace the new value of `X` with `v`.
• After we are done making these changes we write an `langle ABORT\ T\rangle` record. When finished processing the log file, we can resume operations.
• If the DB fails while we are doing recovery, we no longer have to consider the transaction for which we successfully wrote an `langle ABORT\ T\rangle`.