Chapter 7 Isolation Concepts - PowerPoint PPT Presentation

About This Presentation
Title:

Chapter 7 Isolation Concepts

Description:

Chapter 7 Isolation Concepts Dr. Kien A. Hua Isolation Concepts (1) The system state consists of objects related in certain ways. These relationships are best thought ... – PowerPoint PPT presentation

Number of Views:152
Avg rating:3.0/5.0
Slides: 124
Provided by: TingJu6
Learn more at: http://www.cs.ucf.edu
Category:

less

Transcript and Presenter's Notes

Title: Chapter 7 Isolation Concepts


1
Chapter 7 Isolation Concepts
  • Dr. Kien A. Hua

2
Isolation Concepts (1)
  • The system state consists of objects related in
    certain ways. These relationships are best
    thought of as invariants about the
    objects.Examples
  • Account balances must be positive.
  • All managers must be employees and so must appear
    in the EMPLOYEE table.
  • The system state is said to be consistent if it
    satisfies all these invariants.

3
Isolation Concepts (2)
  • Often, the state must be made temporarily
    inconsistent while it is being transformed (by a
    transaction) to a new, consistent state.
  • Concurrent transaction execution must be
    controlled so that correct programs do not
    malfunction.
  • Systems try to control concurrency by providing
    the appearance of isolation (e.g., using locking).

4
The Dependency Model of Isolation
  • Ii ? the set of objects read by transaction Ti
    (its input)Oi ? the set of objects written by
    Ti (its output)Ti ? the set of transactions.
  • The set of transactions Ti can run in parallel
    with no concurrency anomalies if their outputs
    are disjoint from one anothers inputs and
    outputsOi ? (Ij ? Oj) ? for all i ? j
  • The ACID property assumes that the transaction
    knows what it is doing to its datagt If a
    transaction runs in isolation, it will
    correctly transform the system state.

5
Static Allocation
  • The transaction scheduler compares the new
    transactions needs to all the running
    transactions.
  • If there is a conflict, initiation of the new
    transaction is delayed until the conflicting
    transactions has completed.
  • Problem It is difficult to determine the inputs
    and outputs of a transaction before it runs.
  • Example Since a banking transaction potentially
    updates any record of the Account table, static
    allocation would probably reserve the entire
    table.

6
Dynamic allocation
  • Each transaction is viewed as a sequence of
    actions (rather than as an input-output set.)
  • When an action accesses a particular object, the
    object is dynamically allocated to that
    transaction.
  • Advantage more concurrency

7
Transaction Dependencies (1)
  • Transactions are sequences of actions operating
    on objects. Objects go through a sequence of
    versions as they are written by these actions
  • Nothing ever changes rather, new versions of
    objects are created.
  • Dependencies
  • If a transaction reads an object, the transaction
    depends on that object version.
  • If the transaction writes an object, the
    resulting object version depends on the writing
    transaction.

8
Transaction Dependencies (2)
  • Note Only write actions create versions and
    dependencies.

a dependency graph
9
Isolation Theorems (1)
  • The main result of isolation theorems is that any
    dependency graph without cycles implies an
    isolated execution of the transactions.

  • The transactions can be
    topologically sorted to

    make an equivalent
    execution history in which
    each
    transaction ran
    serially, one completing
    before the next began.

T1
T1
T2
T2
T3
T4
T3
T4
10
Isolation Theorems (2)

  • There is no topological
    order
    for this set of transactions.
    gt concurrency anomalies.

T1
T2
T3
11
Three Transaction Dependencies (1)
  • The various ways isolation is violated are
    characterized by the related dependency cycles.
    Cycles take one of three generic forms.
  • Lost update caused by WRITE-gtWRITE
    dependenciesT2 READ lt o,1 gt
    T1s writeT1 WRITE lt o,2 gt
    is ignored byT2
    WRITE lt o,3 gt
    T2
  • Dirty read caused by WRITE-gtREAD dependencies
    T2 WRITE lt o,2 gtT1
    READ lt o,2 gtT2 WRITE lt o,3 gtT1 reads an
    object previously written by T2, then T2 makes
    further changes to the object.

12
Three Transaction Dependencies (2)
  • Unrepeatable read caused by READ-gtWRITE
    dependencies

  • T1 READ lt o,1 gtT2 WRITE lt o,2 gtT1
    READ lt o,2 gt
  • T1 reads an object twice, once before T2 updates
    it and once after committed T2 has updated it
    (the first version is not repeatable)
  • Note Preventing such anomalies gives the effect
    of running transactions in isolation.

13
Isolation (1)
  • Definition The outputs of a transaction are said
    to be uncommitted, or dirty, if the transaction
    has not yet issued COMMIT WORK.
  • Transaction T is isolated from other transactions
    if
  • T does not overwrite dirty data of other
    transactions.
  • Ts writes are neither read nor written by other
    transactions until COMMIT WORK
  • T does not read dirty data from other
    transactions.
  • Other transactions do not write any data read by
    T before T completes.

14
Isolation (2)
  • Effects
  • (1) (2) preclude lost updates
  • (3) isolates the transaction from dirty reads
  • (4) prevents unrepeatable reads.
  • Properties
  • Each transaction reads consistent input state.
  • Any execution of the system is equivalent to some
    serial execution (no concurrency anomalies)
  • None of the updates of committed transactions can
    be lost, and aborted transactions can be rerun.

15
Notations
  • S lt a, b, c gt a sequenceS S?
    concatenation of two sequencesSi the ith
    element of sequence S
  • lt Si predicate (Si) gt a subsequence of S
  • The system state, S, consists of an infinite set
    of named objects, each with a value. S is denoted
    lt name, value gt
  • The system supports the following actions on
    these objects.

READ WRITE XLOCK (exclusive lock) SLOCK (share
lock) UNLOCK
BEGIN COMMIT ROLLBACK
16
Simple Transactions (1)
  • To simplify the transaction model, BEGIN, COMMIT,
    and ROLLBACK are defined in terms of the other
    actions, so that only READ, WRITE, LOCK and
    UNLOCK actions remain.
  • Examples

17
Simple Transactions (2)
  • The simple transactions equivalent to T and
    T?
  • Idea The commit action simply releases
    locks, while the ROLLBACK action must first undo
    all changes to the objects the transaction wrote
    and then issue the unlock statements.

18
Well-Formed and 2-Phase Transactions (1)
  • A transaction is said to be well-formed if
  • All its READ, WRITE, and UNLOCK actions are
    covered by locks (preceded by locking of the
    corresponding object), and
  • Each lock action is eventually followed by a
    corresponding UNLOCK action.
  • - READ
    and UNLOCK actions
    on o3 are
    not covered by an
    SLOCK or XLOCK.
  • -
    WRITE action on o1 is not
    covered by an XLOCK

19
Well-Formed and 2-Phase Transactions (2)
  • A transaction is defined as 2-phase if all its
    lock actions precede all its UNLOCK actions.
  • Growing phase acquiring locks
  • Shrinking Phase releasing locks
  • The previous example is a 2-phase.

20
Transaction Histories
  • Any sequence-preserving merge of the actions of a
    set of transactions into a single sequence is
    called a history for the set of transactions.
  • A history lists the order in which actions were
    successfully completed.
  • The simplest histories first run all the actions
    of one transaction, then run all the actions of
    another to completion, and so on. Such
    one-transaction-at-a-time histories are called
    serial histories.
  • Note serial histories have no concurrency-induced
    inconsistency and no transaction sees dirty
    data.
  • An initial state and a history completely define
    the systems behavior.

21
Legal Histories (1)
  • Locking constrains the set of allowed histories.
  • Histories that obey the locking constraints are
    called Legal.

22
Legal Histories (2)
  • Note Histories are not constructed, they are a
    byproduct of the system behavior. For example,
    locking systems only produce legal behavior.

23
Dependency Graph (1)
  • lt t, a, o gt a step of the history. It denotes
    an action a by transaction t
    on object o.
  • V(o, k) The version of an object o at step k of
    a history.At step k of
    history H, object o has a version equal to the
    number of writes of that object before this
    step.V(o, k) lt t, aj, o gt?H jltk and aj
    WRITE

24
Dependency Graph (2)
  • Each history H for a set of transactions Ti
    defines a ternary dependency relation DEP(H) lt
    T, lt o, V(o,j) gt, T? gt ? DEP(H)if a1 is a WRITE
    and a2 is a WRITE, or a1 is a WRITE and a2 is
    a READ, or a1 is a READ and a2 is a
    WRITE.where a1 and a2 are actions performed on
    the object o by T and T?, respectively.Note
    This definition captures the WRITE ?WRITE,
    WRITE?READ, and READ?WRITE dependencies.
  • The dependency relation for a history defines a
    directed dependency graph transactions are the
    nodes, and object versions label the edges.lt T,
    lt o, j gt, T?gt ? DEP(H) ? the graph has an edge
    from node T to node T? labeled by lt o, j gt

25
Equivalent and Isolated Histories
  • Two histories for the same set of transactions
    are equivalent if they have the same dependency
    relation (DEP(H) DEP(H?)).
  • A history is said to be isolated if it is
    equivalent to a serial history.
  • The dependencies of a history define a time order
    of the transactions. This ordering is denoted by
    the symbol ltltlt.
  • Tltltlt T? if there is a path in the dependency
    graph from transaction T to transaction T?TltltltH
    T? if lt T, o, T? gt ? DEP(H), or
    TltltltHT? and lt T?, o, T? gt ? DEP(H)
  • All equivalent histories define the same
    ordering.
  • BEFORE(T) T? T?ltltltTAFTER(T) T?
    TltltltT?T? is called a wormhole transaction if
    T?? BEFORE(T) ? AFTER(T)

26
Wormholes Are Not Isolated (1)
  • TltltltT?ltltltT indicates a cycle in the dependency
    graph.
  • Since a dependency graph with cycles is not
    equivalent to any serial history, a history with
    wormholes is not isolated.

27
Wormholes Are Not Isolated (2)
  • No wormhole
    T3,T4,T5,T6 transaction
    are wormhole

    transaction ?No cycles!
    ?Cycles!Note Two transactions not
    related by ltltlt maybe arbitrarily
    scheduled with respect to each
    other.Example T2 and T4

28
Theorems
  1. Wormhole theorem A history is isolated if, and
    only if, it has no wormhole transactions.
  2. Locking theorem If all transactions are
    well-formed and two-phase, then any legal history
    will be isolated.
  3. Locking theorem (converse) If a transaction is
    not well-formed or is not two-phase, then it is
    possible to write another transaction such that
    the resulting pair is a wormhole.
  4. Rollback theorem An update transaction that does
    an UNLOCK and then a ROLLBACK is not two-phase.

29
Wormhole Theorem Proof (1)
  • A history is isolated if, and only if, it has no
    wormhole transactions.
  • Proof1. Isolation ? no wormholes This proof is
    by contradiction.
  • Suppose H is an isolated history of Ti i
    1,.., n ? H is equivalent to some serial
    history SH T1 T2 Tn
  • Suppose that H has a wormholeTltltlt T?ltltlt
    TjltltltHTi ltltlt ltltltT???ltltltT
    ? Let i be the minimum transaction
    index in this wormhole.
  • By the minimality of i, Tj comes completely AFTER
    Ti in SH. ? TjltltltSH Ti is impossible.
  • Since ltltltH and ltltltSH are equivalent, Tj ltltltH Ti
    is not possible either ! ? H has no wormholes.

30
Wormhole Theorem Proof (2)
  • 2. No wormholes ? isolated This proof is by
    induction on the number of transactions, n, that
    appear in history H.
  • (1) If n lt 2, then any history is a serial
    history ? any history is isolated.
  • (2) Suppose the induction hypothesis is true for
    n-1 transactions
  • (3) Consider some history H of n transactions
    that has no wormholes.

31
Wormhole Theorem Proof (3)
  • (a) Pick a transaction T and construct the
    sequence S lt T, T?, gt such that TltltltH
    T? ltltltH Since H has no wormhole, S must
    be finite (otherwise, some T?? must appear
    in S more than once)(b) Let T be the last
    transaction in S. Consider the history
    H? lt lt ti, ai, oi gt ? H ti ? T gt ?
    DEP(H?) lt T, lto, i gt, T? gt ? DEP(H) T? ? T
    (c) H has no wormholes ? H? has no wormholes
    ? H? is isolated (due to (2)) ? H?
    has an equivalent serial history SH?
    T1 T2 Tn-1 (d) DEP(SH) DEP(SH?) ?
    lt T?, lto, igt, T gt ? DEP(H)
    DEP(H?) ? lt T?, lto, igt, T gt ? DEP(H)
    lt T, lt o, i gt, T? gt ?
    DEP(H) T? ? T ? lt T?, lt o, i gt,
    T gt ? DEP(H) DEP(H). Thus, the
    induction step is proven.

32
Serializability Theorem (1)
  • The Wormhole Theorem is the basic result from
    which all the others follow.
  • The Wormhole Theorem can be stated in many
    different ways.
  • Serializability Theorem A history H is isolated
    (also called a serializable schedule or a
    consistent schedule) if, and only if, ltltltH
    implies a partial order of the transactions
    (i.e., no cycles).

33
Serializability Theorem (2)
  • Note If there had been a cycle, it would have
    produced dependency graph arrows pointing up in
    the history (back in time).

34
Locking Theorem Lemma
  • SHRINK(T) ? the index of the first unlock step of
    T is the history H.
  • Lemma Let T and T? be two well-formed and
    2-phase transactions of a legal history H.
    TltltltH T? ? SHRINK(T) lt SHRINK(T?)
  • Proof
  • TltltltH T? ? there are some steps i lt j of H such
    that
  • Hi lt T, a, o gt, Hj lt T?, a?, o gt, and
  • a and a? are conflicting actions.
  • H is legal ? H does not grant conflicting locks
    to two different
    transactions at the same time. ?
    there must be k1 and k2 such that i lt k1 lt k2 lt
    j, Hk1 lt T, UNLOCK, o gt,
    and Hk2 lt T, LOCK, o
    gt.
  • T and T? are 2-phase ? SHRINK(T) ? k1 lt k2 lt
    SHRINK(T?)

35
Locking Theorem Proof
  • Wormhole Theorem A history is isolated if, and
    only if, it has no wormhole transactions.
  • Lemma TltltltHT? ? SHRINK(T) lt SHRINK(T?)
  • Locking Theorem If all transactions are
    well-formed and two-phase, then any legal history
    will be isolated (locking correctly done provides
    the isolation).
  • Proof This proof is by contradiction.
  • Suppose that H is not isolated.Wormhole Theorem
    ? there must be a sequence lt T1,, Tn gt such
    that T1 ltltlt T2 ltltlt ltltlt Tn ltltlt T1
  • Lemma ? SHRINK(T1) lt SHRINK(T2) lt lt SHRINK(T1)
    is a contradiction ? H cannot have any wormholes.

36
Degenerate Transactions
  • A transaction is degenerate if it does any of the
    following
  • Useless lock Locks something that it never reads
    or writes
  • Orphan unlock Unlocks something that it has not
    locked.
  • Orphan lock Ends without unlocking some of its
    locks (impossible if it ends with COMMIT or
    ROLLBACK).

37
Locking Theorem (Converse) Proof
  • Locking Theorem (converse) If a transaction is
    not well-formed or not two-phase, then it is
    possible to write another transaction such that
    the resulting pair has a legal but not isolated
    history (unless the transaction is degenerate).
  • Proof1. Not well-formed ? legal but not
    isolated
  • Suppose that transaction T is not well formed and
    not degenerate T lt lt T,a1, c gt, , ltT, ak, o
    gt, gt ak is a READ or WRITE action that is not
    covered by a lock. The READ case is proved here
    the WRITE case is similar.

38
Locking Theorem (Converse) Proof (2)
  • We can write a transaction T? T? lt lt T?,
    XLOCK, o gt, lt T?, WRITE, o gt, lt T?, WRITE, o gt,
    lt T?, UNLOCK, o gt gt ? T? is 2-phase and
    well-formed.
  • Consider the historyH ltTi iltk gt lt
    T?1, T?2, Tk, T?3, T?4 gt lt Ti i
    gt k gt ? H is legal since no conflicting locks are
    granted on object o at any point of the history.
  • H implies that lt T?, lt o, j gt, T gt and lt T, lt o,
    j gt, T? gt must be in DEP(H) for some j.? Tltltlt
    T?ltltltT ? T is a wormhole.? H is not isolated
    (wormhole theorem).

39
Locking Theorem (Converse) Proof (3)
  • 2. Not two-phase ? legal but not isolated
  • Suppose flat transaction T is not two-phaseT
    lt lt T, a1, o gt, .., lt T, aj, o1 gt, .., lt T, ak,
    o2 gt, ..gt
    ? ?

    UNLOCK SLOCK or XLOCK
  • We can write a transaction T? T? lt lt T?,
    XLOCK, o1 gt, lt T?, XLOCK, o2 gt, lt T?, WRITE, o1
    gt, lt T?, WRITE, o2 gt, lt T?, UNLOCK, o1 gt, lt T?,
    UNLOCK, o2 gt? T? is two-phase and well-formed.

40
Locking Theorem (Converse) Proof (4)
  • Consider the historyH lt Ti i ? j gt T?
    lt Ti i gt j gt? H is legal
  • Since T is not degenerate, it- must read or
    write o1 before the UNLOCK at step j,
  • - must read or write o2 after the LOCK at step
    k. ? lt T, lt o1, m gt, T? gt and lt T?, lt o2, n gt, T
    gt must be in DEP(H).? TltltltH T?ltltltHT? T
    is a wormhole in H.? H is not an isolated
    history (according to the wormhole theorem).

41
Locking Theorem
  • It would be nice if the Locking Theorem were
    stronger and readA history is isolated if, and
    only if, every transaction is well-formed and
    two-phase.Example A serial history is isolated
    even if its transactions are not well-formed or
    two-phase. (In fact, we dont even need locking
    in this case).
  • The best the Locking Theorem can say is,If you
    break the rules, then someone else may come along
    next week and get bad info from you or give you
    bad information.
  • The lack of the only-if part created a cottage
    industry that searched for interesting
    refinements of the theorem.

42
Rollback Theorem
  • Rollback Theorem A transaction that does an
    UNLOCK of an XLOCK and then does a ROLLBACK is
    not well-formed and, consequently, is a potential
    wormhole (unless the transaction is degenerate).
  • Proof
  • T acquires XLOCK on object o and is not
    degenerate ? T writes o
  • The ROLLBACK action has to WRITE (UNDO) object o
    again.
  • The second WRITE is not covered by a lock ? the
    transaction is not well-formed? the transaction
    is a potential wormhole.
  • It might be argued that the ROLLBACK should
    reacquire XLOCK on all such unlocked
    objects.However, that would violate the 2-phase
    restriction and, in doing do, introduce another
    potential wormhole.

43
Summary of the Isolation Theorems
  • The implication of these theorems is that a
    transaction should1. Be well-formed it should
    cover all actions with locks2. Set XLOCK on any
    data it writes.3. Be 2-phase it should not
    release locks until it knows it needs no more
    locks.4. Hold XLOCKs until COMMIT or ROLLBACK.
  • If these rules are followed, the execution
    history will give each transaction the illusion
    it is running in isolation.
  • On the other hand, if any of the above rules are
    violated, then the transactions may have
    inconsistent inputs or outputs.

44
Isolation In Practice
  • Most systems do not provide true isolation.
    Implementers make a compromise between
    correctness and performance.1. Typical system
    default is to ignore READ ? WRITE
    dependencies. ? cursor stability in most SQL
    systems. ? users must request true isolation
    as an option.2. Many systems also allow the
    option to ignore WRITE ? READ isolation.
    ? browse access option allows queries to scan the
    database without acquiring locks and
    without delaying other transactions.

45
Degrees of Isolation (1)
  • Degree 3 This is true isolation.
  • Lock protocol two-phase and well-formed.
  • Degree 2 A 2? isolated transaction has no lost
    updates and no dirty reads.
  • Lock protocol two-phase with respect to XLOCKs
    and well-formed.
  • Common name cursor stability / ignore READ ?
    WRITE /

46
Degrees of Isolation (2)
  • Degree 1 A 1? isolated transaction has no lost
    updates.
  • Lock protocol two-phase with respect to XLOCKs
    and well-formed with
    respect to writes
  • Common name browse mode / ignore READ ? WRITE
    WRITE ? READ /
  • Degree 0 A 0? isolated transaction does not
    overwrite another transactions dirty data if the
    other transaction is 1? or greater.
  • Lock protocol well-formed with respect to writes
  • Common name anarchy / ignore all dependencies /

47
Mixing Isolation Levels
  • A 1? transactions inputs are 1? isolated? the
    transaction may get dirty reads.? such data may
    not satisfy the system consistency
    constraints.? If the transaction uses dirty
    reads to update the database, then other
    transactions will see inconsistent values
    and may malfunction.? such transaction systems
    assume that the 1? transaction knows what
    it is doing.
  • Most systems reserve 1? isolation (browse mode)
    for read-only transactions.

48
SQL systems (1)
  • The lower degrees of isolation allow transactions
    to acquire fewer locks or to hold the locks for a
    shorter time.
  • SQL systems implement a slightly better form of
    isolation than pure 2?.
  • Cursor stability To prevent lost updates, most
    SQL systems keep a shared lock on the record
    currently addressed by a cursor.

49
SQL systems (2)
50
Phantoms (1)
  • Select from EMPwhere eyes blue and
    hair red
  • Case1 locks all the individual records that
    satisfy the where clause (predicate)
  • Problem What is to prevent someone else from
    inserting a new, blue-eyed, red-haired EMP during
    the execution of the above query.

51
Phantoms (2)

  • ---- first match


  • Just

    inserted?

  • current

  • match
  • The record corresponding to Casey is a phantom.
  • Case 2 locks the whole EMP table
  • Problem significantly reduces concurrency.
  • This solution is unacceptable

Name Eyes Hair
Andrew Blue Red
Casey Blue Red
Jane Blue Red
Kathleen Blue Red
52
Predicate Locks (1)
  • Rather than locking an individual object, a
    predicate lock can specify a subset of the
    database to which the lock applies.
  • Example 1 lt t, slock, emp.eyes blue ?
    emp.hair red gt

    transaction t requests
    slocks for all the
    emp tuples that satisfy this predicate
  • lt t, mode, p gt and lt t?, mode?, p? gt are
    compatible if t t?, or mode mode?
    slock, or ( p AND p? ) FALSE / t and t?
    access a different
    subset of the database /

53
Predicate Locks (2)
  • Example 2lt t, slock, emp.eyes blue ?
    emp.hair red gt
  • lt t, xlock, emp.eyes blue gt
  • lt t?, slock, emp.eyes blue gt
  • lt t?, xlock, emp.eyes blue gt
  • Predicate lock is an elegant solution for
    phantoms.

compatible
compatible
incompatible
54
Predicate Locks Scheduling (1)
  • A predicate lock system can schedule transactions
    dynamically as follows
  • Each time a transaction requests a predicate
    lock, the system compares the lock request with
    the other granted and waiting predicate locks.
  • If the lock request is compatible with all other
    requests, it is added to the granted set and
    granted immediately.

55
Predicate Locks Scheduling (2)
  • Otherwise, it is marked as waiting and added to
    the waiting list.
  • Waiting predicates are considered in turn. A
    waiting predicate is granted the lock if it
    becomes compatible with all the current granted
    and waiting predicates.
  • This scheme is very expensive ? not used in
    practice.

56
Granular Locks (1)
  • Predicate locks (predicate comparisons) are too
    expensive. Simplified predicate locks, called
    granular locks, are typically used in practice.
  • In granular locking, the system picks a fixed set
    of predicates (in essence, to pre-compute the
    predicate locks)
  • Each predicate is represented by a name, and
  • The predicates are organized into a lock
    hierarchy.

57
Granular Locks (2)
  • Example
  • A finest granularity lockSites Ripa ? Files
    Phone ? Records Giovanna
  • To lock all the data in Ripa (coarse grain)Sites
    Ripa.

58
Intent Lock Mode (1)
  • When a transaction sets a lock on a granule at a
    given level of the granularity hierarchy, it is
    implicitly locking in the same mode all its
    descendants.
  • Example when a lock is set on a relation, all
    tuples of this relation are
    locked in the same way.
  • It is necessary to guarantee that after setting a
    basic lock (SLOCK, XLOCK) on a given vertex of
    the granularity hierarchy by a transaction T no
    other transaction T? can obtain an incompatible
    lock on any of its predecessors.

59
Intent Lock Mode (2)
  • There must be a way to prevent T? from
    setting Xlock on Ripa.
  • A solution to the above problem Intention locks.
  • Setting an intention lock at a given level
    implies basic locking at all lower levels.
  • The modes of intention locks depend directly on
    the modes of the basic lock.

Transaction T locks the file phone.
Sometime later, T? XLOCKs Ripa
60
Intention Locking
  • Setting a record-granularity SLOCK on Giovanna
    consists of the following sequence of lock
    requests.
  • LOCK IN DATABASE IN INTENT MODELOCK
    NODE RIPA IN INTENT MODELOCK FILE
    PHONE IN INTENT MODELOCK NAME
    GIOVANNA IN SHARED MODE
  • Intent mode represents the intention to set locks
    at a finer granularity.

61
Intention Locks (1)
  • IX Intent to set shared or exclusive locks at
    finer granularity.
  • IS Intent to set shared locks at finer
    granularity.
  • SIX A shared lock on a given granule with intent
    to set finer-granularity shared or
    exclusive locks.

62
Intention Locks (2)
Granted ModeRequest Mode None IS IX S SIX Update X
IS - -
IX - - - -
S - - - -
SIX - - - - -
Update - - - - -
X - - - - - -
  • Update lock will be discussed next.

63
Update Locks (1)
  • A typical construct in programs is to read a
    record and rewrite some of its fields.
  • If two different transactions both read an object
    at nearly the same time, then they both request
    it in exclusive mode. They creates a deadlock
    cycle.
  • A study of system R showed that virtually all
    deadlocks in that system were of this form.

64
Update Locks (2)
  • A solution use UPDATE lock mode.
  • When an SQL cursor is declared with FOR
    UPDATE,record locks acquired by the cursor are
    acquired in UPDATE mode.
  • If the cursor updates the datathen the update
    mode lock is converted to an XLOCK,
    which is held to transaction commit.else the
    update mode lock is downgraded to shared
    mode

65
Update Locks Compatibility (1)
Granted ModeRequest Mode IS IX S SIX Update X
IS -
IX -
S -
SIX -
Update - - - - -
X -
66
Update Locks Compatibility (2)
  • The compatibility matrix for update mode locks is
    asymmetric update is compatible with share, but
    share is not compatible with update.? This
    allows the updater to read but delays other
    transaction reader and updaters, since the
    transaction is about to update.

67
Granular Locking Protocol (1)
  • Acquire locks from root to leaf
  • Release locks from leaf to root.
  • To acquire an S mode or IS mode lock on a
    non-root node, its predecessor must be held in
    IS mode or higher (one of IS, IX, S, SIX, U, X)
  • To acquire an X, U, SIX, or IX mode lock on a
    non-root node, its predecessor must be held in IX
    mode or higher (one of IX, SIX, U, X)

68
Granular Locking Protocol (2)
  • Example
  • Transaction T3 wants to lock the entire database
    in shared mode. It is waiting for T1 and T2 to
    complete.
  • Transaction T2 has record-granularity locks on
    File-2, and is waiting for T1 to release an XLOCK
    on one of these records.

69
Key-Range Locking
  • A key range is denoted by the first key in the
    range and by the first key after the range.
  • Example R, S) ? all k, such that R ? k lt S.
  • A fixed number of key ranges is predetermined
  • Example A, N), N, X), X, ?)
  • Locking protocol
  • A transaction acquires a lock on the key range
    before it accesses records in the key range.
  • Since key ranges are granules within files,
    intent mode locks on the file (and on any
    coarser-granularity objects) must first be
    acquired in accordance with the granular lock
    protocol.
  • Why does this work?
  • If the key ranges are thought of as predicates,
    they are simply predicate locks covering the
    records.? key-range locking also prevents
    phantoms.

70
Dynamic Key-Range Locks (1)
  • The key-range locking described in the last page
    is not adaptive because there are a fixed number
    of key ranges.
  • Dynamic key-range locking assigns a key range to
    every record in the file.

71
Dynamic Key-Range Locks (2)
  • Example The file has three records W, Y, Z.
  • Current key rangesA, W), W, Y), Y, Z), Z,?)
    1
  • After the insertion of X, 1 becomesA, W),
    W, X), X, Y), Y, Z), Z, ?)
  • After the deletion of Y, 1 becomesA,
    W), W, Z), Z, ?)
  • Dynamic key ranges appear and disappear. This
    makes the locking protocol more complex than
    static key-range locking.

72
Dynamic Key-Range Locking Protocol (1)
  • Given a sorted file of three records W, Y, Z.?
    key ranges A, W), W, Y), Y, Z), Z, ?)
  • There are four operations of interest1. Read
    unique read a unique record (say X),
    given its key. Protocol The
    transaction T must SLOCK W, Y)
    preventing anyone else from updating
    that key range.
  • Example If the record is not found, the lock
    prevents a phantom insertion of X
    by another transaction prior to
    Ts commit.

73
Dynamic Key-Range Locking Protocol (2)
  • 2. Read next read the next record,Y, after the
    record W.
  • protocol The transaction T must hold a lock on
    W,Y). (This prevents any phantoms from appearing
    in this key range). All T needs, then, is to
    request a SLOCK on key Y (This implicitly locks
    Y,Z)).
  • 3. Insert insert record X between W and Y.
  • protocol First, the old key range W,Y) (seen to
    be W,X)) must be locked in exclusive mode. Then,
    the new key range X,Y) should be locked in
    exclusive mode.
  • 4. Delete delete record Y.
  • protocol First, lock Y,Z in exclusive mode,
    then lockW,Y) is exclusive mode. When these two
    locks are granted, perform the delete.

74
Dynamic Key-Range Locks Problem (1)
  • Problem If a key-range lock has to wait to be
    granted, then when it is granted,
    the key range may have
    disappeared.
  • Example The file has three records W, Y,
    Z.current key ranges A, W), W, Y), Y, Z),
    Z, ?)
  • 1. locks W, Y) in XLOCK 2. locksX, Y)
    in XLOCK

  • 3. Waits for X, Y)
  • 4. T1 aborts, the key range returns to
    W, Y)
    5. The lock on X, Y) is granted to T2,

    but X, Y) disappears.

T2 reads the record previous to Y
T1 inserts X
T I M E
75
Dynamic Key-Range Locks Problem (2)
  • Solution If the key range has changed, the
    transaction should release the lock and
    request the lock for the new key
    range (i.e., W, Y)).

76
Lock Conversion (1)
  • QuestionCan a transaction requests a look in
    shared mode, and later converts it to exclusive
    mode?
  • The transaction should not release the shared
    lock, and then request the exclusive lock.
  • Reason 2P-locking is violated!

77
Lock Conversion (2)
  • If a lock is held in one mode and requested in a
    second mode, the request is converted to the max
    of the two modes.
  • Example If the lock is held in IX mode and
    requested in U mode, then the request is
    converted to a request for a X mode lock.
  • Note dirty reads might occur.

conversion
Requested mode
Current made
78
Lock Escalation (1)
  • If the system makes a mistake and uses fine
    granularity when coarse granularity is
    appropriate, the lock system may run out of
    storage.
  • 1. Typical solution If the number of locks held
    by a transaction reaches an escalation
    threshold, the lock manager converts
    fine-granularity locks to coarse locks.

79
Lock Escalation (2)
  • Example It looks for an IS lock with a large
    number of children set in S mode,
    converts the parent IS mode lock to
    an S mode, and releases all the
    S mode locks on the children.

IS
S
Page 101
Page 101
Lockescalation
S
S
record 112
record 113
record 134
record134
record113
record112
??
??
The records are implicitly locked in Shared mode.
80
Lock Escalation (3)
  • 2. Rdbs approach It defaults to
    file-granularity locks. If there is any
    contention on the file-granularity lock,
    each holder of the lock de-escalates to
    finer granularity on demand.

81
Hotspots
  • The virtue of page-granularity locking is that
  • It is very easy to implement,
  • It works well in almost all cases, and
  • It can be used to give phantom protection (by
    locking the page on which the phantom would have
    to appear).
  • However, page-granularity locking can create
    hotspots.Examples
  • 1. Directories are typically small, high-traffic
    read-write databases, they can become hotspots.
  • 2. The debit/credit transaction demonstrates
    main-memory hotspotproblems. All the branch
    records (usually less than 100) typically fit in
    a couple of pages.
  • 3. Inserting in a sequential file can give page
    locking systems heartburn, since locking the
    page containing the end of the file prevents any
    other transaction from inserting at the end of
    the file.

82
Sequential Nested Transaction Locking
  • The simplest generalization of flat transactions
    is nested transactions without parallelism,
    called sequential nested transactions, or
    savepoints.
  • The effects of the transaction are made durable
    and public only when the top-level transaction
    invokes COMMIT work.
  • Implementation
  • All subtransactions run under the same TRID.
  • Locks are maintained as a stack. When the
    transaction does a nested BEGIN work, that
    establishes a savepoint in the stack.
  • If the transaction does a ROLLBACK to one of
    these savepoints, all locks subsequent to the
    lock in the savepoint are released.

83
Parallel Nested Transaction Locking (1)
  • The parallel subtransactions are each viewed as
    atomic units (they have A, C, and I, but not D of
    ACID)
  • Each subtransaction gets a unique TRID.
  • The nested transactions are viewed as nodes of
    the transaction family tree.
  • At commit, both inherited and acquired locks are
    given back to the parent, but at ROLLBACK only
    inherited locks are returned.

84
Parallel Nested Transaction Locking (2)
  • When a transaction requests a lock that is
    already held by another transaction, the
    following test is run
  • Is the other transaction in the same family?
  • If yes, is it an ancestor?
  • If yes, inherit 1
  • If no, wait, then inherit. 2
  • If no, wait. 3

acquired
1
3
2
Family F2
Family F1
85
Deadlock Avoidance vs. Toleration (1)
  • Deadlock A situation in which each transaction
    of the deadlock set is waiting
    for another member of the set.
    These transactions will not
    advance unless one transaction
    stops waiting. In this situation, one of
    the transactions must rollback.

86
Deadlock Avoidance vs. Toleration (2)
  • Solutions
  • 1. Deadlock avoidance
  • Linearly ordering resources and requesting them
    only in that order (avoiding wait-for cycles).
  • Asking jobs to predeclare their maximum needs.
  • 2. Deadlock detection
  • Whenever a transaction waits more than a certain
    time, declare that something is wrong (very
    pessimistic) and rollback that transaction.Exampl
    es IBMs CICS, Tandems Encompass, NonStop SQL
    Systems.
  • After a transaction times out, the system runs a
    deadlock detector that looks for cycles in the
    wait-for graph.

87
Wait-for Graph and Deadlock Detection (1)
  • 1. Lock data structures
  • Each lock L has two lists, the granted list and
    the waiting list.
  • It convenient to have a quick way of finding all
    the places a particular transaction is waiting.
    Therefore, a transaction wait list, with an entry
    for each active transaction, is maintained.

88
Wait-for Graph and Deadlock Detection (2)
  • 2. Wait-for graph The edge T ? T? should be
    added to the wait-for graph if T in the waiting
    list and T? in the waiting or granted list meet
    two conditions
  • T? is in the granted list or is ahead of T is the
    waiting list, and
  • Modes m and m? are incompatible.

l2
l1
granted list
waiting list
89
Wait-for Graph and Deadlock Detection (3)
  • 3. Deadlock detection
  • The graph can be examined for cycles by
    doing a depth-first search that runs in time
    proportional to the number of edges.

90
Distributed Deadlock (1)
  • Problem The transaction wait list is
    distributed. Typically, each
    site maintains a local fragment
    of the transaction wait list
    containing all transaction waiting at that
    node.

91
Distributed Deadlock (2)
  • Solution Path Pushing
  • Each site with a wait-for edge pointing to a
    transaction in a second site traces back from
    that edge to source transactions.
  • It then sends (pushes) the resulting graph to the
    target site.
  • The target site adds these edges and iterates the
    logic.
  • If there is a cycle, one or more sites in the
    cycle's path will find it.

92
Distributed Deadlock (3)
  • Note
  • There is no standard representation of the
    wait-for graph.
  • Not all RMs provide their part of the wait-for
    graph.? Implementing a global deadlock detector
    may not be possible.

93
Phantom Deadlocks (1)
  • Since the wait-for graph is constantly changing,
    it is not possible to get a consistent picture of
    the graph.
  • Phantom deadlocks cycles in the graph caused by
    transient arcs.

94
Phantom Deadlocks (2)
  • Example
  • At time t1 T T?
    T T? Site 1
    Site 2
    Site3
  • (T? is finishing (collecting
    subgraphs) (serving T) writing the log)
  • At time t2
  • Good news the deadlock detector will not miss
    any real deadlocks.

Collecting subgraphs
log lock
T? request a service at site 3
Collecting subgraph
T T?
T T?
Site1
Site2
Site3
(completed the log operation for T?, currently
serving T)
(a phantom deadlockis detected)
(serving T)
95
Deadlock Resolution
  • Once a deadlock is found, it must be broken.?
    The standard resolution technique is to
    roll back the least expensive transactions
    the ones with the shortest logs.

96
Deadlock Summary
  • Wait-for graphs have the following
    characteristics
  • 1. Sparse (waits are very rare)
  • 2. Most cycles are very short (two)
  • 3. Cycles are very rare
  • Most systems use the following approach
  • 1. The system falls back on timeout, plus
  • 2. A simple local deadlock detector that detects
    local deadlocks.

97
Probability of Deadlock (1)
  • A very simple model
  • The system consists of R records.
  • There are n1 concurrent transactions.
  • Each transaction performs r 1 actions. Each
    action accesses a random record from the set of R
    records.
  • nrltltR that is, most of the database is unlocked
    most of the time.

98
Probability of Deadlock (2)
  • Analysis
  • The probability that a single request waits isPW
    ( n r/2 ) / R n r / 2R
  • The probability that a particular transaction T,
    waits in its life time isPW(T) 1 ( 1 PW
    )r 1 1


Avg num of locks held by each transaction
The probability that a single request does not
have to wait.
nrltltR ? PWltlt1 ? these high order
terms can be dropped
PW(T) nr2 / 2R
99
Probability of Deadlock (3)
  • The probability that some transaction T is
    deadlockPD(T) PW(T)2 / n ?1 PW(T)3 ?2
    PW(T)4 ??
    T1
    T2

PW(T) prob for waiting for some transaction
In general, the probability of a cycle of length
I is proportional to PW(T)iSince we typically
have PW(T)lt0.1? These terms can be dropped.
1/n PW(T) prob for waiting for a specific
transaction (i.e., T1)
100
Probability of Deadlock (4)
  • The probability that any transaction
    deadlocksPD PD(T1) PD(T2) ??
    PD(Tn1)PD ? n PD(T) n2 r4 / 4R2

Multiprogramming level must be kept low
Fine-granularity locking, browse mode locking and
shared locks are important. They all tend to
reduce the transaction collision cross sections.
deadlocks are very rare
101
Reducing the transactions collision cross
section (1)
  • Transactions collision cross section is the
    time-space product of the amount of data the
    transaction locks and the lock duration.
  • Reducing the transactions collision cross
    section? minimizes lock waits? increases
    concurrency reduces deadlocks

102
Reducing the transactions collision cross
section (2)
  • T2 and T3 occur concurrently. However, they are
    isolated because they access different data.
  • T3 and T4 access the same data. However, they are
    isolated because they occur during time spans.
  • Since T2 collides with T1, isolation problems can
    occur.

Collision Cross Section
Time
T2
T3
T1
T4
Locks
103
Field Calls (1)
  • Field calls have been the most successful
    technique to minimize the collision cross
    section.
  • Field call is an action on a specific hotspot
    record. The action consists of two parts a
    predicate a transform.
  • Example update inventory set
    quantity-on-hand quantity-on-hand - delta
    where item MS Windows
    and quantity-on-hand ?
    deltapredicate quantity-on-hand ?
    deltatransform quantity-on-hand
    quantity-on-hand - delta

104
Field calls (2)
  • 1. The record corresponding to MS Windows is
    locked in short duration shared mode.
  • 2. The predicate is tested against the MS
    Windows record, and the shared lock is
    immediately released afterward.
  • 3. If the predicate is true, the system generates
    a REDO log record to be performed at a
    transaction commit. The REDO log record contains
    the predicate and the transform.
  • 4. At commit (phase 1), all deferred REDO log
    records are examined.
  • Shared locks are acquired on predicate only
    records,
  • and exclusive locks are acquired on records
    mentioned in transforms.
  • 5. Each predicate is again tested at commit phase
    1. If the predicates are still true at phase 1,
    then at commit phase 2, each transform is applied
    and the locks are freed.

105
Field calls Advantages
Checkout Clerk C1
Checkout Clerk C2
update inventory set qoh qoh - delta where
item milk AND qoh ? delta
Stocking Clerk S
-5
-10
300
C2 updates
S updates
C1 updates
Milk
1000
Milk
990
Milk
1290
Milk
1285
Original qoh
time
C1
S
C2
C2
S
C1
C2
S
C1
C1
S
C2
106
Field calls Recovery (1)
  • Field calls never have any transaction undo work.
    If the transaction aborts, the field calls have
    not made any changes to the database thus, they
    are already undone.
  • Redo of field calls is a little more subtle.
  • Since transformations are deferred to phase 2 of
    commit, it is safest to delay inserting field
    call transforms into the REDO log until the end
    of phase 1 of commit.
  • Reason Since transactions are inserting field
    call transforms will appear in the order that
    they were applied (rather than the order in which
    the field calls themselves were issued).

107
Field calls Recovery (2)
hotspot record h
t
t?
transaction T
transaction T
Example
  • time transaction T transaction T?
    log 1. t(h)
    , t(h) 2.
    t?(h) , t(h),
    t?(h) 3.
    commit 4.
    commit
    redo order- Effect(commit order) t(t?(h))-
    System crashed, the redo logic reconstructed the
    value of h t?(t(h)).- Note commit order ?
    redo order If t?(t(h)) ? t(t?(h)) ?
    the wrong value is reconstructed.

108
Escrow Locking
  • Refinement of field callsIf we can preserve the
    truth of the predicate between the time the
    transaction first makes the field call and the
    time the predicate is revaluated at phase 1 of
    commit, then we can prevents transaction
    predicate failure at commit, and the consequent
    transaction aborts.

Phase1commit
Phase 2 commit
Field call
time
Re-evaluate predicate
predicate test
T1 ends
T1 begins
Try to preserve the predicate
109
Escrow Locking (2)
  • Idea
  • Associated with each hotspot field is a range of
    values. m, n
  • For each new field call, the predicate is tested
    against both m and n.

The new value if all the pending field call
transforms are applied.
Note m n if there is no pending field call.
The current value (in the database)
110
Escrow Locking (2)
  • Note we need to test the predicates against both
    end values because the operator could be a
    (instead of - ).

111
Optimistic Concurrency Control
  • Conventional locking techniques are pessimistic
  • They assume a high degree of contention among
    concurrent transactions.
  • A certain degree of checking is done before a
    database operation can be executed.
  • Optimistic concurrency control techniques do not
    perform any locking at all. A transaction is
    composed of three phases
  • Read Phase Updates are applied only to local
    copies of the data items kept in the transaction
    workspace.
  • Validation Phase Checking is performed to ensure
    that serializability will not be violated if the
    transaction updates are applied to the database.
  • Write Phase
  • If the validation is successfulthen the updates
    are applied to the databaseelse the updates are
    discarded and the transaction is restarted.

112
Optimistic Concurrency Control Validation
  • The validation phase for Ti checks that, for each
    such transaction Tj that is committed or is in
    its validation phase, one of the following
    conditions holds
  • 1.Transaction Tj completes its write phase before
    Ti starts its read phase.
  • 2.Ti starts its write phase after Tj completes
    its write phase, and the read-set of Ti has
    no items in common with the write-set of Tj.
  • 3.Tj completes its read phase before Ti completes
    its read phase, and both read-set and
    write-set of Ti have no items in common with
    the write-set of Tj.
  • Note If any one of these three conditions holds,
    there is no interference and Ti is
    validated successfully.

113
Optimistic Concurrency Control Validation (1)
  • The validation phase for Ti checks that for each
    such transaction Tj that is committed or is in
    its validation phase, one of the following
    conditions holds
  • 1.Transaction Tj completes its write phase before
    Ti starts its read phase. Tj
    Ti
  • 2.Ti starts its write phase after Tj completes
    its write phase, and the read-set of Ti has
    no items in common with the write-set of Tj
    (no Write ? Read dependencies) Tj
    Ti

R
V
W
R
V
W
R
V
W
R
V
W
114
Optimistic Concurrency Control Validation (2)
  • 3. Both the read-set and the write-set of Ti have
    no
  • items in common with the write-set of Tj
    (no Read ? Write, Write ? Write
    dependencies), and Tj completes its read
    phase before Ti completes its read phase.
    Tj Ti

R
V
W
R
V
W
115
Optimistic Concurrency Control Problem
  • Advantage No checks are made while the
    transaction is running
    ? locking is not required.
  • Disadvantage
  • When interference is significant, this approach
    is worse than conventional locking
  • Conventional locking would serialize the
    transactions, causing them to wait for one
    another.
  • Optimistic techniques would let all the
    concurrent transactions run and then abort all
    but one of them.
  • Field calls plus escrow reads is better is
    general.

116
Ti
Write a Comment
User Comments (0)
About PowerShow.com