CS336 Principles of Database Management Systems
Problem Set 5 ANSWERS

1.

Suppose we have the following two transactions:

      T3                  T4
-------------------------------------
     X(W)                X(Y)
     R(W)                X(W)
     X(X)                R(W)
     R(X)                X(X)
     X(Y)                R(X)
     U(X)                U(X)
     U(W)                U(W)
     Y <- W * X          Y <- W * X
     W(Y)                W(Y)
     U(Y)                U(Y)

(a)

Does T3 comply with 2PL? If not, why not?

T3 does comply with 2PL. All locks are obtained, followed by all unlocks.

(b)

Does T4 comply with 2PL? If not, why not?

T4 also complies with 2PL.

2.

For the following tree organization of elements in a (tiny) database:

                   A
                 /   \
               /       \
              B         C
            /   \     /   \
           D     E   F     G
          / \   / \ / \   / \
         H  I  J  K L  M N   O

Which of the following sequences of locks and unlocks conforms to tree-locking protocol?

(a)

X(A), X(B), U(A), X(E), U(B), X(J), U(E), U(J)

This is OK.

(b)

X(A), X(B), U(A), X(E), U(B), X(C), U(E), U(C)

Cannot obtain the lock on C, as the lock on A was already released.

(c)

X(E), X(J), X(K), U(E), U(K), U(J)

This is fine. The first lock can be placed anywhere in the tree.

(d)

X(E), X(J), U(E), X(K), U(K), U(J)

Cannot obtain the lock on K after the lock on E is released.

(e)

X(B), X(D), X(E), U(B), X(I), X(K), U(E), U(I), U(D), U(K)

This is OK.

3.

For the following recovery log:

<T1, start>
<T2, start>
<T1, X, 10, 50>
<T2, X, 50, 55>
<T1, Y, 5, 35>
<T1, commit>

(a)

Describe the sequence of recovery actions under a deferred update protocol.

No undo actions are necessary. Only T1 committed, so the redo sequence sets X to 50 and Y to 35.

(b)

Describe the sequence of recovery actions under an immediate update protocol.

T2 is uncommitted, so the undo sequence sets X to 50. T1 is committed, so the redo sequence is as above.

4.

For the following recovery log:

<T1, start>
<T2, start>
<T1, X, 10, 20>
<T3, start>
<T2, Y, 21, 22>
<T4, start>
<T1, Z, 5, 33>
<T3, Y, 22, 14>
<T1, commit>
<T4, Z, 33, 100>
<CHECKPOINT>
<T2, commit>
<T4, X, 20, 15>

(a)

Describe the sequence of recovery actions under a deferred update protocol.

Since the protocol is deferred, no undo actions are required.

T1 and T2 are committed, but T1 committed before the checkpoint and does not need to be redone. Therefore, the redo action sets Y to 22.

(b)

Describe the sequence of recovery actions under an immediate update protocol.

The uncommitted transactions T3 and T4 must be undone. The sequece of undo actions sets X to 20, Z to 33, and Y to 22. The redo actions are as above.

5.

State (and give justification) whether each of the following pairs of transaction schedules are:

Conflict equivalent? (Draw a precedence graph)
View equivalent? (State rule(s) satisfied/violated)
In each case, is the second schedule (2) conflict-serializable, view-serializable, both, or neither?

(a)

     (1)  T1               T2
          --------------------
          R(X)
          R(Y)
          W(X)
          W(Y)
                          R(X)
                          W(X)
                          R(Y)
                          W(Y)

Conflicts (T1 - T2)

R(X) $\rightarrow$ W(X)
R(Y) $\rightarrow$ W(Y)
W(X) $\rightarrow$ R(X)
W(X) $\rightarrow$ W(X)
W(Y) $\rightarrow$ R(Y)
W(Y) $\rightarrow$ W(Y)

     (2)  T1               T2
          --------------------
          R(X)
          R(Y)
          W(X)
                          R(X)
                          W(X)
          W(Y)
                          R(Y)
                          W(Y)

Conflicts (T1 - T2)

R(X) $\rightarrow$ W(X)
R(Y) $\rightarrow$ W(Y)
W(X) $\rightarrow$ R(X)
W(X) $\rightarrow$ W(X)
W(Y) $\rightarrow$ R(Y)
W(Y) $\rightarrow$ W(Y)

All conflicts have the same direction in both schedules, therefore the schedules are conflict-equivalent.

Since the schedules are conflict-equivalent, they must also be view-equivalent.

Since S2 is conflict-equivalent to S1, a serial schedule, S2 is conflict-serializable. This is also true because there are no cycles in the precedence graph for S2. It must therefore also be view-serializable.

(b)

     (1)  T1               T2
          --------------------
          R(Y)
          W(Z)
                          W(Z)
                          R(Y)
                          W(X)

Conflicts (T1 - T2)

W(Z) $\rightarrow$ W(Z)

     (2)  T1               T2
          --------------------
          R(Y)
                          W(Z)
                          R(Y)
          W(Z)
                          W(X)

Conflicts (T1 - T2)

W(Z) $\leftarrow$ W(Z)

Since the conflicts are not in the same direction in S1 as in S2, the two schedules are not conflict-equivalent.

The two schedules are not view-equivalent:

T1 and T2 read the original value of Y in both.
Neither T1 nor T2 reads a value written by the other.
T2 writes the final values of X and Z in S1, but not in S2.

Since there are no cycles in the precedence graph for S2, it is conflict-serializable and view-serializable.

(c)

     (1)  T1               T2
          --------------------
           R(X)
           W(Y)
           W(Z)
                           R(X)
                           W(Z)
                           W(X)
                           R(Y)
                           W(Z)

Conflicts (T1 - T2)

R(X) $\rightarrow$ W(X)
W(Y) $\rightarrow$ R(Y)
W(Z) $\rightarrow$ W(Z)
W(Z) $\rightarrow$ W(Z)

     
     (2)  T1               T2
          --------------------
           R(X)
                           R(X)
                           W(Z)
                           W(X)
           W(Y)
                           R(Y)
           W(Z)
                           W(Z)

Conflicts (T1 - T2)

R(X) $\rightarrow$ W(X)
W(Y) $\rightarrow$ R(Y)
W(Z) $\rightarrow$ W(Z)
W(Z) $\leftarrow$ W(Z)

Since the conflicts are not in the same direction in S1 as in S2, the two schedules are not conflict-equivalent.

The two schedules are view-equivalent:

T1 and T2 read the original value of X in both.
T2 reads the value of Y written by T1 in both.
T1 writes the final value of Y in both. T2 writes the final values of X and Z in both.

Since there are cycles in the precedence graph for S2, it is not conflict-serializable. Since S2 is view-equivalent to S1, a serial schedule, S2 is view-serializable.

(d)

       (1)  T1               T2               T3
          ---------------------------------------
                           R(X)
                           W(Z)
           R(X)
           W(Z)
           W(X)
           W(Y)
                                            R(Y)
                                            W(Z)

Conflicts (T1 - T2)

W(Z) $\leftarrow$ W(Z)
W(X) $\leftarrow$ R(X)

Conflicts (T2 - T3)

W(Z) $\rightarrow$ W(Z)

Conflicts (T1 - T3)

W(Z) $\rightarrow$ W(Z)
W(Y) $\rightarrow$ R(Y)

       (2)  T1               T2               T3
          ---------------------------------------
           R(X)
           W(Z)
                           R(X)
                           W(Z)
           W(X)
           W(Y)
                                            R(Y)
                                            W(Z)

Conflicts (T1 - T2)

W(Z) $\rightarrow$ W(Z)
W(X) $\leftarrow$ R(X)

Conflicts (T2 - T3)

W(Z) $\rightarrow$ W(Z)

Conflicts (T1 - T3)

W(Z) $\rightarrow$ W(Z)
W(Y) $\rightarrow$ R(Y)

Since the conflicts are not in the same direction in S1 as in S2, the two schedules are not conflict-equivalent.

The two schedules are view-equivalent:

T1 and T2 read the original value of X in both.
T3 reads the value of Y written by T1 in both.
T1 writes the final values of X and Y in both. T3 writes the final value of Z in both.

Since there are cycles in the precedence graph for S2, it is not conflict-serializable. Since S2 is view-equivalent to S1, a serial schedule, S2 is view-serializable.

About this document ...

This document was generated using the LaTeX2HTML translator Version 98.1p1 release (March 2nd, 1998)

The command line arguments were:
latex2html -no_navigation -show_section_numbers -split 0 hw4-spr02-answers.tex.

The translation was initiated by Jack Keane on 2002-05-04

Jack Keane
2002-05-04