cs4432 database systems ii
Download
Skip this Video
Download Presentation
CS4432: Database Systems II

Loading in 2 Seconds...

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

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

slide13
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

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

slide15

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

slide17
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

slide18
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

slide19
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

slide20

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

slide31

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

slide41

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

slide42

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

slide43

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

slide44

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

slide46
# locks

held by

Ti

Time

Growing Shrinking

Phase Phase

concurrency control

slide47

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

slide50

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

slide51

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

slide55
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

ad