Final Exam Review - PowerPoint PPT Presentation

About This Presentation
Title:

Final Exam Review

Description:

... (table, page, record) using IS, IX, SIX, S, X locks (check compatibility matrix! ... We studied approach of Aries system. Buffer management Steal, no Force ... – PowerPoint PPT presentation

Number of Views:88
Avg rating:3.0/5.0
Slides: 30
Provided by: joehell
Learn more at: https://dsf.berkeley.edu
Category:

less

Transcript and Presenter's Notes

Title: 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 ?
Write a Comment
User Comments (0)
About PowerShow.com