Recitation #3

Paper Problems 1. r_1(x), r_2(x), w_1(x),w_2(x)

Write the DSG for the above schedule.Is this schedule conflict serializable? Is this schedule view serializable?

2. w_2(z),r_1(x),r_1(y),r_2(y),w_1(x),r_2(x),w_2(y),w_1(z),w_2(z)

Is this schedule conflict serializable? Is this schedule view serializable?

3. Assume that the following schedule with locks is executed over a SVCC mechanism.

rlk_1(x), r_1(x), rlk_2(x), wlk_1(x),w_1(x),ulk_1(x),cmt_1, wlk_2(x),w_2(x),ulk_2(x), cmt_2

Describe what happens.Does this schedule respect 2PL, SS2PL (AKA ‘Rigorous’), Conservative 2PL?

4. Which SQL isolation levels are met with schedules that observe: a. locking protocols b. 2PL c. SS2PL (AKA 'Rigorous') d. Conservative and Rigorous

System Exercises Run these system exercises on PostgreSQL version 9.1 or greater.¶

Consider we have 2 runways and three time slots that planes may land. We represent this scenarioe initial state of this problem in the following table:

runway time_slot flight condition 1 1 NULL fair 1 2 NULL fair 1 3 NULL fair 2 1 NULL fair 2 2 NULL fair 2 3 NULL fair

Note that runway and time_slot are a primary key of the table and should be declared so! Two transactions (T_1 and T_2) want access to this table concurrently. In the problems that follow, always run T_1 before T_2, but start T_2 before T_1 commits, but after T_1 has executed its UPDATE operation. Use ps_sleep to automate this process. Always run the transactions over the initial state of the database above. For each of the transactionproblems below, run under all 9 possible permutations of PostgreSQL isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) and group resulting behaviors into equivalence classes. (Isolation levels are READ COMMITTED, REPEATABLE READ, SERIALIZABLE). Think about what you can conclude from these observations and be ready to discuss.

5. T_1: Book flight SK123 for runway 1 at time 1 T_2: Book flight DY321 for runway 1 at time 1

6. T_1: Book flight SK123 for runway 1 at time 1 T_2: Book flight DY321 for runway 1 at time 2

7. T_1: Book flight SK123 for runway 1 at time 1 T_2: Set condition = 'rain' for runway 1 at time 1

8. T_1: Select the available runways for time 1, wait 20 seconds, and book the lowest number runway for SK123. T_2: Select the available runways for time 1, wait 20 seconds, and book the lowest number runway for DY321.

Note you should start T_2 as T1 is 'waiting 20' seconds.¶

9. Define a PostgreSQL rule that guards against updates that allow for two flights being booked on the same runway in consecutive time steps.

10. Rerun problem 6 under with the rule of 9 defined.

* The system exercises here are modeled loosely on the exercises given in Steve Hegner's Database Systems course.