CHAPTER 18: CONCURRENCY CONTROL TECHNIQUES
Answers to Selected Exercises
18.20 Prove that the basic two-phase locking protocol guarantees conflict serializability of
schedules. (Hint: Show that, if a serializability graph for a schedule has a cycle, then at least
one of the transactions participating in the schedule does not obey the two-phase locking
(This proof is by contradiction, and assumes binary locks for simplicity. A similar
proof can be made for shared/exclusive locks.)
Suppose we have n transactions T1, T2, .
.., Tn such that they all obey the basic
two-phase locking rule (i.e. no transaction has an unlock operation followed by a lock
operation). Suppose that a non-(conflict)-serializable schedule S for T1, T2, .
does occur; then, according to Section 17.5.2, the precedence (serialization) graph for
S must have a cycle. Hence, there must be some sequence within the schedule of the form:
..; [o1(X); .
..; o2(X);] .
..; [ o2(Y); .
..; o3(Y);] .
.. ; [on(Z); .
where each pair of operations between square brackets [o,o] are conflicting (either [w,
w], or [w, r], or [r,w]) in order to create an arc in the serialization graph. This
implies that in transaction T1, a sequence of the following form occurs:
..; o1(X); .
.. ; o1(Z); .
Furthermore, T1 has to unlock item X (so T2 can lock it before applying o2(X) to follow
the rules of locking) and has to lock item Z (before applying o1(Z), but this must occur
after Tn has unlocked it). Hence, a sequence in T1 of the following form occurs:
..; o1(X); .
..; unlock(X); .
.. ; lock(Z); .
..; o1(Z); .
This implies that T1 does not obey the two-phase locking protocol (since lock(Z) follows
unlock(X)), contradicting our assumption that all transactions in S follow the two-phase
18.21 Modify the data structures for multiple-mode locks and the algorithms for
read_lock(X), write_lock(X), and unlock(X) so that upgrading and downgrading
of locks are possible. (Hint: The lock needs to keep track of the transaction id(s)
that hold the lock, if any.)
We assume that a List of transaction ids that have read-locked an item is maintained, as
well as the (single) transaction id that has write-locked an item. Only read_lock and
write_lock are shown below.
read_lock (X, Tn):
B: if lock (X) = "unlocked"
then begin lock (X) <- "read_locked, List(Tn)";
no_of_reads (X) <- 1
else if lock(X) = "read_locked, List"
(* add Tn to the list of transactions that have read_lock on X *)
lock (X) <- "read_locked, Append(List,Tn)";
no_of_reads (X) <- no_of_reads (X) + 1
else if lock (X) = "write_locked, Tn"
Pre-Publication Material: This is draft manuscript yet to be copy edited or paged.