Cs4432 database systems ii
This presentation is the property of its rightful owner.
Sponsored Links
1 / 55

CS4432: Database Systems II PowerPoint PPT Presentation


  • 42 Views
  • Uploaded on
  • Presentation posted in: General

CS4432: Database Systems II. Concurrency Control. Chapter 18 Concurrency Control. T1T2…Tn. DB (consistency constraints). Example:. T1:Read(A)T2:Read(A) A  A+100A  A  2 Write(A)Write(A) Read(B)Read(B) B  B+100B  B  2 Write(B)Write(B)

Download Presentation

CS4432: Database Systems II

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Cs4432 database systems ii

CS4432: Database Systems II

Concurrency Control

concurrency control


Chapter 18 concurrency control

Chapter 18Concurrency Control

T1T2…Tn

DB

(consistency

constraints)

concurrency control


Example

Example:

T1:Read(A)T2:Read(A)

A  A+100A  A2

Write(A)Write(A)

Read(B)Read(B)

B  B+100B B2

Write(B)Write(B)

Constraint: A=B

concurrency control


A schedule

A schedule

An ordering of operations inside

one or more transactions over time

Why interleave operations?

concurrency control


Schedule a

AB

2525

125

125

250

250

250250

Schedule A

T1T2

Read(A); A  A+100

Write(A);

Read(B); B  B+100;

Write(B);

Read(A);A  A2;

Write(A);

Read(B);B  B2;

Write(B);

concurrency control


Schedule b

AB

2525

50

50

150

150

150150

Schedule B

T1T2

Read(A);A  A2;

Write(A);

Read(B);B  B2;

Write(B);

Read(A); A  A+100

Write(A);

Read(B); B  B+100;

Write(B);

concurrency control


Serial schedules

Serial Schedules !

  • Any serial schedule is “good”.

concurrency control


Interleave transactions in a schedule

Interleave Transactionsina Schedule?

concurrency control


Schedule c

AB

2525

125

250

125

250

250250

Schedule C

T1T2

Read(A); A  A+100

Write(A);

Read(A);A  A2;

Write(A);

Read(B); B  B+100;

Write(B);

Read(B);B  B2;

Write(B);

concurrency control


Schedule d

AB

2525

125

250

50

150

250150

Schedule D

T1T2

Read(A); A  A+100

Write(A);

Read(A);A  A2;

Write(A);

Read(B);B  B2;

Write(B);

Read(B); B  B+100;

Write(B);

concurrency control


Schedule e

AB

2525

125

125

25

125

125125

Same as Schedule D

but with new T2’

Schedule E

T1T2’

Read(A); A  A+100

Write(A);

Read(A);A  A1;

Write(A);

Read(B);B  B1;

Write(B);

Read(B); B  B+100;

Write(B);

concurrency control


What is a good schedule

What is a ‘good’ schedule?

concurrency control


Cs4432 database systems ii

  • We want schedules that are “good” regardless of :

    • initial state and

    • transaction semantics

  • Hence we consider only :

    • order of read/writes

      Example:

      Sc=r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B)

  • concurrency control


    Remember schedule c

    Remember Schedule C

    T1T2

    Read(A); A  A+100

    Write(A);

    Read(A);A  A2;

    Write(A);

    Read(B); B  B+100;

    Write(B);

    Read(B);B  B2;

    Write(B);

    concurrency control


    Cs4432 database systems ii

    Example:

    Sc=r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B)

    Sc’=r1(A)w1(A) r1(B)w1(B)r2(A)w2(A)r2(B)w2(B)

    T1 T2

    concurrency control


    Now let s try that for schedule d

    Now Let’s Try that for Schedule D !!!

    T1T2

    Read(A); A  A+100

    Write(A);

    Read(A);A  A2;

    Write(A);

    Read(B);B  B2;

    Write(B);

    Read(B); B  B+100;

    Write(B);

    concurrency control


    Cs4432 database systems ii

    Now for Sd:

    Sd=r1(A)w1(A)r2(A)w2(A) r2(B)w2(B)r1(B)w1(B)

    Sd=r1(A)w1(A) r1(B)w1(B)r2(A)w2(A)r2(B)w2(B)

    concurrency control


    Cs4432 database systems ii

    Or, let’s try for Sd:

    Sd=r1(A)w1(A)r2(A)w2(A) r2(B)w2(B)r1(B)w1(B)

    Sd=r2(A)w2(A)r2(B)w2(B) r1(A)w1(A)r1(B)w1(B)

    concurrency control


    Cs4432 database systems ii

    In short, Schedule D cannot be “fixed” :

    Sd=r1(A)w1(A)r2(A)w2(A) r2(B)w2(B)r1(B)w1(B)

    • as a matter of fact, there seems to

    • be no safe way to transform this Sd

    • into an equivalent serial schedule !?

    concurrency control


    Cs4432 database systems ii

    • We note about schedule D:

    • T2 T1

    • T1 T2

    T1 T2

    Sd cannot be rearranged

    into a serial schedule

    Sd is not “equivalent” to

    any serial schedule

    Sd is “bad”

    concurrency control


    Returning to sc

    Returning to Sc

    Sc=r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B)

    T1 T2 T1 T2

     no cycles  Sc is “equivalent” to a

    serial schedule,

    I.e., in this case (T1,T2).

    concurrency control


    Concepts

    Concepts

    Transaction: sequence of ri(x), wi(x) actions

    Conflicting actions: read/write on same resource A:

    r1(A) w2(A) w1(A)

    w2(A) r1(A) w2(A)

    Schedule: represents chronological order in which actions are executed

    Serial schedule: no interleaving of trans/actions

    concurrency control


    Anomalies with interleaving

    Anomalies with Interleaving

    Reading Uncommitted Data (WR Conflicts, “dirty reads”):e.g. T1: A+100, B+100, T2: A*1.06, B*1.06

    T1: R(A), W(A), R(B), W(B), Abort

    T2:R(A), W(A), C

    Unrepeatable Reads (RW Conflicts): E.g., T1: R(A), check if A >0, decrement, T2: R(A), decrement

    T1:R(A),R(A), W(A), C

    T2:R(A), W(A), C

    • Overwriting Uncommitted Data (WW Conflicts):

    T1:W(A), W(B), C

    T2:W(A), W(B), C


    Definition

    Definition

    S1, S2 are conflict equivalentschedules

    if S1 can be transformed into S2 by a series of swaps on non-conflicting actions.

    concurrency control


    Definition1

    Definition

    A schedule is conflict serializable

    if it is conflict equivalent

    to some serial schedule.

    concurrency control


    How determine this

    How determine this ?

    Answer: A Precedence Graph !

    concurrency control


    Precedence graph p s s is schedule

    Precedence graph P(S) (S is schedule)

    Nodes: transactions in S

    Arcs: Ti  Tj whenever

    - pi(A), qj(A) are actions in S

    - pi(A) <S qj(A)

    - at least one of pi, qj is a write

    concurrency control


    Exercise

    Exercise:

    • What is P(S) forS = w3(A) w2(C) r1(A) w1(B) r1(C) w2(A) r4(A) w4(D)

    • Is S conflict-serializable?

    concurrency control


    Another exercise

    Another Exercise:

    • What is P(S) forS = w1(A) r2(A) r3(A) w4(A) ?

    • Is S conflict-serializable?

    concurrency control


    Lemma

    Proof:

    Assume P(S1)  P(S2)

     Ti: Ti  Tj in S1 and not in S2

     S1 = …pi(A)... qj(A)… pi, qj

    S2 = …qj(A)…pi(A)... conflict

     S1, S2 not conflict equivalent

    Lemma

    S1, S2 conflict equivalent  P(S1)=P(S2)

    concurrency control


    Cs4432 database systems ii

    Counter example:

    S1=w1(A) r2(A) w2(B) r1(B)

    S2=r2(A) w1(A) r1(B) w2(B)

    Note: P(S1)=P(S2)  S1, S2 conflict equivalent

    concurrency control


    Theorem

    Theorem

    P(S1) acyclic  S1 conflict serializable

    () Assume S1 is conflict serializable

     Ss: Ss, S1 conflict equivalent

     P(Ss)=P(S1)

     P(S1) acyclic since P(Ss) is acyclic

    concurrency control


    Theorem1

    Theorem

    P(S1) acyclic  S1 conflict serializable

    T1

    T2 T3

    T4

    () Assume P(S1) is acyclic

    Transform S1 as follows:

    (1) Take T1 to be transaction with no incident arcs

    (2) Move all T1 actions to the front

    S1 = ……. qj(A)…….p1(A)…..

    (3) we now have S1 = < T1 actions ><... rest ...>

    (4) repeat above steps to serialize rest!

    concurrency control


    How to enforce serializable schedules

    How to enforce serializable schedules?

    concurrency control


    How to enforce serializable schedules1

    How to enforce serializable schedules?

    Option 1: try all possible swaps of non-conflicting operation pairs to determine if the schedule can be turned into a serial one, I.e., if the schedule is ‘good’.

    concurrency control


    How to enforce serializable schedules2

    How to enforce serializable schedules?

    Option 2: run system, recording P(S);

    at end of day, check for P(S) cycles and declare if execution was good

    concurrency control


    How to enforce serializable schedules3

    How to enforce serializable schedules?

    Option 3: prevent P(S) cycles from occurring

    T1 T2 …..Tn

    Scheduler

    DB

    concurrency control


    A locking protocol

    A locking protocol

    Two new actions:

    lock (exclusive):li (A)

    unlock:ui (A)

    T1 T2

    lock

    table

    scheduler

    concurrency control


    Rule 1 well formed transactions

    Rule #1: Well-formed transactions

    Ti: … li(A) … pi(A) … ui(A) ...

    concurrency control


    Rule 2 legal scheduler

    Rule #2 Legal scheduler

    S = …….. li(A) ………... ui(A) ……...

    no lj(A)

    concurrency control


    Cs4432 database systems ii

    Exercise:

    • What schedules are legal?What transactions are well-formed?

      S1 = l1(A)l1(B)r1(A)w1(B)l2(B)u1(A)u1(B)

      r2(B)w2(B)u2(B)l3(B)r3(B)u3(B)

      S2 = l1(A)r1(A)w1(B)u1(A)u1(B)

      l2(B)r2(B)w2(B)l3(B)r3(B)u3(B)

      S3 = l1(A)r1(A)u1(A)l1(B)w1(B)u1(B)

      l2(B)r2(B)w2(B)u2(B)l3(B)r3(B)u3(B)

    concurrency control


    Cs4432 database systems ii

    Exercise:

    • What schedules are legal?What transactions are well-formed?

      S1 = l1(A)l1(B)r1(A)w1(B)l2(B)u1(A)u1(B)

      r2(B)w2(B)u2(B)l3(B)r3(B)u3(B)

      S2 = l1(A)r1(A)w1(B)u1(A)u1(B)

      l2(B)r2(B)w2(B)l3(B)r3(B)u3(B)

      S3 = l1(A)r1(A)u1(A)l1(B)w1(B)u1(B)

      l2(B)r2(B)w2(B)u2(B)l3(B)r3(B)u3(B)

    concurrency control


    Cs4432 database systems ii

    Schedule F : Adding locking ???

    T1 T2

    l1(A);Read(A)

    A A+100;Write(A);u1(A)

    l2(A);Read(A)

    A Ax2;Write(A);u2(A)

    l2(B);Read(B)

    B Bx2;Write(B);u2(B)

    l1(B);Read(B)

    B B+100;Write(B);u1(B)

    concurrency control


    Cs4432 database systems ii

    Schedule F

    A B

    T1 T2 25 25

    l1(A);Read(A)

    A A+100;Write(A);u1(A) 125

    l2(A);Read(A)

    A Ax2;Write(A);u2(A) 250

    l2(B);Read(B)

    B Bx2;Write(B);u2(B) 50

    l1(B);Read(B)

    B B+100;Write(B);u1(B) 150

    250 150

    concurrency control


    Rule 3 two phase locking 2pl for transactions

    Rule #3 Two phase locking (2PL)for transactions

    Ti = ……. li(A) ………... ui(A) ……...

    no unlocks no locks

    concurrency control


    Cs4432 database systems ii

    # locks

    held by

    Ti

    Time

    Growing Shrinking

    Phase Phase

    concurrency control


    Cs4432 database systems ii

    Schedule F : Does it follow 2PL ?

    T1 T2

    l1(A);Read(A)

    A A+100;Write(A);u1(A)

    l2(A);Read(A)

    A Ax2;Write(A);u2(A)

    l2(B);Read(B)

    B Bx2;Write(B);u2(B)

    l1(B);Read(B)

    B B+100;Write(B);u1(B)

    concurrency control


    Schedule g

    Schedule G

    T1 T2

    l1(A);Read(A)

    A A+100;Write(A)

    l1(B); u1(A)

    l2(A);Read(A)

    A Ax2;Write(A);

    concurrency control


    Schedule g1

    Schedule G

    T1 T2

    l1(A);Read(A)

    A A+100;Write(A)

    l1(B); u1(A)

    l2(A);Read(A)

    A Ax2;Write(A);l2(B)

    delayed

    concurrency control


    Cs4432 database systems ii

    Schedule G

    T1 T2

    l1(A);Read(A)

    A A+100;Write(A)

    l1(B); u1(A)

    l2(A);Read(A)

    A Ax2;Write(A);l2(B)

    Read(B);B B+100

    Write(B); u1(B)

    delayed

    concurrency control


    Cs4432 database systems ii

    Schedule G

    T1 T2

    l1(A);Read(A)

    A A+100;Write(A)

    l1(B); u1(A)

    l2(A);Read(A)

    A Ax2;Write(A);l2(B)

    Read(B);B B+100

    Write(B); u1(B)

    l2(B); u2(A);Read(B)

    B Bx2;Write(B);u2(B);

    delayed

    concurrency control


    We got the good schedule

    AB

    2525

    125

    250

    125

    250

    250250

    We Got the GOOD Schedule !!!!

    T1T2

    Read(A); A  A+100

    Write(A);

    Read(A);A  A2;

    Write(A);

    Read(B); B  B+100;

    Write(B);

    Read(B);B  B2;

    Write(B);

    concurrency control


    Schedule h t 2 reversed

    Schedule H (T2 reversed)

    T1T2

    l1(A); Read(A) l2(B);Read(B)

    A A+100;Write(A) B Bx2;Write(B)

    … …

    concurrency control


    Schedule h t 2 reversed1

    Schedule H (T2 reversed)

    T1T2

    l1(A); Read(A) l2(B);Read(B)

    A A+100;Write(A) B Bx2;Write(B)

    l1(B) l2(A)

    delayed

    delayed

    concurrency control


    Cs4432 database systems ii

    • SUMMARY:

      • 2PL is commonly used solution for concurrency

    • NOTE:

      • But 2PL does not prevent deadlocks

    • NEXT:

      • What are we going to do about deadlocks?

    concurrency control


  • Login