CS3402 Database Systems Tutorials
CS3402 Concurrency Control:
Question 1
Consider the following arrival order of operations to the scheduler. If the scheduler uses strict two phase locking to schedule the operations, modify the table below to show the new schedule.
Ta
Tb
Tc
Write(x)
Read(y)
Read(z)
Read(x)
Write(y)
Write(x)
Read(x)
Commit
Write(z)
Commit
Commit
Answer:
It is a deadlock.
Question 2
Consider the following schedule at a single server system.
(1) Add lock and unlock operations to the schedule if Conservative 2PL is adopted. (2) Add lock and unlock operations to the schedule if Strict 2PL is adopted.
(3) Which one (S2PL or C2PL) will you choose for scheduling the two transactions?
Answers (1)
Ta
Tb
Tc
WL(x); Write(x)
RL(y); Read(y)
RL(z); Read(z)
RL(x) blocked
WL(y) blocked
WL(x) blocked
T1
T2
Read(a)
Read(a)
Write(a)
Write(a)
T1
T2
CS3402 Database Systems Tutorials
WriteLock(a)
Read(a)
WriteLock(a) blocked
Write(a)
Unlock(a)
WriteLock(a)
Read(a)
Write(a)
Unlock(a)
(2)
(3)
C2PL since it does not have the deadlock problem and the transactions are short.
Question 3
The following table shows the schedule for transactions T1 and T2 with T1 having an “older” time-stamp than T2.
(1) Assume that Strict Two Phase Locking is used for concurrency control. Define the wait- for-graph.
(2) Show the new schedule if the wait-die method is used.
(3) Show the new schedule if the wound-wait method is used.
T1
T2
ReadLock(a)
Read(a)
ReadLock(a)
Read(a)
WriteLock(a) blocked
WriteLock(a) blocked
T1
T2
Read(a)
Read(b)
Write(b)
Write(a)
Answers:
(1) With S2PL, the schedule is
T1
T2
ReadLock(a); Read(a)
ReadLock(b); Read(b)
WriteLock(b) blocked
WriteLock(a) blocked
The wait-for-graph is:
T1
a
T2
b
CS3402 Database Systems Tutorials
(2)
Wait-die: If TS(Ti) < TS(Tj), Ti waits else Ti dies
T1
T2
ReadLock(a); Read(a)
ReadLock(b); Read(b)
WriteLock(b) blocked
WriteLock(a) aborted and restarted
WriteLock(b)
Write(b)
Unlock(a,b)
ReadLock(b); Read(b)
WriteLock(a)
Write(a)
Unlock(a,b)
(3)
Wound-wait: If TS(Ti) < TS(Tj), Tj wounds else Ti waits
T1
T2
ReadLock(a); Read(a)
ReadLock(b); Read(b)
WriteLock(b)Abort T2
Write(b)
Unlock(a,b)
ReadLock(b); Read(b)
WriteLock(a)
Write(a)
Unlock(a,b)