Final Exam Review

1
Final Exam Review

• Last Lecture
• RG - All Chapters Covered

The end crowns all, And that old common
arbitrator, Time, Will one day end it.
William Shakespeare. Troilus and Cressida.
2
Topics Covered

• Transactions
• Concurrency Control
• Crash Recovery

3
ACID properties of Transaction Executions

• A tomicity All actions in the Xact happen, or
  none happen.
• C onsistency If each Xact is consistent, and
  the DB starts consistent, it ends up consistent.
• I solation Execution of one Xact is isolated
  from that of other Xacts.
• D urability If a Xact commits, its effects
  persist.

4
Concurrency Control

• Transaction basic unit of operation
• made up of reads and writes
• Goal ACID Transactions
• A D are provided by Crash Recovery
• C I are provided by Concurrency Control
• Bottom line reads and writes for various
  transactions MUST be ordered such that the final
  state of the database is the same as some serial
  ordering of the transactions

5
Scheduling Transactions

• Serial schedule Schedule that does not
  interleave the actions of different transactions.
• Equivalent schedules For any database state,
  the effect (on the set of objects in the
  database) of executing the first schedule is
  identical to the effect of executing the second
  schedule.
• Serializable schedule A schedule that is
  equivalent to some serial execution of the
  transactions.
• (Note If each transaction preserves
  consistency, every serializable schedule
  preserves consistency. )

6
Conflict Serializable Schedules

• Two schedules are conflict equivalent if
• Involve the same actions of the same transactions
• Every pair of conflicting actions is ordered the
  same way
• Schedule S is conflict serializable if S is
  conflict equivalent to some serial schedule

7
Example

• A schedule that is not conflict serializable
• The cycle in the graph reveals the problem. The
  output of T1 depends on T2, and vice-versa.

T1 R(A), W(A), R(B), W(B) T2
R(A), W(A), R(B), W(B)
A
T1
T2
Dependency graph
B
8
Dependency Graph

• Dependency graph One node per Xact edge from
  Ti to Tj if an operation of Ti conflicts with an
  operation of Tj and Tis operation appears
  earlier in the schedule than the conflicting
  operation of Tj.
• Theorem Schedule is conflict serializable if and
  only if its dependency graph is acyclic

9
An Aside View Serializability

• Schedules S1 and S2 are view equivalent if
• If Ti reads initial value of A in S1, then Ti
  also reads initial value of A in S2
• If Ti reads value of A written by Tj in S1, then
  Ti also reads value of A written by Tj in S2
• If Ti writes final value of A in S1, then Ti also
  writes final value of A in S2
• View serializability is weaker than conflict
  serializability!
• Every conflict serializable schedule is view
  serializable, but not vice versa!
• I.e. admits more legal schedules

T1 R(A) W(A) T2 W(A) T3 W(A)
T1 R(A),W(A) T2 W(A) T3
W(A)
10
Approaches to Concurrency Control

• 2PL - all objects have Shared and eXclusive locks
• once one lock is released, no more locks may be
  acquired
• Strict 2PL dont release locks until commit time
• Conservative 2PL acquire all locks at start,
  release all at end
• Locking issues
• must either prevent or detect deadlock
• may want multiple granularity locks (table, page,
  record) using IS, IX, SIX, S, X locks (check
  compatibility matrix!)
• locking in B-trees usually not 2PL
• phantom problem locking all records of a given
  criteria (e.g., age gt 20)

11
Multiple-Granularity Locks

• Hard to decide what granularity to lock (tuples
  vs. pages vs. tables).
• Shouldnt have to make same decision for all
  transactions!
• Data containers are nested

contains
12
Solution New Lock Modes, Protocol

• Allow Xacts to lock at each level, but with a
  special protocol using new intention
  locks
• Still need S and X locks, but before locking an
  item, Xact must have proper intension
  locks on all its ancestors in the
  granularity hierarchy.

• IS Intent to get S lock(s) at finer
  granularity.
• IX Intent to get X lock(s) at finer
  granularity.
• SIX mode Like S IX at the same time. Why
  useful?

13
Multiple Granularity Lock Protocol

• Each Xact starts from the root of the
  hierarchy.
• To get S or IS lock on a node, must hold IS or IX
  on parent node.
• What if Xact holds SIX on parent? S on parent?
• To get X or IX or SIX on a node, must hold IX or
  SIX on parent node.
• Must release locks in bottom-up order.

Protocol is correct in that it is equivalent to
directly setting locks at the leaf levels of the
hierarchy.
14
Examples 2 level hierarchy

• T1 scans R, and updates a few tuples
• T1 gets an SIX lock on R, then get X lock on
  tuples that are updated.
• T2 uses an index to read only part of R
• T2 gets an IS lock on R, and repeatedly gets an S
  lock on tuples of R.
• T3 reads all of R
• T3 gets an S lock on R.
• OR, T3 could behave like T2 can
  use lock escalation to decide
  which.
• Lock escalation
• Dynamically asks for coarser-grained locks
  when too many low level
  locks acquired

15
Other Approaches to CC

• Optimistic CC
• Timestamp CC
• Multiversion CC
• NOT COVERED in CLASS NOT ON FINAL

16
Question from old final - True/False

• Our lock manager uses S and X locks, which
  guarantees that all schedules are
  conflict-serializable
• Because we use 2PL, deadlocks can never occur
• Because we use Strict 2PL, there will be no
  cascading aborts

17
Question from old final - True/False

• Our lock manager uses S and X locks, which
  guarantees that all schedules are
  conflict-serializable
• Because we use 2PL, deadlocks can never occur
• Because we use Strict 2PL, there will be no
  cascading aborts

18
More questions ..

• Read Uncommitted (UR)
• Always read data without setting any locks
• Always set exclusive locks before writing and
  hold them until end-of-transaction.
• Or No S-Locks, Strict 2-Phase X-Locks. Which of
  the following conflicts can occur between a UR
  Xact, and a Strict 2PL Xact
• Write-Write
• Read-Write
• Write-Read

19
More questions ..

• Read Committed (RC)
• Obtain locks in the same way as Strict 2PL, and
  release exclusive locks at the end-of-transaction
  just like Strict 2PL.
• Or RC transactions use Short-Term S-Locks, and
  Strict 2-Phase X-Locks. Which of the following
  conflicts can occur between a RC Xact, and a
  Strict 2PL Xact
• Write-Write
• Read-Write
• Write-Read

20
More questions ..

• All SQL lock modes even the relaxed modes use
  Strict 2-Phase Write Locks. Consider what would
  happen if a transaction released a shared lock
  immediately after a read, and released an
  exclusive lock immediately after a write
  (Short-Term R-Locks and Short-Term X-Locks).
  Which of the following could happen in those
  cases?
• Write-Write conflicts.
• Inconsistent data in the database
• Deadlocks

21
Crash Recovery

• ACID - need way to ensure A D
• We studied approach of Aries system
• Buffer management Steal, no Force
• Every Write to a page is first logged in WAS
• log record is in stable storage before data page
  on disk
• log record has Xact, before value, after value
• Checkpoints record which pages dirty, which XActs
  running

22
Buffer Mgmt Plays a Key Role

• Force policy make sure that every update is on
  disk before commit.
• Provides durability without REDO logging.
• But, can cause poor performance.
• No Steal policy dont allow buffer-pool frames
  with uncommited updates to overwrite committed
  data on disk.
• Useful for ensuring atomicity without UNDO
  logging.
• But can cause poor performance.

Of course, there are some nasty details for
getting Force/NoSteal to work
23
Preferred Policy Steal/No-Force

• This combination is most complicated but allows
  for highest performance.
• NO FORCE (complicates enforcing Durability)
• What if system crashes before a modified page
  written by a committed transaction makes it to
  disk?
• Write as little as possible, in a convenient
  place, at commit time, to support REDOing
  modifications.
• STEAL (complicates enforcing Atomicity)
• What if the Xact that performed udpates aborts?
• What if system crashes before Xact is finished?
• Must remember the old value of P (to support
  UNDOing the write to page P).

24
Buffer Management summary
No Steal
Steal
No Steal
Steal
No Force
Fastest
No Force
Force
Slowest
Force
Performance Implications
Logging/Recovery Implications
25
Basic Idea Logging

• Record REDO and UNDO information, for every
  update, in a log.
• Sequential writes to log (put it on a separate
  disk).
• Minimal info (diff) written to log, so multiple
  updates fit in a single log page.
• Log An ordered list of REDO/UNDO actions
• Log record contains
• ltXID, pageID, offset, length, old data, new datagt
  
• and additional control info (which well see
  soon).

26
Write-Ahead Logging (WAL)

• The Write-Ahead Logging Protocol
• Must force the log record for an update before
  the corresponding data page gets to disk.
• Must force all log records for a Xact before
  commit. (alt. transaction is not committed until
  all of its log records including its commit
  record are on the stable log.)
• 1 (with UNDO info) helps guarantee Atomicity.
• 2 (with REDO info) helps guarantee Durability.
• This allows us to implement Steal/No-Force
• Exactly how is logging (and recovery!) done?
• Well look at the ARIES algorithms from IBM.

27
Transaction Commit

• write Commit record to log
• flush log tail to stable storage
• remove Xact from Xact table
• write End record to log

28
Transaction Abort

• write Abort record to log
• go back through log, undoing each write (and add
  CLR to log)
• when done, write End record to log

29
Crash Recovery - 3 phases

• Analysis starting from checkpoint, go forward in
  the log to see
• what pages were dirty
• what transactions were active at time of crash
• Redo start from oldest transaction that wrote to
  a dirty page, and redo all writes to dirty pages.
• Undo start at the end of the log (time of
  crash), work backward undoing all writes made by
  transactions that were active at time of crash
• What happens when you encounter a CLR ?