Transcript PPTX

Apply Algorithm 2 to Following Schedule
CSE
255
(1)
(2)
(3)
(4)
(5)
(6)
(7)
(8)
(9)
(10)
(11)
(12)
(13)
(14)
(15)
(16)
(17)
(18)
(19)
(20)
(21)
(22)
(23)
(24)
T1
T2
Rlock A
T3
Rlock A
Wlock C
Unlock C
Rlock C
Wlock B
Unlock B
T4Each Rlock
For
For
:WlockA B
TT11 :Rlock
for
TLook
A
Since
Rlock
2 :Rlock
Next
T to
Between
Look Arc
for
Wlock
B
Add
Next TT toto T
from
1
2
Wlock A
Rlock B
Unlock A
Unlock A
Wlock A
Rlock C
Wlock D
Unlock B
Unlock C
Rlock B
For Rlock B
Next to to
Wlock?
For Rlock Cs
No Wlock C
Unlock A
Wlock A
Unlock B
Wlock B
Unlock B
Unlock D
Unlock C
Unlock A
prob.1
Algorithm 2 Solution


CSE
255
Precedence Graph Below
Equivalent Serial Schedule via Topological Sort is
 T1, T2, T3, T4
 Note there may be multiple Serial Schedules
A:RW
T1
T3
B:W
W
B:WR
A:WW
A:RW
T2
T4
B:RW
prob.2
Apply Algorithm 3 to Following Schedule
CSE
255
T1
(T0) Write A, B
(1)
(2)
(3)
(4) Rlock A
(5)
(6)
(7)
(8)
(9) Wlock B
(10)
(11) Unlock A
(12)
(13) Unlock B
(14)
(15)
(16)
(TF) Read A, B
T2
T3
T4
Wlock A
Rlock B
Unlock A
Unlock B
Wlock B
Rlock A
Unlock B
Unlock A
Wlock A
Rlock B
Unlock A
Unlock B
prob.3
Algorithm 3 Solution

Precedence Graph after Steps 1 to 4
B:WR
CSE
T0
255
T1
B:WR
T2
A:WR
T3
T4
A:WR
Tf
A:WR

T0
Remove Useless
Trans.
B:WR
T1
B:WR
T2
T3
T4
A:WR
Tf
A:WR
B:WR
prob.4
Alg 3 Ex - Step 5 - Who Else Writes A?
CSE
255
T1
(T0) Write A, B
(1)
(2)
(3)
(4) Rlock A
(5)
(6)
(7)
(8)
(9) Wlock B
(10)
(11) Unlock A
(12)
(13) Unlock B
(14)
(15)
(16)
(TF) Read A, B
T2
T3
T4
Wlock A
Rlock B
Unlock A
Wlock B
ForUnlock
T3 toB T1 Arc
Who Else Writes A?
Rlock A
Unlock B
Unlock A
Case IV: T4
Add T4 to T3 Arc &
Add T1 to T4 Arc &
Wlock A
Rlock B
Unlock B
For T4 to Tf Arc
WhoUnlock
Else AWrites A?
Case III: T3
Add T3 to T4 Arc
prob.5
Alg 3 Ex - Step 5 - Who Else Writes B?
CSE
255
T1
(T0) Write A, B
(1)
(2)
(3)
(4) Rlock A
(5)
(6)
(7)
(8)
(9) Wlock B
(10)
(11) Unlock A
(12)
(13) Unlock B
(14)
(15)
(16)
(TF) Read A, B
T2
T3
T4
Wlock A
Rlock B
Unlock A
Wlock B
ForUnlock
T0 toB T4 Arc
Who Else Writes B?
Rlock A
Unlock B
Unlock A
Case II: T1 & T3
Add T4 to T1 Arc &
Add T4 to T3 Arc &
Wlock A
Rlock B
Unlock B
For T1 to Tf Arc
WhoUnlock
Else AWrites B?
Case III: T3
Add T3 to T1 Arc
prob.6
Algorithm 3 Solution

5. For Each Arc Ti to Tj Consider All T’s that Write X



CSE
255

I. 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 Ti
IV. If Ti  To and Tj  Tf then Add Pair from T to Ti and Tj to T
B:WR
IV A:RW
II B:RW
IV A:RW
T0
T1
T3 II B:RW T4
T2
A:WR
A:WR
Tf
III A:RW
III B:RW
B:WR
prob.7
Algorithm 3 Solution



Choose one Arrow from Dotted Pair
Say from T4 to T3 - Still have Cycle Can’t remove
Therefore, Not Serializable.
CSE
255
B:WR
IV A:RW
II B:RW
IV A:RW
T0
T1
T3 II B:RW T4
T2
A:WR
A:WR
Tf
III A:RW
III B:RW
B:WR
prob.8
Algorithm 4 Problem
T1=175
T3=200
2=150
T2 TS 150
≥ A.WT T=
0 – set
A.RT =T4=225
150
(1)
Read A
T1 TS 175 ≥ A.WT = 0 – set A.RT = 175
(2) Read A
T1 TS 175≥ C.RT = 0 – set C.WT = 175
(3) Write C
CSE
T3 TS 200 ≥ C.WT = 0 – set C.RT = 200
255 (4)
Read C
T1 TS 175≥ B.RT = 0 – set B.WT = 175
A
RT=150
WT=0
RT=175
WT=0
B
T3 TS(6)200 ≥ A.RT = 175 – set A.WTRead
= 300
T2 TS(8)150 ≥ D.RT = 0 – set D.WT =Read
150C
B
C
D
RT=0
WT=175
RT=200
WT=175
Write B
T4 TS(5)225
≥ B.WT = 175 – set B.RT = 225
Write
A = 225
T4 TS(7)225 ≥ C.RT = 0 – set
C.WT
Initially RT/WT of
A, B, C, and D
are all Zero (0)
RT=0
WT=175
RT=225
WT=175
RT=175
WT=200
RT=225
WT=175
T2 TS(9)150 IN NOT Write
≥ B.WT
= 225 – ABORT T2
D
RT=0
WT=150
T4 TS(10)
225 ≥ A.RT Read
= 175–
set A.WT = 225
B
T4 TS(11)
225 ≥ B.RT = 225 – set B.WTWrite
= 225
A RT=175
Abort T2
WT=225
(12)
Write B
RT=225
WT=225
prob.9
Initial Query Tree

CSE
255
CustomerName
 BranchName = Storrs
^ Balance > 1000
X
Depositor
X
Branch
Account
prob.10
QO: Solution - Optimized Query Tree
CustomerName
AccountNumber
CSE
255
AccountNumber
Depositor
BranchName
BranchName
BranchCity=‘Storrs’
Branch
BranchName, AccountNumber
Balance > 1000
Account
prob.11