Concurrency Control and Crash Recovery

1
Concurrency Control and Crash Recovery

• (cont)

2
Deadlock Detection

• Waits-for graph
• Each node corresponds to an active transaction
• There is an arc (edge) from Tk to Tl currently
  holding a lock if and only if Tk is waiting for
  Tl to release the lock.
• If a waits-for graph is cyclic, then the schedule
  contains a deadlock

3
Example 9
T1 T2 T3 T4
R(A)
R(C)
W(B)
W(A)
W(C)
R(B)
W(A)
Does the schedule contain a deadlock?
4
Frequency of Deadlock Detection

• Assign initial time step ?t, ?tmin, and ?tmax
• If during ?t no deadlock is detected
• ?t 2 ?t
• If during ?t a deadlock is detected
• ?t ?t/2

5
Recovery after Deadlock Detection

• Choice of a victim
• A transaction that has just started
• A transaction that has a few locks
• A transaction that have a few changes left.

6
Database Performance

• Deadlock prevention
• Aborted transactions
• Suspended transactions
• Too strong conditions
• Deadlock detection
• Analysis of schedules

7
Concurrency Control with Timestamping Methods

• Timestamp unique identifier indicates starting
  time of the transaction
• Each data item has read and write timestamps
  timestamps of last transactions operating with
  the data item
• Methods
• Basic timestamp ordering
• Thomass write rule

8
Basic Timestamp Ordering - Read Rule

• Let T1 and T2 transactions with timestamps
  ts(T1) lt ts(T2)
• T1 issues a R(A) and A is modified by T2
• T1 is aborted and restarted with a new timestamp
• T2 issues a R(B) and B is modified by T1
• T2 reads B

9
Basic Timestamp Ordering Write Rule

• T1 issues a W(B) and B is read by T2
• T1 is aborted and restarted with a new timestamp
• T1 issues a W(C) and C is modified by T2
• T1 is aborted and restarted with a new timestamp
• T2 issues a W(D) and D is modified by T1
• T2 modifies D

10
Thomass Write Rule

• Let T1 and T2 transactions with timestamps
  ts(T1) lt ts(T2)
• T1 issues a W(B) and B is read by T2
• T1 is aborted and restarted with a new timestamp
• T1 issues a W(C) and C is modified by T2
• W1(C) is ignored
• T2 issues a W(D) and D is modified by T1
• T2 modifies D

11
Multiversion Schemes

• Each W(O) from the schedule creates a new version
  of O
• When a transaction issues R(O), the system
  selects the version that ensures serializability
• Versions that are no longer needed are deleted

12
Multiversion Timestamping Method

• Each transaction receives a static timestamp
  ts(Ti)
• For each data item used the system maintains
  versions O1, O2,,Om
• Each version Ok consists of
• Content
• W-timestamp
• R-timestamp

13
Multiversion Timestamp Ordering

• Possible version for any transaction T
• Om - version with maximal W-timestamp which is
  less or equal to ts(T)
• If T requests R(O), the content of Om is returned
• If T requests W(O)
• If ts(T)ltR-timestamp(Om), T is aborted
• If ts(T)R-tamestamp(Om), a new version Om1 is
  created.

14
Implementation Issues

• Control for schedule recoverability
• Version withdrawal
• Determine the timestamp of the oldest active
  transaction ts(T)
• Find all versions with W-timestamp less than
  ts(T)
• Delete all versions except the youngest one

15
Concurrency Control with Optimistic Methods

• Most database operations do not conflict
• Transaction is executed without restrictions
  until commits
• Phases
• Read Phase
• Validation Phase
• Write Phase

16
Transaction Phases

• Read-only transaction
• Read from the start until the commit point
• Validation possible conflicts are checked
• Update transaction
• Read
• Validation
• Write updates made to the local copy are stored
  to the database

17
Validation

• Each transaction is assigned three timestamps
  start, validation, and finish
• Test for transaction T
• For all S such that start(S)ltstart(T),
  finish(S)ltstart(T)
• For any S such that start(S)ltstart(T), but
  finish(S)start(T)
• T doesnt read data modified by S
• start(T)ltfinish(S)ltvalidation(T)

18
Rollback with Optimistic Method

• If the schedule contains a conflict, T is aborted
  and restarted
• Rollback affects only local copy
• No cascading rollbacks

19
Phantom Problem

• Two transactions are concurrently executed and
  one of them implements INSERT or DELETE statement
• Phantom the possibility that a transaction
  retrieves a set of objects twice and receives
  different sets

20
Transaction Support in SQL

• The beginning of a transaction
• Ending of the transaction
• Explicit
• COMMIT
• ROLLBACK
• Implicit
• Successful end of the program
• Abnormal program termination (Transaction is
  aborted)

21
Transaction Characteristics

• Access mode
• READ ONLY
• READ WRITE
• Isolation level
• Diagnostics size

22
Isolation Levels

• Serializable
• T reads only committed data
• No value read or written by T is modified by T
  until T is complete
• Phantom is not allowed
• Repeatable read
• T reads only committed data
• No value read or written by T is modified by T
  until T is complete
• Phantom is allowed

23
Isolation Levels (cont)

• Read committed
• T reads only committed data
• No value written by T is modified by T until T
  is complete
• Phantom is allowed
• Read uncommitted
• T reads changes of active transactions, values
  read can be further modified
• Is allowed for READ ONLY access mode

24
Transaction Configuration

• SET TRANSACTION
• READ ONLY l READ WRITE
• ISOLATION LEVEL
• READ UNCOMMITTED l READ COMMITTED l REPEATABLE
  READ l
• SERIALIZABLE

25
Transaction Support in Oracle

• SET TRANSACTION READ ONLY
• SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• SET TRANSACTION ISOLATION LEVEL READ COMMITTED

26
Crash Recovery

• The process of restoring the database to a
  correct state
• A transaction is a unit of recovery
• Loss of updates of committed transactions
• Atomicity and durability support

27
Buffers

• Main memory locations which are used to transfer
  data to and from secondary storage
• Dirty bit and dirty page
• Replacement strategies
• First-in-first-out (FIFO)
• Least recently used (LRU)

28
Buffer management

• A steal policy buffer manager writes a buffer to
  disk before a transaction commits
• Alternative policy no-steal
• A force policy all pages updated by a
  transaction are immediately written to disk when
  the transaction commits.
• Alternative policy no-force

29
Recovery Facilities

• Backup mechanism
• Logging
• Checkpoints
• Recovery algorithms

30
The Log

• History of actions executed by the DBMS
• Stable storage
• Log tail
• Actions are recorded in chronological order

31
Log Sequence Number

• Monotonically increasing order
• Every page (object) contains pageLSN
• PageLSN - the most recent LSN from the log

32
Actions recorded to the Log

• Start
• Updating an object
• Update record is appended to the log tail
• pageLSN is set to the current LSN
• Commit
• Commit record is appended to the log
• Log tail is written to stable storage

33
Actions recorded to the Log (cont)

• Abort
• Abort log record is appended to the log
• Undo is initiated
• End
• Undoing updates
• After the action described in update log record
  is undone, a compensation log record (CLR) is
  appended to the log.

34
Update Log Record

• Common fields for all log records
• LSN, prevLSN, transID, type
• Fields for update records only
• PageID
• Length of changes in bytes
• Offset (start byte)
• Before-image
• After-image

35
Compensation Log Record

• Is written just before the action recorded in
  Update log record is undone.
• The same fields as for Update record
• Field undonextLSN
• LSN from prevLSN of correspondent Update log
  record

36
The Write-Ahead Log Protocol

• All Update log records must be written to stable
  storage first
• Log records
• Transaction records
• Systems records

37
Checkpoints

• A point of synchronization between database and
  transaction log
• All buffers are force-written to secondary
  storage
• Checkpoints in AREIS
• Begin_checkpoint record
• End_checkpoint record
• Master record holds LSN of the begin_checkpoint
  record

38
Recovery Using Deferred Update

• Updates are not written to the database until the
  transaction T commits
• T starts
• Start log record
• T commits
• Commit log record
• Log tail is force-written to disk
• Updates are implemented

39
Recovery Using Deferred Update (cont)

• T aborts
• Abort log record
• Ignore the log records related to T
• Recovery after the crash
• Most recent checkpoint
• Transactions with Start and Commit log records
  are redone
• Transactions with Start and Abort log records are
  ignored
• Transactions that have neither Commit nor Abort
  log records receive Abort log records

40
Recovery Using Immediate Update

• Updates are written to the database as they occur
• T starts
• Start log record
• T writes
• Update log record
• Update is written to the buffer
• Update is applied to the database when the buffer
  content is transferred to secondary storage
• T commits
• Commit log record

41
Recovery Using Immediate Update (cont)

• T aborts
• Updates are undone in reverse order
• Recovery after the crash
• Most recent checkpoint
• Transactions with Start and Commit log records
  are redone
• Transactions with Start log record that dont
  have Commit log record are undone

42
Recovery (Example 10)
T1
T2
T3
T4
T5
T6
tc
tf
43
ARIES

• Algorithm for Recovery and Isolation Exploiting
  Semantics
• Works with steal, no-force policies
• Three main phases
• Analysis
• Redo
• Undo

44
Basic Principles

• Write-ahead logging
• Repeating history during Redo phase
• Logging of all changes during Undo phase

45
Analysis Phase

• Scan log from the checkpoint to the end
• Main goal
• To determine the point in the log at which to
  start the Redo step
• To determine dirty pages at the moment of the
  crash
• To identify active transactions at the time of
  the crash

46
Analysis Step Result

• Transaction Table contains accurate list of
  transactions active at the moment of the crash
• (actions must be undone)
• Dirty Page Table contains list of modified pages
  that must be recorded to secondary storage
• (actions must be redone)

47
Redo Phase

• Repeating history paradigm
• Redo(Redo(A)) Redo(A)
• Redo actions are recorded to the log
• The result
• The database is brought to the state as before
  the crash

48
Undo Phase

• Scans backward from the end of the log
• Undo(Undo(A)) Undo(A)
• For every Undo action the CLR is added to the log
  
• Removes all changes caused by incomplete
  transactions