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 the 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 problems below, run under all 9 possible permutations of PostgreSQL isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) and group resulting behaviors into equivalence classes.For example case 1: T_1 under RC, T_2 under RC; case 2 T_1 under RC, T_2 under RR; case 3. ... 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.