1. Apply the query tuning on
following query and explain why your modification is more efficient? 2
SELECT
S_name, S_Phone, S_Age
FROM
STUDENT
WHERE
S_Age > 22 OR S_Phone like '0555%’;
SELECT S_name, S_Phone, S_Age
FROM STUDENT
WHERE S_Age > 22
union
SELECT S_name, S_Phone, S_Age
FROM STUDENT
WHERE S_ S_Phone like '0555%’;
2. Consider the three
transactions T1, T2, and T3, and the schedules S1 given below. Draw the
serializibility (precedence) graphs for S1 and state whether schedule is
serializable or not. If a schedule is serializable, write down the equivalent
serial schedule(s). 2
T1: r1(x); r1(z); w1(x)
T2: r2(z); r2(y); w2(z);
w2(y)
T3: r3(x); r3(y); w3(y)
S1: r1(x); r2(z); r3(x);
r1(z); r2(y); r3(y); w1(x); w2(z); w3(y); w2(y)
Problem
21.23 (Page 774)
Consider
the three transactions T1, T2, and T3, and the schedules
S1 and S2
given below. Draw the serializability
(precedence) graphs
for S1 and
S2 and state whether each schedule is serializable or not.
If a
schedule is serializable, write down the equivalent serial schedule(s).
T1: r1(X);
r1(Z); w1(X);
T2: r2(Z);
r2(Y); w2(Z); w2(Y);
T3: r3(X);
r3(Y); w3(Y);
S1: r1(X);
r2(Z); r1(Z); r3(X); r3(Y); w1(X); w3(Y); r2(Y); w2(Z); w2(Y)
S2: r1(X);
r2(Z); r3(X); r1(Z); r2(Y); r3(Y); w1(X); w2(Z); w3(Y); w2(Y)
----------------------------------------------------------------
T1 T2 T3
r1(X) r2(Z) r3(X)
Time r1(Z) r2(Y) r3(Y)
w1(X) w2(Z) w3(Y)
w2(Y)
----------------------------------------------------------------
T1 T2 T3
r1(X)
r2(Z)
Time r1(Z)
r3(X)
r3(Y)
w1(X)
w3(Y)
r2(Y)
w2(Z)
w2(Y)
Schedule: S1
Summary:
Possible conflicts occur when T1 writes to X when T3 is
still
reading X. However T3 does not write to
X so this is ok.
T3 Then
reads and writes to Y before T2 reads and writes to Y so
this is ok
as well. Since T2 reads and writes to Z,
it is also ok
that T1
reads Z but does not write. This schedule is serializable
because
there are no cycles.
----------------------------------------------------------------
T1 T2 T3
r1(X)
r2(Z)
Time r3(X)
r1(Z)
r2(Y)
r3(Y)
w1(X)
w2(Z)
w3(Y)
w2(Y)
Schedule: S2
Summary:
This schedule is non-serializable and contains a major
conflict. Both T2 and T3 are accessing 'Y' when T3
writes to it.
Therefore
when T2 writes to 'Y', the transaction for T3 is lost
and
overridden.
----------------------------------------------------------------
Problem
22.25 (Page 804)
Apply the
timestamp ordering algorithm to the schedules in Figure 21.8 (b)
and (c),
and determine whether the algorithm will allow the execution of the
schedules.
b)
c)
3. What is cautious waiting deadlock avoidance
algorithm? Explain it
return to book page 787
