Outline

• Types DB Failures
• Quiz
• Transactions
• Undo Logging

Introduction

• We completed our discussion of how query plans are optimized and generated.
• Today, we continue a new topic, how to handle system failures

An Overarching Goal

• Recall, 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.

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 need to 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)	8	8	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	12	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.

Quiz

Which of the following statements is true?

1. The number of tuples produced by a natural join is at least linear in the size of the smaller join relation.
2. To find out the physical plan for a query `q`, in sqlite you can type: ANALYZE `q`.
3. Pipelining is a technique whereby we pass the output of the sub-query computed so far to the next stage in the query evaluation.

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 modified a block must have its `langle T,X,v rangle` log 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 transactions 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`.

Checkpointing

• So far the recovery techniques we've considered require that the entire log file be looked at.
• Even if a transaction has written a COMMIT to the log, one might still need to look at its operations during a recovery because several other transaction might be running at the same time.
• To simplify the issue we can checkpoint the log periodically.
• This involves doing the following:
  1. Stop accepting new transactions
  2. Wait until all current transactions commit or abort and have written the COMMIT or ABORT to the log.
  3. Flush the log to disk.
  4. Write a log record `langle CKPT rangle` and flush the log again.
  5. Resume accepting transactions.
• If checkpoints are used then there is no need to look in the log file prior to the last checkpoint.

Nonquiescent Checkpointing

• The problem with checkpointing is that we effectively stop the system until all current operations have committed or aborted.
• Nonquiescent checkpointing is a technique that avoids this bottleneck. The steps to do this are to:
  1. Write a record `langle START\ CKPT(T_1,...,T_k) rangle` and flush log. Here `T_1,..., T_k` are the active transactions.
  2. Wait until all of `T_1, ..., T_k` commit or abort, but allow new transactions to start.
  3. When all of `T_1,..., T_k` have written COMMIT or ABORT then write `langle END\ CKPT rangle` to the log.
• If this kind of checkpoint is being used and a crash occurs, then we look backwards through the log for the first `langle START\ CKPT(T_1,...,T_k) rangle` or `langle END\ CKPT rangle`.
• If we see an `langle END\ CKPT rangle`, we know we only have to consider transactions that started after the `langle START\ CKPT(T_1,...,T_k) rangle`.
• If we see a `langle START\ CKPT(T_1,...,T_k) rangle` but no `langle END\ CKPT rangle`, then we need to only consider after the transactions `T_1, ..., T_k` began.