University of Helsinki/CS
Database Management, spring2003, Practice session 6

The exam is on Friday the 16th of May at 16-20 in class Porthania I.

1. The consistency constraints placed on the database require 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;
  1. Do the independent transactions preserve consistency?
  2. What isolation anomalies exist in this schedule?
  3. Write down the log entries registered when the initial values are X = Y = Z = 0?
  4. Does this schedule preserve the consistency of the database?
  5. How does strict 2PL prevent this schedule to take place?

2. 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;
  1. Which pairs of operations conflict in this schedule? Which isosolation anomaly is attached to each conflict?
  2. Give three examples of operation pairs in this schedule that do not conflict. Use a separate criterion in each example.

3. The database management system uses strict two-phase locking. Elements that are locked are pages. What locks are taken and when to carry out the following transaction 

   begin transaction; 
      update EMPLOYEE set SALARY = SALARY + 500 where DEPARTMENT = 5; 
   commit transaction.
a) when relation EMPLOYEE is implemented as a heap.,
b) when relation EMPLOYEE is implemented as a hash file using hash key DEPARTMENT.
c) when relation EMPLOYEE is implemented as a heap, and has a secondary hash organized index.. 
 

4. The database management system uses strict two-phase locking. Elements that are locked are pages. 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.
when the table is implemented as an ISAM structure?

5. A common tehcnique in maintaining databases is to fetch a collection of rows into the workspace a a client computer, show the rows to the user that may then modify some of them. The changed made by the user are then registered in the database. How should the operations be divided into transactions? What isolation level and what kind of locking should be used?