CS336 Principles of Database Management Systems
Problem Set 4

Due: May 2, 2002

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?

(b)

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

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)

(b)

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

(c)

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

(d)

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

(e)

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

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.

(b)

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

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.

(b)

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

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)
     
     (2)  T1               T2
          --------------------
          R(X)
          R(Y)
          W(X)
                          R(X)
                          W(X)
          W(Y)
                          R(Y)
                          W(Y)

(b)

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

(c)

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

(d)

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

About this document ...

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

Copyright © 1993, 1994, 1995, 1996, 1997, Nikos Drakos, Computer Based Learning Unit, University of Leeds.

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

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