Database: Transactions and Concurrency Control

Consider the following two transactions:                                                      

T0: read(A);

     read(B);

     if A=0 then B:=B+1;

     write(B).

T1: read(B);

     read(A);

     if B=0 then A:=A+1;

     write(A).

1)   Let the consistency requirement be A=0 \/ B=0, with A=B=0 the initial values.

                            i.          Show that every serial execution involving these two transactions preserves the consistency of the database.                                                               (4 points)

                       

                          ii.          Show a concurrent execution of T0 and T1 which produces a nonserializable schedule.                                                                                              (4 points)

                        iii.          Is there a concurrent execution of T0 and T1 which produces a serializable schedule?                                                                                         (4 points)

2) Add lock and unlock instructions to T0 and T1, so that they observe the two-phase locking protocol.                                                                                                     (4 points)

3) Can the execution of the above two-phase locking transactions result in a deadlock?

                                                                                                                            (4 points)

4) Show how two-phase locking helps avoid the non-serializable schedule of your answer to 1).ii.