# Algorithm 2  Embed code:

## Algorithm 2

Download Presentation - The PPT/PDF document "Algorithm 2" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.

### Presentations text content in Algorithm 2

Slide1

Algorithm 2

Algorithm 2 Problem

Apply Algorithm 2 to the Schedule of Transactions on the next Slide

In this Case, Wlock Implies Reading

For this Problem:

Draw the Precedence Graph

Check to See if the Schedule is Serializable

If So, Determine ALL Serial Schedules

Slide2

Input: Schedule S for Transactions

T

1

, T

2

, …

T

k

Output: Is S Serializable? If so, Serial Schedule

Method: Create a Directed Precedence Graph G:

Suppose in S,

T

i

:

Rlock

A.

If

T

j

:

Wlock

A is the Next Transaction to

Wlock

A (if it exists) then place an Arc from

T

i

to

T

j

.

Repeat

for all

T

i

’s

, all

Rlocks

before

Wlock

on A!

Suppose in S,

T

i

:

Wlock

A.

If

T

j

:

Wlock

A is the Next Transaction to

Wlock

A (if it exists) then place an Arc from

T

i

to

T

j

.

If Also exists

T

m

:

Rlock

A after

T

i

:

Wlock

A but before

T

j

:

Wlock

A, then Draw an Arc from

T

i

to T

m

.

Review the Resulting Precedence Graph

If G has Cycles - Non-Serializable

If G is Acyclic - Topological Sort for Serial Schedule

Slide3

Apply Algorithm 2 to Following Schedule

T

1

T

2

T

3

T

4

(1)

Rlock

A

(2)

Rlock

A

(3)

Wlock

C

(4) Unlock C

(5)

Rlock

C

(6)

Wlock

B

(7) Unlock B

(8)

Rlock

B

(9) Unlock A

(10) Unlock A

(11)

Wlock

A

(12)

Rlock

C

(13)

Wlock

D

(14) Unlock B

(15) Unlock C

(16)

Rlock

B

(17) Unlock A

(18)

Wlock

A

(19) Unlock B

(20)

Wlock

B

(21) Unlock B

(22) Unlock D

(23) Unlock C

(24) Unlock A

Slide4

Apply Algorithm 2 to Following Schedule

T1 T2 T3 T4 (1) Rlock A(2) Rlock A(3) Wlock C(4) Unlock C(5) Rlock C(6) Wlock B(7) Unlock B(8) Rlock B(9) Unlock A(10) Unlock A(11) Wlock A(12) Rlock C(13) Wlock D(14) Unlock B(15) Unlock C(16) Rlock B(17) Unlock A(18) Wlock A(19) Unlock B(20) Wlock B(21) Unlock B(22) Unlock D(23) Unlock C(24) Unlock A

Look for

Next T to

Wlock

A

For Each

Rlock

T

1

:Rlock AT2 :Rlock A

For

Rlock CsNo Wlock C

For Rlock BNext to toWlock?

For

T

1 :Wlock B Look forNext T toWlock B

Since Rlock BetweenAdd Arcfrom T1 to T2

T

1

before T

3,

T

2

before

T

3,

T

2

before

T

4

T

2

before

T

4,

T

1

before

T

4,

T

1

before

T

2

Slide5

Algorithm 2 Solution

A:RW

T

1

T

3

T

4

T

2

B:WR

A:RW

B:RW

B:WW

A:WW

Precedence Graph Below

Equivalent Serial Schedule via Topological Sort is

T1, T2, T3, T4

Note there may be multiple Serial Schedules

Slide6

Algorithm 3 – Steps 1 to 4

Input: Schedule S for Transactions

T

1

, T

2

, …

T

k

Output: Is S Serializable? If so, Serial Schedule

Method: Create a Directed Polygraph Graph P:

Augment

S with Dummy T

o

(Write Every Item) an Dummy

T

f

Create

Initial Polygraph P by Adding Nodes for T

o

,

T

f

, and Each

T

i

Transaction , in S

Place

an Arc from

T

i

to

T

j

Whenever

T

j

Reads A in Augmented S (with Dummy States) that was Last Written by

T

i

.

Write

to

for Each Item

Repeat

this Step for all

Arcs.

Don’t

Forget to Consider Dummy States!

Discover

Useless Transactions - T is Useless if there is no Path from T to

T

f

This is the “Initialization” Phase of Algorithm 3

Slide7

Algorithm 3 – Steps 5 to 7

Method: Reassess the Initial Polygraph P:

For

Each Remaining Arc

T

i

W to

T

j

R(meaning

that

T

j

T

i

)

Consider

all

T

T

o

and T

T

f

that

also

Writes A:

I. If

T

i

= T

o

and

T

j

=

T

f

II. If

T

i

= T

o

and

T

j

T

f

T

j

to T

III. If

T

i

T

o

and

T

j

=

T

f

to

T

i

IV. If

T

i

T

o

and

T

j

T

f

then Add Arc Pair from T

to

T

i

and

T

j

to T

Determine

if P is Acyclic by “Choosing” One Transaction Arc for Each Pair - Make Choices Carefully

If

Acyclic - Serializable - Perform Topological Sort without T

o ,

T

f

for Equivalent Serial Schedule. Else - Not Serializable

Slide8

Algorithm 3 – Key Steps

Method: Reassess the Initial Polygraph P:

Place an Arc from

T

i

to

T

j

Whenever

T

j

Reads A in Augmented S (with Dummy States) that was Last Written by

T

i

.

Write

to

for Each Item

Repeat this Step for all Arcs.

Don’t Forget to Consider Dummy States!

Discover Useless Transactions - T is Useless if there is no Path from T to

T

f

For

Each Remaining Arc

T

i

W to

T

j

R(meaning

that

T

j

T

i

)

Consider

all

T

T

o

and T

T

f

that

also

Writes A:

I. If

T

i

= T

o

and

T

j

=

T

f

II. If

T

i

= T

o

and

T

j

T

f

T

j

to T

III. If

T

i

T

o

and

T

j

=

T

f

to

T

i

IV. If

T

i

T

o

and

T

j

T

f

then Add Arc Pair from T

to

T

i

and

T

j

to T

Determine

if P is Acyclic by “Choosing” One Transaction Arc for Each Pair - Make Choices

Carefully

Slide9

Apply Algorithm 3 to Following Schedule

T

1

T

2

T

3

T

4

(T

0

) Write

A

Write B

(1)

Wlock

A

(2)

Rlock

B

(3) Unlock A

(4)

Rlock

A

(5) Unlock B

(6)

Wlock

B

(7)

Rlock

A

(8) Unlock B

(9)

Wlock

B

(10) Unlock A

(11) Unlock A

(12)

Wlock

A

(13) Unlock B

(14)

Rlock

B

(15) Unlock A

(16) Unlock B

(T

F

Slide10

T1 T2 T3 T4 (T0) Write A Write B(1) Wlock A(2) Rlock B(3) Unlock A(4) Rlock A(5) Unlock B(6) Wlock B(7) Rlock A(8) Unlock B(9) Wlock B(10) Unlock A(11) Unlock A(12) Wlock A(13) Unlock B(14) Rlock B(15) Unlock A(16) Unlock B(TF) Read A, Read B

Apply Algorithm 3 to Following Schedule

T

3

before T

1,

T

3

before T2, T4 before TfT0 before T4, T1 before T2, T1 before Tf

Remove Useless Arcs

Slide11

Precedence Graph after Steps 1 to 4Remove UselessTrans.

Algorithm 3 Solution

T

4

T

3

T

2

T

1

T

0

T

f

A:WR

A:WR

B:WR

B:WR

B:WR

A:WR

T

4

T

3

T

2

T

1

T

0

T

f

A:WR

A:WR

B:WR

B:WR

Remove Arrows

From Sched

Before

Step 5

Slide12

T1 T2 T3 T4 (T0) Write A Write B(1) Wlock A(2) Rlock B(3) Unlock A(4) Rlock A(5) Unlock B(6) Wlock B(7) Rlock A(8) Unlock B(9) Wlock B(10) Unlock A(11) Unlock A(12) Wlock A(13) Unlock B(14) Rlock B(15) Unlock A(16) Unlock B(TF) Read A, Read B

Apply Algorithm 3 to Following Schedule

Two Arrows

Removed

Slide13

Alg 3 Ex - Step 5 - Who Else Writes A?

T1 T2 T3 T4 (T0) Write A Write B(1) Wlock A(2) Rlock B(3) Unlock A(4) Rlock A(5) Unlock B(6) Wlock B(7) Rlock A(8) Unlock B(9) Wlock B(10) Unlock A(11) Unlock A(12) Wlock A(13) Unlock B(14) Rlock B(15) Unlock A(16) Unlock B(TF) Read A, Read B

For

T

4 to Tf Arc Who Else Writes A?

Case III: T3 Add T3 to T4 Arc

For T3 to T1 Arc Who Else Writes A?

Case IV: T4 Add T4 to T3 Arc &Add T1 to T4 Arc &

Slide14

T1 T2 T3 T4 (T0) Write A Write B(1) Wlock A(2) Rlock B(3) Unlock A(4) Rlock A(5) Unlock B(6) Wlock B(7) Rlock A(8) Unlock B(9) Wlock B(10) Unlock A(11) Unlock A(12) Wlock A(13) Unlock B(14) Rlock B(15) Unlock A(16) Unlock B(TF) Read A Read B

Alg 3 Ex - Step 5 - Who Else Writes B?

For T1 to Tf Arc Who Else Writes B?

Case III: T3 Add T3 to T1 Arc

For T0 to T4 Arc Who Else Writes B?

Case II: T1 & T3 Add T4 to T3 Arc Add T4 to T1 Arc

Slide15

Algorithm 3 Solution

5. For Each Arc Ti to Tj Consider All T’s that Write XI. If Ti = To and Tj = Tf then Add No Arcs II. If Ti = To and Tj  Tf then Add Arc from Tj to T III. If Ti  To and Tj = Tf then Add Arc from T to TiIV. If Ti  To and Tj  Tf then Add Pair from T to Ti and Tj to T

T

4

T

3

T

2

T

1

T

0

T

f

A:WR

A:WR

B:WR

B:WR

II B:RW

II B:RW

III B:RW

III A:RW

IV A:RW

IV A:RW

Slide16

Algorithm 3 Solution

T

4

T

3

T

2

T

1

T

0

T

f

A:WR

A:WR

B:WR

B:WR

II B:RW

II B:RW

III B:RW

III A:RW

IV A:RW

IV A:RW

Choose one Arrow from Dotted Pair

Say from T4 to T3 - Still have Cycle Can’t remove

Therefore, Not Serializable.

Slide17

Algorithm 4

Algorithm 4 Problem

Apply Algorithm 4 to the Schedule of Transactions on the next Slide

Assume the Following Transaction Times

T

1

= 175, T

2

= 150, T

3

= 200, T

4

= 225

Determine the RT and WT for A, B, C, and D at each Step

Indicate when a Transaction Aborts or has No Effect

Remember, RT=WT=0 for A, B, C, and D Prior to the 1st Step in the Schedule

Slide18

Algorithm 4: Optimistic CC

Let T be a Transaction with Timestamp

t

Attempting to Perform Operation X on a Data Item I with

t

R

and

Writetime

t

W

If (X = Read and t

t

W

) Perform

Oper

If

t >

t

W

then set

t

R

= t for Data Item

If (X

= Write and t

t

R

and

t

t

W

) Perform

Oper

If

t >

t

r

then set

t

W

= t for Data Item I (write after read)

If

(X = Write and

t

R

 t

<

t

W

)

then

Do Nothing since Later Write will Cancel out the Write of T

If (X = Read and t

<

t

W

) or

(X = Write and t

<

t

R

) then Abort the Operation

1st - T trying to Read Item Before it was Written

2nd - T trying to Write an Item Before it was Read

Slide19

T1=175 T2=150 T3=200 T4=225 A B C D (1) Read A (2) Read A (3) Write C (4) Read C (5) Write B (6) Read B (7) Write A (8) Read C (9) Write D (10) Read B (11) Write A (12) Write B

Algorithm 4 Problem

Initially RT/WT of

A, B, C, and D

are all Zero (0)

T

2

TS 150

A.WT = 0

– set A.RT = 150

T3 TS 200 ≥ C.WT = 0 – set C.RT = 200

T1 TS 175≥ C.RT = 0 – set C.WT = 175

T1 TS 175 ≥ A.WT = 0 – set A.RT = 175

T1 TS 175≥ B.RT = 0 – set B.WT = 175

T4 TS 225 ≥ B.WT = 175 – set B.RT = 225

T3 TS 200 ≥ A.RT = 175 – set A.WT = 200

T4 TS 225 ≥ C.RT = 0 – set C.WT = 225

T2 TS 150 ≥ D.RT = 0 – set D.WT = 150

T2 TS 150 IN NOT ≥ B.WT = 225 – ABORT T2

T4 TS 225 ≥ B.RT = 225 – set B.WT = 225

T4 TS 225 ≥ A.RT = 175– set A.WT = 225

RT=150

RT=175

WT=175

RT=200

WT=175

RT=225

WT=200

WT=225

WT=150

Abort

T2

WT=225

WT=225

Slide20

T1=175 T2=150 T3=200 T4=225 A B C D (1) Read A RT=150 WT=0(2) Read A RT=175 WT=0(3) Write C RT=0 WT=175 (4) Read C RT=200 WT=175 (5) Write B RT=0 WT=175(6) Read B RT=225 WT=175(7) Write A RT=175 WT=200(8) Read C RT=225 WT=175(9) Write D RT=0 WT=150(10) Read B Abort T2(11) Write A RT=175 WT=225(12) Write B RT=225 WT=225

Algorithm 4 Problem

Initially RT/WT of A, B, C, and Dare all Zero (0)

Slide21

Query Optimization Problem

CustomerName(BranchName=‘Storrs’^ Balance > 1000 (Branch x Account x Depositor))

Consider the Three Relations:

Branch (BranchName, BranchCity, Assets)

Account(AccountNumber, BranchName, Balance)

Depositor(CustomerName, AccountNumber)

Optimize the following Relational Express by

Drawing the Initial Expression Tree

Optimizing (Rewriting) the Tree

The Query Finds all Customers who have Accounts at the Storrs Branch with Balance > 1000

Slide22

Depositor

Branch

Account

CustomerName

BranchName = Storrs

^ Balance > 1000

X

X

Create the Initial

Query Tree

Slide23

Depositor

Branch

Account

CustomerName

BranchName = Storrs

^ Balance > 1000

X

X

Initial

Query Tree

Slide24

QO: Solution - Optimized Query Tree

CustomerName

AccountNumber

Account

Balance > 1000

Branch

BranchName, AccountNumber

BranchName

BranchCity=‘Storrs’

BranchName

Depositor

AccountNumber

Slide25

Slide26

Slide27

Slide28

Slide29