Transaction Management, Concurrency Control and Recovery

1
Transaction Management, Concurrency Control and
Recovery

• Chapter 20

2
Overview

• What are transactions?
• What is a schedule?
• What is concurrency control?
• Why we need concurrency control
• Three problems.
• Serializabiltiy and Concurrency control
• Theory
• Conflict Serializability
• View Serializability
• Practice
• Locking
• Time-stamping
• Optimistic techniques
• Recovery facilities

3
What is a Transaction?

• Transaction
• Action, or series of actions, carried out by
  user or application, which accesses or changes
  contents of database.
• Logical unit of work on the database.
• Transforms database from one consistent state to
  another, although consistency may be violated
  during transaction.
• Example
• Read(staffNo, salary)
• salarysalary 1.1
• write(staffNo , salary)

4
What is a Transaction?

• Can have one of two outcomes
• Success - transaction commits and database
  reaches a new consistent state.
• Failure - transaction aborts, and database must
  be restored to consistent state before it started
  (rolled back or undone).
• Committed transaction cannot be aborted.
• Aborted transactions that are rolled back can be
  restarted later.

5
Properties of Transactions

• Four basic (ACID) properties of a transaction
  are
• Atomicity All or nothing property.
• Consistency Must transform database from one
  consistent state to another.
• Isolation Partial effects of incomplete
  transactions should not be visible to other
  transactions.
• Durability Effects of a committed transaction are
  permanent and must not be lost because of later
  failure.
• We deal with transactions in a schedule.

6
Schedule
Data Items affected by transactions (optional)
Start with t0 or t1
Running Transactions
Time T1 T2 T3 Balance1 Balance2
t0 Begin Transaction 100 200
t1 Read(Balance1) Begin Transaction 100 200
t2 Read(Balance1) Begin Transaction 100 200
t3 Balance1 500 Read(Balance2) 100 200
t4 Write(Balance1) 600 200
t5 Commit Read(Balance1) 600 200

Order of execution
7
Schedule Rules

• Never start two transactions at the same time.
• Never perform Reads and Writes of different
  transactions at the same time.
• Each transaction should end with a commit or
  abort (rollback).

8
Schedule Definitions

• Schedule
• Sequence of reads/writes by set of concurrent
  transactions.
• Serial Schedule
• Schedule where operations of each transaction
  are executed consecutively without any
  interleaved operations from other transactions.
• No guarantee that results of all serial
  executions of a given set of transactions will be
  identical. (Think of an example)
• Non-Serial Schedule
• Schedule where operations from set of concurrent
  transactions are interleaved

9
Example of a Serial Schedule
Time T1 T2
t0 Begin Transaction
t1 Read(Balance1)
t2 Balance1 500
t3 Commit
t4 Begin Transaction
t5 Read(Balance1)
t6 Commit
10
Example of a non-Serial Schedule
Time T1 T2
t0 Begin Transaction
t1 Begin Transaction
t2 Read(Balance1)
t3 Read(Balance1)
t4 Commit
t5 Balance1 500
t6 Commit
11
What is Concurrency Control?

• Concurrency transactions running simultaneously.
• Concurrency Control Process of managing
  simultaneous operations (transactions) on the
  database without having them interfere with one
  another.
• Prevents interference when two or more users are
  accessing database simultaneously and at least
  one is updating data.
• Although two transactions may be correct in
  themselves, interleaving of operations may
  produce an incorrect result.

12
Why we Need Concurrency Control?

• Three examples of potential problems caused by
  concurrency
• Lost update problem.
• Uncommitted dependency problem.
• Inconsistent analysis problem.

13
Lost Update Problem

• Successfully completed update is overridden by
  another user.
• T1 withdrawing 10 from an account with balx,
  initially 100.
• T2 depositing 100 into same account.
• Serially, final balance would be 190.
• Loss of T2s update avoided by preventing T1 from
  reading balx until after update

14
Uncommitted Dependency Problem

• Occurs when one transaction can see intermediate
  results of another transaction before it has
  committed.
• T4 updates balx to 200 but it aborts, so balx
  should be back at original value of 100.
• T3 has read new value of balx (200) and uses
  value as basis of 10 reduction, giving a new
  balance of 190, instead of 90.
• Problem avoided by preventing T3 from reading
  balx until after T4 commits or aborts.

15
Inconsistent Analysis Problem

• Occurs when transaction reads several values but
  second transaction updates some of them during
  execution of first.
• Sometimes referred to as dirty read or
  unrepeatable read.
• T6 is totaling balances of account x (100),
  account y (50), and account z (25).
• Meantime, T5 has transferred 10 from balx to
  balz, so T6 now has wrong result (10 too high).

16
Inconsistent Analysis Problem

• Problem avoided by preventing T6 from reading
  balx and balz until after T5 completed updates.

17
Serializability

• Serializability is a property of a schedule
• We say serializable schedule and non-serializable
  schedule.
• But what makes a schedule serializable?
• A serializable schedule is a non-serial schedule
  that allows transactions to execute concurrently
  without interfering with one another.
• In other words, a non-serial schedule that is
  equivalent to some serial schedule.
• Main goal is to prevent transactions interfering
  with each other (3 problems discussed earlier).

18
Serializability

• Two types of seriailizability
• Conflict.
• View.

19
Conflict Serializability

• In serializability, ordering of read/writes is
  important
• (a) If two transactions only read a data item,
  they do not conflict and order is not important.
• (b) If two transactions either read or write
  completely separate data items, they do not
  conflict and order is not important.
• (c) If one transaction writes a data item and
  another reads or writes same data item, order of
  execution is important. They conflict.

20
Conflict Serializability

• Schedule S1 is conflict serializable if it is
  conflict equivalent to a serial schedule.
• Two ways of testing a schedule for conflict
  serialiazibility
• A schedule is conflict serializable if you can
  switch order of 2 non-conflicting operations
  until you reach a serial schedule.
• Precedence graph.

21
Testing for Conflict Serializability
Time T7 T8 t1
begin-transaction t2 read(balx) t3
write(balx) t4
begin_transaction t5
read(balx) t6 write(balx) t7
read(baly) t8 write(baly) t9
commit t10 read(baly)
t11 write(baly) t12
commit
T7 T8
begin-transaction read(balx)
write(balx)
begin_transaction
read(balx) read(baly)
write(balx) write(baly)
commit read(baly)
write(baly) commit
22
Testing for Conflict Serializability
Time T7 T8 t1
begin-transaction t2 read(balx) t3
write(balx) t4
begin_transaction t5
read(baly) t6 read(balx) t7
write(balx) t8 write(baly)
t9 commit t10
read(baly) t11 write(baly) t12
commit
T7 T8
begin-transaction read(balx)
write(balx) read(baly) write(baly)
commit
begin_transaction
read(balx) write(balx)
read(baly) write(baly)
commit
23
Non-conflict Serializable Schedule
Time T7 T8 t1
begin-transaction t2 read(balx) t3

begin_transaction t4

write(balx) t5 write(balx) t6
commit t7 commit
T7 T8
begin-transaction read(balx)
write(balx) commit

begin_transaction

write(balx) commit
24
Testing for Conflict Serializability Precedence
Graph

• Create
• node for each transaction
• a directed edge Ti ? Tj, if Tj reads the value of
  an item written by Ti
• a directed edge Ti ? Tj, if Tj writes a value
  into an item after it has been read by Ti.
• a directed edge Ti ? Tj, if Tj writes a value
  into an item after it has been written by Ti.
• If precedence graph contains cycle, schedule is
  not conflict serializable.

25
Test Schedule Is it conflict serializable?
Time T7 T8 t1
begin-transaction t2 read(balx) t3
balx balx 100 t 4
write(balx) t5
begin_transaction t6 read(balx)
t7 balx balx 1.1 t8
write(balx) t9 read(baly) t10
baly baly 1.1 t11
write(baly) t12
commit t13 read(baly) t14 write(baly) t15
commit
26
View Serializability

• Offers less stringent definition of schedule
  equivalence than conflict serializability.
• Two schedules S1 and S2 are view equivalent if
• For each data item x, if Ti reads initial value
  of x in S1, Ti must also read initial value of x
  in S2.
• For each read on x by Ti in S1, if value read by
  x is written by Tj, Ti must also read value of x
  produced by Tj in S2.
• For each data item x, if last write on x
  performed by Ti in S1, same transaction must
  perform final write on x in S2.

27
View Serializability

• Schedule is view serializable if it is view
  equivalent to a serial schedule.
• Every conflict serializable schedule is view
  serializable, although converse is not true.
• It can be shown that any view serializable
  schedule that is not conflict serializable
  contains one or more blind writes.

28
View Serializable Schedule
Time T7 T8 t1
begin-transaction t2 read(balx) t3
write(balx) t4 read(baly) t5
write(baly) t6 commit t7
begin-transaction t8 read(balx)
t9
write(balx) t10
read(baly) t11 write(baly) t12
commit
T7
T8 begin-transaction read(balx)
write(balx)
begin_transaction read(balx)
write(balx) read(baly) write(baly)
commit read(baly) write(baly)
commit
29
View Serializable Schedule
Time T11 T12 T13 t1
begin-transaction t2
read(balx) t3

begin_transaction t4
write(balx) t5 commit t6
write(balx) t7 commit t8

begin_transaction t9 write(balx) t10
commit
Is this schedule conflict serializable?
30
Recoverable Schedule

• A schedule where, for each pair of transactions
  Ti and Tj, if Tj reads a data item previously
  written by Ti, then the commit operation of Ti
  precedes the commit operation of Tj.

31
Concurrency Control Techniques
32
Concurrency Control Techniques

• Two basic concurrency control techniques
• Locking,
• Timestamping.
• Both are conservative approaches delay
  transactions in case they conflict with other
  transactions.
• Optimistic methods assume conflict is rare and
  only check for conflicts at commit.

33
Concurrency Control Techniques Overview
Locking
Time-stamping
Optimistic
Basic Rules
2PL
Deadlock Prevention
Basic Time-stamp Ordering
Multi-version Time-stamp Ordering
Deadlock Detection
Thomass Write Rule
Regular
Rigorous
Wait-Die
Wound-Wait
Wait-for Graph
Time outs
Strict
34
Locking

• Main Idea Transaction uses locks to deny access
  to other transactions and so prevent incorrect
  updates.
• Most widely used approach to ensure
  serializability.
• A transaction must claim
• a shared (read) on x before it can read it.
• or an exclusive (write) lock on x before it can
  write it.
• Lock prevents other transactions from reading or
  writing the locked data item.

35
Locking Basic Rules

• Shared Lock
• If transaction has shared lock on item, it can
  read but not update item.
• More than one transaction can hold a shared lock
  on an item.
• Exclusive Lock
• If transaction has exclusive lock on item, can
  both read and update item.
• Only one transaction can hold an exclusive lock
  on an item.
• Some systems allow transaction to
• upgrade read lock to an exclusive lock.
• downgrade exclusive lock to a shared lock.

36
Locking -- Commands

• To acquire a shared (read) lock on X
• Read_Lock(x)
• RLock(X)
• Shared_Lock(X)
• SLock(X)
• To acquire an exclusive (write) lock on X
• Write_Lock(X)
• WLock(X)
• Exclusive_Lock(X)
• XLock(X)
• To release a lock on X
• Unlock(X)

37
Time T9 T10 t1
begin-transaction t2
write_lock(balx) t3 read(balx) t4 balx
balx 100 t5
write(balx) t6 unlock(balx) t7
begin_transaction t8
write_lock(balx) t9 read(balx)
t10 balx balx 1.1 t11
write(balx) t12 unlock(balx) t13 wri
te_lock(baly) t14 read(baly) t15 baly
baly 1.1 t16
write(baly) t17
commit/unlock(baly) t18
write_lock(baly) t19 read(baly) t20 baly
baly - 100 t21 write(baly) t22
commit/unlock(baly)
Correct use of locks. But is the execution
correct?
38
Two-Phase Locking (2PL)

• We just saw that locking alone doesnt always
  work.
• Solution 2PL.
• Transaction follows 2PL protocol if all locking
  operations precede first unlock operation in the
  transaction.
• Two phases for transaction
• Growing phase - acquires all locks but cannot
  release any locks.
• Shrinking phase - releases locks but cannot
  acquire any new locks.
• With 2PL, we can prevent the three problems.

39
Original Lost Update Problem
40
Preventing Lost Update Problem
Time T1 T2 balx t1
begin-transaction 100 t2 begin_transaction wri
te_lock(balx) 100 t3 write_lock(balx) r
ead(balx) 100 t4 WAIT balx balx
100 100 t5 WAIT write(balx) 200 t6
WAIT commit/unlock(balx)
200 t7 read(balx) 200 t8
balx balx -10 200 t9 write(balx)
190 t10 commit/unlock(balx) 190
41
Original Uncommitted Dependency Problem
42
Preventing Uncommitted Dependency Problem
Time T3 T4 balx t1
begin-transaction 100 t2 write_lock(balx) 10
0 t3 read(balx) 100 t4
begin_transaction balx balx 100 100 t5
write_lock(balx) write(balx) 200 t6
WAIT commit/unlock(balx)
200 t7 read(balx) 200 t8
balx balx -10 200 t9 write(balx)
190 t10 commit/unlock(balx) 190
43
Original Inconsistent Analysis Problem
44
Preventing Inconsistent Analysis Problem
45
A Potential Problem with 2PL
46
Cascading Rollbacks

• If every transaction in a schedule follows 2PL,
  schedule is serializable.
• However, problems can occur with interpretation
  of when locks can be released.
• Cascading rollback is undesirable since they
  potentially lead to the undoing of a significant
  amount of work
• To prevent this with 2PL, 2 solutions
• Rigorous 2PL Leave release of all locks until
  end of transaction.
• Strict 2PL Holds only exclusive locks until the
  end of the transaction.
• BOTH are still 2PL. So both still have growing
  and shrinking phases.
• 2PL still may cause deadlock.

47
Problems with 2PL

• Cascading Rollbacks
• Solved with strict or rigorous 2PL.
• Dead Locks
• Happen in regular 2PL, and also in strict and
  rigorous 2PL.
• Handled using deadlock detection and prevention
  techniques.

48
Deadlocks

• Deadlock An impasse that may result when two (or
  more) transactions are each waiting for locks
  held by the other to be released.
• Once a deadlock happens, only one way to break
  deadlock abort one or more of the transactions.
• Deadlock should be transparent to user, so DBMS
  should restart aborted transaction(s).

49
Example Deadlock
Time T9 t1
begin-transaction t2
write_lock(balx) t3 read(balx) t4 balx
balx - 10 t5 write(balx) t6
write_lock(baly) t7 WAIT t8 WAIT t9
WAIT t10 WAIT t11
T10
begin-transaction write_lock(baly)
read(baly) baly baly 100
write(baly)
wait_lock(balx) WAIT WAIT
WAIT
50
Deadlock Handling

• Two general techniques for handling deadlock
• Deadlock prevention DBMS doesnt allow deadlock
  to happen.
• Timeouts.
• Wait-Die.
• Wound-wait.
• Deadlock detection and recovery DBMS allows
  deadlocks to happens but detects and recovers
  from them.
• Wait-for Graphs (WFG).

51
Timeouts

• Transaction that requests lock will only wait for
  a system-defined period of time.
• If lock has not been granted within this period,
  lock request times out.
• DBMS assumes transaction deadlocked, even though
  it may not be, and it aborts and automatically
  restarts the transaction.

52
Timestamps

• Before we discuss Wait-die and Wound-wait
  techniques, introduce timestamps.
• A timestamp is a unique number given to each
  transaction.
• Traditionally, it is the time the transaction
  started.
• The smaller the timestamp, the older the
  transaction.

53
Timestamps
Time T11 T12 T13 t1
begin-transaction t2
read(balx) t3

begin_transaction t4
write(balx) t5 commit t6
write(balx) t7 commit t8

begin_transaction t9 write(balx) t10
commit

• TS(T11) 1
• TS(T12) 3
• TS(T13) 8

54
Wait-Die Technique

• Only an older transaction can wait for younger
  one, otherwise transaction is aborted (dies) and
  restarted with same timestamp. (Why the same?)
• If a transaction Ti requests a lock on an item
  held by Tj
• If Ti gt Tj TS(Ti) lt TS(Tj), Ti waits for Tj to
  release the lock.
• If Ti lt Tj TS(Ti) gt TS(Tj), Ti is aborted and
  restarted with the same TS.

55
Wound-Wait Technique

• only a younger transaction can wait for an older
  one. If older transaction requests lock held by
  younger one, younger one is aborted (wounded) and
  restarted with same timestamp. (Why the same?)
• If a transaction Ti requests a lock on an item
  held by Tj
• If Ti gt Tj TS(Ti) lt TS(Tj), Tj is aborted and
  Ti gets the lock.
• If Ti lt Tj TS(Ti) gt TS(Tj), Ti waits for Tj to
  release the lock.

56
Deadlock Detection and Recovery

• Usually handled by construction of wait-for graph
  (WFG) showing transaction dependencies
• Create a node for each transaction.
• Create edge Ti ?Tj, if Ti waiting to lock item
  locked by Tj.
• Deadlock exists if and only if WFG contains
  cycle.

57
Example Schedule with WFG
Time T9 t1
begin-transaction t2
write_lock(balx) t3 read(balx) t4 balx
balx - 10 t5 write(balx) t6
write_lock(baly) t7 WAIT t8 WAIT t9
WAIT t10 WAIT t11
T10
begin-transaction write_lock(baly)
read(baly) baly baly 100
write(baly)
wait_lock(balx) WAIT WAIT
WAIT
T10
T9
58
Deadlock Detection and Recovery

• WFG is created at regular intervals.
• Several issues when recovering from a deadlock
• choice of deadlock victim
• avoiding starvation.
• Self-read pages 596-597

59
Concurrency Control Techniques Overview
Locking
Time-stamping
Optimistic
Basic Rules
2PL
Deadlock Prevention
Basic Time-stamp Ordering
Multi-version Time-stamp Ordering
Deadlock Detection
Thomass Write Rule
Regular
Rigorous
Wait-Die
Wound-Wait
Wait-for Graph
Time outs
Strict
60
Timestamping

• Main Idea Transactions ordered globally so that
  older transactions (smaller timestamps) get
  priority in the event of conflict.
• Conflict is resolved by rolling back (aborting)
  and restarting transaction.
• No locks so no deadlock.
• Timestamp
• A unique identifier created by DBMS that
  indicates relative starting time of a
  transaction.
• Timestamping
• A concurrency control protocol that orders
  transactions in such a way that order
  transactions. Transactions with smaller
  timestamps, get priority in the event of conflict

61
Timestamping

• 2 Techniques
• Basic Timestamp Ordering.
• Thomass Write Rule.
• Multiversion Timestamp Ordering.

62
Basic Timestamp Ordering

• Read/write proceeds only if last update on that
  data item was carried out by an older
  transaction.
• Otherwise, transaction requesting read/write is
  restarted and given a new timestamp.
• Main Goal Ordering writes then reads/writes as
  they would have been ordered in a serial
  schedule.
• Timestamps are also set for data items
• read-timestamp - timestamp of last transaction to
  read item
• write-timestamp - timestamp of last transaction
  to write item.

63
Basic Timestamping Read(x)

• Consider a read(x) transaction T with timestamp
  TS(T)
• TS(T) lt write_timestamp(x)
• x already updated by younger (later) transaction.
• Transaction T must be aborted and restarted with
  a new timestamp.
• TS(T) ? write_timestamp(x)
• execute the read(x) operation of T
• read_timestamp(x) TS(T)

64
Basic Timestamping Write(x)

• TS(T) lt read_timestamp(x)
• x already read by younger transaction.
• Transaction T must be aborted and restarted with
  a new timestamp.
• TS(T) lt write_timestamp(x)
• x already written by younger transaction.
• Transaction T must be aborted and restarted with
  a new timestamp.
• Otherwise, operation is accepted and executed.
• Write_timestamp(x) TS(T)

65
Basic Timestamp Ordering
66
Thomass Write Rule

• Provide greater concurrency by rejecting obsolete
  write operations.
• When a read(x) is encountered, behave just like
  in slide 62.
• When a write(x) is encountered, perform the
  following check
• TS(T) lt read_timestamp(x)
• x already read by younger transaction.
• Transaction T must be aborted and restarted with
  a new timestamp.
• TS(T) lt write_timestamp(x)
• x already written by younger transaction.
• Ignores the write operation (ignore obsolete
  write rule)
• Otherwise, operation is accepted and executed.
• Write_timestamp(x) TS(T)

67
Comparison of Methods
68
Multiversion Timestamp Ordering

• Main Idea Versioning of data can be used to
  increase concurrency so create multiple versions
  of each data item.
• Basic timestamp assumes only one version of data
  item exists, and so only one transaction can
  access data item at a time.
• Multiversion allows multiple transactions to read
  and write different versions of same data item.
• Multiversion ensures each transaction sees
  consistent set of versions for all data items it
  accesses.
• In multiversion
• Each write operation creates new version of data
  item while retaining old version.
• When transaction attempts to read data item,
  system selects one version that ensures
  serializability ? NO ABORTS ON READs
• Each version has a read and a write timestamp.
• Versions can be deleted once they are no longer
  required.

69
Multiversion Timestamping Read(x)

• When a transaction T wishes to read x, we find
  the correct version and let it read it.
• The correct version, xi, is the latest version
  written by an older transaction
• TS(T) ? write_timestamp(xi)
• After xi is found and read by T, we need to
  record that xi was read by T
• read_timestamp(xi) max(read_timestamp(xi),
  TS(T))
• Absolutely no aborts on read.

70
Multiversion Timestamping Write(x)

• When a transaction T wishes to write x, we need
  to perform a test first then write a new version.
• Test we need make sure that there is no older
  version of x that has been read by a transaction
  younger than T
• ? The transaction that is younger than T should
  read Ts version, not this older version.

71
Multiversion Timestamping Write(x)

• Find the correct version, xi is the latest
  version written by an older transaction
• TS(T) ? write_timestamp(xi)
• Test it make sure that no younger transaction
  has already read xi
• TS(T) lt read_timestamp(xi)?
• If yes Abort T.
• If no create a new version xj of x
• read_timestamp(xj) write_timestamp(xj) TS(T)

72
Time T1 T2 T3 T4 T5
0 Begin
1 Begin
2 Read(x)
3 xx20
4 Write(x)
5 Begin
6 Read(x)
7 Begin
8 Read(x)
9 Read(y)
10 Read(y)
11 Begin
12 Read(y)
13 yy/2
14 Write(y)
15 yyx-100
16 Write(y)
17 xx0.10x
18 Write(x)
19 Commit
20 Commit
21 Commit
22 Commit
23 Commit
73
Concurrency Control Techniques Overview
Locking
Time-stamping
Optimistic
Basic Rules
2PL
Deadlock Prevention
Basic Time-stamp Ordering
Multi-version Time-stamp Ordering
Deadlock Detection
Thomass Write Rule
Regular
Rigorous
Wait-Die
Wound-Wait
Wait-for Graph
Time outs
Strict
74
Optimistic Techniques

• Main Idea conflict is rare and it is more
  efficient to let transactions proceed without
  delays to ensure serializability.
• At commit, check is made to determine whether
  conflict has occurred.
• If there is a conflict, transaction must be
  rolled back and restarted.
• Potentially allows greater concurrency than
  traditional protocols.
• Three phases
• Read.
• Validation.
• Write.

75
Optimistic Techniques Read Phase

• Extends from start until immediately before
  commit.
• Transaction reads values from database and stores
  them in local variables. Updates are applied to a
  local copy of the data.
• DB is not changed during read phase.

76
Optimistic Techniques Validation Phase

• Follows the read phase just before the
  transaction commits.
• For read-only transaction
• check that data read are still current values.
• If no interference, transaction is committed.
• Else, transaction is aborted and restarted.
• For update transaction
• check transaction leaves database in a
  consistent state, with serializability
  maintained.

77
Optimistic Techniques Write Phase

• Follows successful validation phase for update
  transactions.
• Updates made to local copy are applied to the
  database.

78
Database Recovery

• Process of restoring database to a correct state
  in the event of a failure.
• Transactions represent basic unit of recovery.
• Recovery manager responsible for atomicity and
  durability.
• If failure occurs between commit and database
  buffers being flushed to secondary storage then,
  to ensure durability, recovery manager has to
  redo (rollforward) transactions updates.
• If transaction had not committed at failure time,
  recovery manager has to undo (rollback) any
  effects of that transaction for atomicity.
• Partial undo - only one transaction has to be
  undone.
• Global undo - all transactions have to be undone.
  

79
Example

• DBMS starts at time t0, but fails at time tf.
  Assume data for transactions T2 and T3 have been
  written to secondary storage.
• T1 and T6 have to be undone. In absence of any
  other information, recovery manager has to redo
  T2, T3, T4, and T5.

80
Recovery Facilities

• DBMS should provide following facilities to
  assist with recovery
• Backup mechanism which makes periodic backup
  copies of database.
• Logging facilities which keep track of current
  state of transactions and database changes.
• Checkpoint facility which enables updates to
  database in progress to be made permanent.
• Recovery manager which allows DBMS to restore
  database to consistent state following a failure.

81
2. Logging Facilities The Log File

• Contains information about all updates to
  database
• Transaction records.
• Checkpoint records.
• Often used for other purposes (for example,
  auditing).

82
3. Checkpoint Facility

• Checkpoint
• Point of synchronization between database and
  log file. All buffers are written to secondary
  storage.
• A checkpoint consists of the following actions
• Suspend execution of transactions temporarily.
• Write all updated buffers to disk.
• Write a checkpoint log record to disk.
• Resume executing transactions.
• When failure occurs, the recovery manager
  performs the following
• Redo all transactions that committed since the
  checkpoint.
• Undo all transactions active at time of crash (if
  using immediate update).

83
Example

• T1 and T6 undo.
• T2 and T3 do nothing.
• T4 and T5 redo.

84
Checkpoint in Log File
85
Main Recovery Techniques

• Three main recovery techniques
• Deferred Update.
• Immediate Update.
• Shadow Paging.

86
Deferred Update

• Updates are not written to the database until
  after a transaction has reached its commit point.
  
• Start from the last checkpoint
• If a transaction has committed before checkpoint
  ? Do nothing.
• If a transaction has committed after checkpoint
  ? Redo it.
• If a transaction has not committed after
  checkpoint ? Do nothing.

87
Immediate Update

• Updates are applied to database as they occur.
• Start from the last checkpoint
• If a transaction has committed before checkpoint?
  Do nothing.
• If a transaction has committed after checkpoint ?
  Redo it.
• If a transaction has not committed after
  checkpoint ? Undo it.
• Undo is done in reverse order from bottom of log
  file to top.
• Redo is done in order from top of log file to
  bottom.

88
Example

• Using Deferred Update Which transactions to
  undo? Redo?
• Using Immediate Update Which transactions to
  undo? Redo?

89
Shadow Paging

• Maintain two page tables during life of a
  transaction
• Current page table.
• Shadow page table.
• When transaction starts, two tables are the same.
  
• Shadow page table is never changed thereafter and
  is used to restore database in event of failure.
• During transaction, current page table records
  all updates to database.
• When transaction completes, current page table
  becomes shadow page table.