Cs4432 database systems ii
Download
1 / 55

CS4432: Database Systems II - PowerPoint PPT Presentation


  • 71 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'CS4432: Database Systems II' - clare


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 18 Concurrency Control

T1 T2 … Tn

DB

(consistency

constraints)

concurrency control


Example
Example:

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

A  A+100 A  A2

Write(A) Write(A)

Read(B) Read(B)

B  B+100 B  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

A B

25 25

125

125

250

250

250 250

Schedule A

T1 T2

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

A B

25 25

50

50

150

150

150 150

Schedule B

T1 T2

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

A B

25 25

125

250

125

250

250 250

Schedule C

T1 T2

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

A B

25 25

125

250

50

150

250 150

Schedule D

T1 T2

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

A B

25 25

125

125

25

125

125 125

Same as Schedule D

but with new T2’

Schedule E

T1 T2’

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



Cs4432 database systems ii

  • 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

    T1 T2

    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 !!!

    T1 T2

    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

    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 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

    A B

    25 25

    125

    250

    125

    250

    250 250

    We Got the GOOD Schedule !!!!

    T1 T2

    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)

    T1 T2

    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)

    T1 T2

    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