Advanced Database System

1
Advanced Database System

• CS 641
• Lecture 14

2
Control access to data

• Data must be protected in the face of a system
  failure
• Data must not be corrupted simply because several
  error-free queries or database modifications are
  being done at once.

3
Failure modes

• Erroneous data entry
• Hard or even impossible to detect, using
  constraints, triggers to solve
• Media failure
• Failure of disks, using RAID, archive or
  redundant copies to solve
• Catastrophic failure
• The database is completely destroyed, using
  archiving and redundant, distributed copies
• System failure
• Power loss or software error, using logging to
  solve the problem

4
Transactions and transaction managers

• Transaction The unit of execution of database
  operations. It must be execute atomically
  (all-or-nothing)
• Transaction manager perform the functions
• Issuing signals to the log manager so that
  necessary information in the form of log
  records can be stored on the log
• Assuring that concurrently executing transactions
  do not interfere with each other in ways that
  introduce errors.

5
The log manager and transaction manager
6
Concepts in transactions

• Elements
• Relations, or their object-oriented equivalent
  the extent of a class
• Disk blocks or pages
• Individual tuples of a relation, or their
  object-oriented equivalent objects
• State a value of each element
• Consistent satisfy all constraints of the schema
• Inconsistent

7
The correctness principle of transactions

• If a transaction executes in the absence of any
  other transactions or system errors, and it
  starts with the database in consistent state,
  then the database is also in a consistent state
  when the transaction ends.

8
Three addresses

• The space of disk blocks holding the database
  elements
• The virtual or main memory address space that is
  managed by the buffer manager
• The local address space of the transaction

9
Primitive operations of transactions

• INPUT(X) issued by the buffer manager
• Copy the disk block containing database element X
  to a memory buffer
• READ(X,t ) issued by the transaction
• Copy X to the transactions local variable t
• WRITE(X,t) issued by the transaction
• Copy the value of t to X in a memory buffer
• OUTPUT(t) issued by the buffer manager
• Copy X from its buffer to disk

10
Example 17.1

• A database has two elements A and B, with the
  constraint that they must be equal in all
  consistent states
• Transaction T
• A A2
• B B2
• Execution
• READ(A,t) tt2 WRITE(A,t)
• READ(B,t) tt2 WRITE(B,t)

11
Steps of the transaction
12
Exercises

• 17.1.1 a)
• 17.1.2 a)

13
Log

• A sequence of log records, used for
  reconstruction in system crashes
• Assure that transactions are atomic
• Two types
• Undo logging restore the database to what
  existed prior to a transaction
• Redo logging

14
Log records

• Log manager use log record to record each
  important event
• One block of the log at a time is filled with log
  records.
• These blocks are initially created in main
  memory, and are written to disk as soon as they
  are full

15
Types of log records

• ltSTART Tgt indicates that the transaction T has
  begun
• ltCOMMIT Tgt T has complete successfully and will
  make no more changes to database elements
• ltABORT Tgt T has not complete successfully. If T
  aborts, no changes it made can have been copied
  to disk.
• Update record ltT, X, vgt T has changed database
  element X, and its former value was v.

16
The Undo-Logging rules

• U1 if T modifies X, the log record ltT,X,vgt must
  be written to disk 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

17
The order of writing

• Materials associated with one transaction must be
  written to disk in the following order
• The log records indicating changed database
  elements
• The changed database elements themselves
• The COMMIT log record
• Flush-log a command used to force buffer manager
  to write log records to disk. (FLUSH LOG)

18
Example 17.2
19
Recovery using undo logging

• Recovery manager will use the log to restore the
  database state to some consistent state. It will
  perform
• Divide the transactions into committed and
  uncommitted.
• A incomplete transaction needs to be undone.
• Whatever changes it made must be reset to the
  previous values.
• It is possible multiple incomplete transactions
  exist

20
Recovery using undo logging (Cont.)

• Scan the log from the end, and remembers all
  those transactions T for which it has seen a
  ltCOMMIT Tgt or ltABORT Tgt
• If it sees a ltT,X,v)
• If T is a transaction whose COMMIT record has
  been seen, do nothing
• Otherwise, change the value of X in the database
  to v.
• After making the changes, write a log record
  ltABORT Tgt for each incomplete transaction T that
  was not previously aborted
• Flush the log
• Resume the normal database operations.

21
Example 17.3

• The crashes occurs after step (12)
• Nothing need to be done for T
• Between (11) and (12)
• If COMMIT T got flushed to disk (in some other
  transactions), nothing need to be done for T
• If COMMIT T never reached disk, T is incomplete,
  system restore values of A and B, and a record
  ltABORT Tgt is added to the log, and log is then
  flushed

22
Example 17.3 (Cont.)

• Between (10) and (11)
• T is uncompleted, Undo as in the previous case
• Between (8) and (10)
• T is uncompleted, Undo. Although the change of A
  and/or B may not have reached disk, we store
  value 8 for each of these elements
• Prior to (8)
• If the log reached disk, undo as the previous
  case if not, nothing needs to be done.

23
A simple checking points

• In a simple checking point, we
• Stop accepting new transactions
• Wait until all current active transactions commit
  or abort and have written a COMMIT or ABORT
  record on the log
• Flush the log to disk
• Write a log record ltCKPTgt, and flush the log
  again
• Resume accepting transactions.

24
Example 17.4
25
Problem in simple checkpointings

• The active transactions may take a long time to
  commit or abort, the system can not accept new
  transactions during this period.

26
Nonquiescent checkpointing

• Allows new transactions to enter the system
• Write a log record ltSTART CKPT(T1,,Tk)gt and
  flush the log
• Wait until all of T1,,Tk commit or abort, but do
  not prohibit other transactions from starting
• When all of T1,,Tk have completed, write a log
  record ltEND CKPTgt and flush the log

27
Recovery

• Still, scan the log from the end, finding all
  incomplete transactions, and restore old values
  for database elements changed.
• If we first meet a ltEND CKPTgt, which means all
  incomplete transactions began after the previous
  ltSTART CKPT(T1,,Tk)gt, we may scan as far as we
  see it.
• If we first meet a ltSTART CKPT(T1,,Tk)gt, then
  the crash occurred during the checkpoint. We only
  need to scan backward to all incomplete
  transactions in T1,,Tk,
• if we use pointers to chain all log records of
  the same transaction, we only need to follow the
  chains of the active transactions.

28
Example 17.5
29
An undo log using nonquiescent checkpointing
30
Undo log with a system crash during checkpointing
31
Exercises

• 17.2.2 a)
• 17.2.4 a) b)
• 17.2.5 a) b)
• 17.2.7 a) b)