MSIT 630 Database Systems

Total: 40 points
1. List the ACID properties. Explain the usefulness of each. (4 points)
2. Consider the following two transactions: (10 points)
T13: read(A);
read(B);
if A = 1 then B := B – 1;
write(B).
T14: read(B);
read(A);
if B = 1 then A := A – 1;
write(A).
Let the consistency requirement be A = 1 or B = 1, with A = 1 and B = 1 as the initial values.
a. Show that every serial execution involving these two transactions preserves the consistency of the database.
b. Show a concurrent execution of T13 and T14 that produces a nonserializable schedule.
c. Is there a concurrent execution of T13 and T14 that produces a serializable schedule?
3. What are the difference between shared lock and exclusive lock. (4 points)
4. Describe the phenomenon of cascading rollback. How can cascading rollback be avoid in a cascadeless schedule? (6 points)
5. What is the two-phase locking protocol? What is the strict two-phase locking protocol? What is the rigorous two-phase locking protocol? What benefit does strict two-phase locking protocol provide? What benefit does rigorous two-phase locking protocol provide? (8 points)
6. Consider the log in Figure 16.5 below. Suppose there is a crash just before the < T0 abort> log record is written out. Explain what would happen during recovery. Describe the redo and undo phase of the recovery algorithm. (8 points)