Database management, Fall 2004

Exercises 6 (9.12.2004)

The course exam on Thursday the 16th of December at 8-12 in Class I, University Main Building (Fabianinkatu 33)

• Consider the following schedule of transactions T1 and T2:

  1 T1: read(X,v);
  2 T2: read(X,u);
  3 T1: write(X,v);
  4 T1: read(Y,z);
  5 T2: read(X,w);
  6 T2: write(X,w);
  7 T1: write(Y,z);
  8 T1: commit;
  9 T2: commit;
  

  Which pairs of operations conflict in this schedule? Which isolation anomaly is attached to each conflict?
• The log has the following contents when the system crashes

               99: [checkpoint] 
              100: [start, T1] 
              101: [start, T2] 
              102: [write, T1, A, 10, 20] 
              103: [write, T2, B, 11, 22] 
              104: [commit, T1] 
              105: [start, T3] 
              106: [write, T3, A, 20, 30] 
              107: [abort, T2] 
              108: [checkpoint] 
              109: [start, T4] 
              110: [write, T3, B, 11, 33] 
              111: [write, T3, A, 30, 40] 
              112: [write, T4, C, 15, 25] 
              113: [commit, T3] 
              114: [write, T4, C, 25, 35] 
  

  • What operations must be done to recover the database when page of data element A has PageLSN = 106, and the page of data element B has PageLSN = 110 and the page of data element C has PageLSN = 112.
  • What operations are needed if line 113 (T3-commit) is omitted, and the situation is otherwise as described in case (a)?
• A consistency constraint placed on the database requires the values of X and Y to be equal. Consider the following schedule of transactions {T1, T2, T3}:

  T1: read(X,v); T1: v:=v+1; T1: write(X,v); T2: read(X,w); T2: write(Y,w); T2: commit; T3: read(Y,p); T1: abort; T3: write(Z,p); T3:commit;

  Do the independent transactions preserve the consistency? What isolation anomalies exist in this schedule? Write down the log entries registered when the initial values are X = Y = Z = 0? Does this schedule preserve the consistency of the database? How does strict 2PL prevent this schedule to take place?

• The database management system uses strict two-phase locking. Elements that are locked are pages. Let table Employee be stored as an ISAM structure. What locks are taken and when to carry out the following transaction

    begin transaction
      update EMPLOYEE set SALARY = SALARY + 500 where EMP_NO=301123
    commit transaction. 

• A common tehcnique in maintaining databases is to fetch a fairly large collection of rows into the workspace of a client computer, ant to show them all to the user who then modifies some of them. The changes made by the user are then written back into the database. How should the operations be divided into transactions? What isolation level and what kind of locking should be used?