Transcript PPTX

Algorithm 2

CSE
255

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
prob.1
Algorithm 2: Read/Write Lock Model



CSE
255
Input: Schedule S for Transactions T1, T2 , … Tk
Output: Is S Serializable? If so, Serial Schedule
Method: Create a Directed Precedence Graph G:
 Suppose in S, Ti :Rlock A.
 If Tj : Wlock A is the Next Transaction to Wlock A (if
it exists) then place an Arc from Ti to Tj.
 Repeat for all Ti’s, all Rlocks before Wlock on A!
 Suppose in S, Ti :Wlock A.
 If Tj : Wlock A is the Next Transaction to Wlock A (if
it exists) then place an Arc from Ti to Tj.
 If Also exists Tm :Rlock A after Ti :Wlock A but before
Tj : Wlock A, then Draw an Arc from Ti to Tm.
 Review the Resulting Precedence Graph
 If G has Cycles - Non-Serializable
 If G is Acyclic - Topological Sort for Serial Schedule
prob.2
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
T4
Rlock A
Wlock C
Unlock C
Rlock C
Wlock B
Unlock B
Rlock B
Unlock A
Unlock A
Wlock A
Rlock C
Wlock D
Unlock B
Unlock C
Rlock B
Unlock A
Wlock A
Unlock B
Wlock B
Unlock B
Unlock D
Unlock C
Unlock A
prob.3
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
T1 before T3, T2 before T3, T2 before T4
T2 before T4, T1 before T4, T1 before T2
Unlock C
Unlock A
prob.4
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:WW
B:WR
A:WW
A:RW
T2
T4
B:RW
prob.5
Algorithm 3 – Steps 1 to 4



CSE
255
Input: Schedule S for Transactions T1, T2 , … Tk
Output: Is S Serializable? If so, Serial Schedule
Method: Create a Directed Polygraph Graph P:
1. Augment S with Dummy To (Write Every Item)
an Dummy Tf (Read Every Item)
2. Create Initial Polygraph P by Adding Nodes for
To, Tf, and Each Ti Transaction , in S
3. Place an Arc from Ti to Tj Whenever Tj Reads A
in Augmented S (with Dummy States) that was
Last Written by Ti. Write to Read for Each Item
 Repeat this Step for all Arcs.
 Don’t Forget to Consider Dummy States!

4. Discover Useless Transactions - T is Useless if
there is no Path from T to Tf
This is the “Initialization” Phase of Algorithm 3
prob.6
Algorithm 3 – Steps 5 to 7

CSE
255
Method: Reassess the Initial Polygraph P:
5. For Each Remaining Arc Ti W to Tj R(meaning
that Tj Reads Item A Written by Ti )
Consider all T  To and T  Tf that also Writes A:
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 Arc Pair from T to
Ti and Tj to T
6. Determine if P is Acyclic by “Choosing” One
Transaction Arc for Each Pair - Make Choices
Carefully
7. If Acyclic - Serializable - Perform Topological
Sort without To , Tf for Equivalent Serial Schedule.
Else - Not Serializable
prob.7
Algorithm 3 – Key Steps

CSE
255
Method: Reassess the Initial Polygraph P:
3. Place an Arc from Ti to Tj Whenever Tj Reads A in
Augmented S (with Dummy States) that was Last Written
by Ti. Write to Read for Each Item
 Repeat this Step for all Arcs.
 Don’t Forget to Consider Dummy States!
4. Discover Useless Transactions - T is Useless if there is no
Path from T to Tf
5. For Each Remaining Arc Ti W to Tj R(meaning that Tj
Reads Item A Written by Ti )
Consider all T  To and T  Tf that also Writes A:
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 Arc Pair from T to Ti and Tj to T
6. Determine if P is Acyclic by “Choosing” One Transaction
Arc for Each Pair - Make Choices Carefully
prob.8
Apply Algorithm 3 to Following Schedule
CSE
255
T1
(T0) Write A
(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
T2
Write B
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
Read B
prob.9
Apply Algorithm 3 to Following Schedule
CSE
255
T1
T2
T3
T4
Look for All Write/Read
(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
T3 before T1, T3 before T2, T4 before Tf
T0 before
T4, T1 before T2, T1 before Tf
(10)
Unlock
A
(11) Unlock A
(12)
Wlock A
(13) Unlock B
(14)
Rlock B
(15)
Unlock A
(16)
Unlock B
Remove Useless Arcs
(TF) Read A,
Read B
prob.10
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

Remove Useless
Trans.
B:WR
T0
Remove Arrows
From Sched
Before Step 5
T1
B:WR
T2
T3
T4
A:WR
Tf
A:WR
B:WR
prob.11
Apply Algorithm 3 to Following Schedule
CSE
255
T1
T2
(T0) Write A Write B
(1)
(2)
(3)
(4) Rlock A
(5)
(6)
(7)
Rlock A
(8)
(9) Wlock B
(10)
Unlock A
(11) Unlock A
(12)
(13) Unlock B
(14)
Rlock B
(15)
(16)
Unlock B
(TF) Read A,
Read B
T3
T4
Wlock A
Rlock B
Unlock A
Unlock B
Wlock B
Unlock B
Two Arrows
Removed
Wlock A
Unlock A
prob.12
Alg 3 Ex - Step 5 - Who Else Writes A?
CSE
255
T1
T2
(T0) Write A Write B
(1)
(2)
(3)
(4) Rlock A
(5)
(6)
(7)
Rlock A
(8)
(9) Wlock B
(10)
Unlock A
(11) Unlock A
(12)
(13) Unlock B
(14)
Rlock B
(15)
(16)
Unlock B
(TF) Read A,
Read B
T3
T4
Wlock A
Rlock B
Unlock A
For T3 to T1 Arc
WhoUnlock
ElseB Writes A?
Wlock B
Unlock B
Case IV: T4
Add T4 to T3 Arc &
Add T1 to T4 Arc &
Wlock A
For T4 to Tf Arc
WhoUnlock
Else AWrites A?
Case III: T3
Add T3 to T4 Arc
prob.13
Alg 3 Ex - Step 5 - Who Else Writes B?
CSE
255
T1
T2
(T0) Write A Write B
(1)
(2)
(3)
(4) Rlock A
(5)
(6)
(7)
Rlock A
(8)
(9) Wlock B
(10)
Unlock A
(11) Unlock A
(12)
(13) Unlock B
(14)
Rlock B
(15)
(16)
Unlock B
(TF) Read A
Read B
T3
T4
Wlock A
Rlock B
Unlock A
Wlock B
Unlock B
ForUnlock
T0 toB T4 Arc
Who Else Writes B?
Case II: T1 & T3
Add T4 to T3 Arc
Add T4 to T1 Arc
Wlock A
For T1 to Tf Arc
WhoUnlock
Else AWrites B?
Case III: T3
Add T3 to T1 Arc
prob.14
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.15
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.16
Algorithm 4

CSE
255
Algorithm 4 Problem
 Apply Algorithm 4 to the Schedule of Transactions
on the next Slide
 Assume the Following Transaction Times
 T1 = 175, T2 = 150, T3 = 200, T4 = 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
prob.17
Algorithm 4: Optimistic CC

CSE
255
Let T be a Transaction with Timestamp t Attempting
to Perform Operation X on a Data Item I with
Readtime tR and Writetime tW
 If (X = Read and t  tW ) Perform Oper
 If t > tW then set tR = t for Data Item I (read after write)

If (X = Write and t  tR and t  tW ) Perform Oper
 If t > tr then set tW = t for Data Item I (write after read)


If (X = Write and tR  t < tW ) then Do Nothing
since Later Write will Cancel out the Write of T
If (X = Read and t < tW ) or
(X = Write and t < tR ) 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
prob.18
Algorithm 4 Problem
(1)
T1=175
T2=150
Read A
T3=200
T4=225
A
RT=150
Initially RT/WT of
A, B, C, and D
are all Zero (0)
B
C
D
T2 TS 150 ≥ A.WT = 0 – set A.RT = 150
(2) Read A
T1 TS 175 ≥ A.WT = 0 – set A.RT = 175RT=175
CSE (3) Write C
T TS 175≥ C.RT = 0 – set C.WT = 175
255 1 (4)
Read C
T3 TS 200 ≥ C.WT = 0 – set C.RT = 200
(5) Write B
T1 TS 175≥ B.RT = 0 – set B.WT = 175
WT=175
RT=200
WT=175
Read B
T4 (6)
TS 225 ≥ B.WT = 175 – set B.RT
= 225
RT=225
T3 (7)
TS 200 ≥ A.RT = 175 Write
– setA A.WT = 200
WT=200
Read
C
T4 (8)
TS 225 ≥ C.RT = 0 – set C.WT
= 225
WT=225
Write
T2 (9)
TS 150 ≥ D.RT
= D0 – set D.WT = 150
WT=150
T2 (10)
TS 150 IN NOT
Read≥ B B.WT = 225 – ABORT T2
T4 (11)
TS 225 ≥ A.RT = 175– set A.WT
= A225
Write
Abort T2
WT=225
T4 (12)
TS 225 ≥ B.RT = 225 – set B.WT
Write=B 225
WT=225
prob.19
Algorithm 4 Problem
(1)
T1=175
T2=150
Read A
T3=200
T4=225
(2) Read A
CSE
255
A
RT=150
WT=0
RT=175
WT=0
Initially RT/WT of
A, B, C, and D
are all Zero (0)
B
(3) Write C
(4)
(5) Write B
RT=0
WT=175
RT=225
WT=175
Read B
(7)
Write A
(8)
RT=175
WT=200
Read C
(9)
Write D
(10)
Read B
D
RT=0
WT=175
RT=200
WT=175
Read C
(6)
C
RT=225
WT=175
RT=0
WT=150
Abort T2
(11)
(12)
Write A RT=175
WT=225
Write B
RT=225
WT=225
prob.20
Query Optimization Problem

CSE
255 

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
CustomerName(BranchName=‘Storrs’^ Balance > 1000 (Branch x Account x Depositor))
prob.21
Create the Initial Query Tree

CSE
255
CustomerName
 BranchName = Storrs
^ Balance > 1000
X
Depositor
X
Branch
Account
prob.22
Initial Query Tree

CSE
255
CustomerName
 BranchName = Storrs
^ Balance > 1000
X
Depositor
X
Branch
Account
prob.23
QO: Solution - Optimized Query Tree
CustomerName
AccountNumber
CSE
255
AccountNumber
Depositor
BranchName
BranchName
BranchCity=‘Storrs’
Branch
BranchName, AccountNumber
Balance > 1000
Account
prob.24