Chapter 17

Describe the four levels of isolation in SQL.

Define the violations caused by each of the following: dirty read, nonrepeatable read, and phantoms.

Why is an explicit transaction end statement needed in SQL but not an explicit begin statement?

Describe situations where each of the different isolation levels would be useful for transaction processing.

Which of the following schedules is (conflict) serializable? For each serializable schedule, determine the equivalent serial schedules. a. $r_1(X);r_3(X);w_1(X);r_2(X);w_3(X)$ b. $r_1(X);r_3(X);w_3(X);w_1(X);r_2(X)$ c. $r_3(X);r_2(X);w_3(X);r_1(X);w_1(X)$ d. $r_3(X);r_2(X);r_1(X);w_3(X);w_1(X)$

Consider the three transactions $T_1,T_2,$ and $T_3,$ and the schedules $S_1$ and $S_2$ given below. Draw the serializability (precedence) graphs for $S_1$ and $S_2,$ and state whether each schedule is serializable or not. If a schedule is serializable, write down the equivalent serial schedule(s). $$\begin{array}{l}{T}_1:r_1(X);r_1(Z);w_1(X)\\ T_2:r_2(Z);r_2(Y);w_2(Z);w_2(Y)\\ T_3:r_3(X);r_3(Y);w_3(Y);\\ S_1:r_1(X);r_2(Z);r_1(Z);r_3(X);r_3(Y);w_1(X);w_3(Y);r_2(Y);w_2(Z);w_2(Y)\\ S_2:r_1(X);r_2(Z);r_3(X);r_1(Z);r_2(Y);r_3(Y);w_1(X);w_2(Z);w_3(Y);w_2(Y)\end{array}$$

Consider schedules $S_3,S_4,$ and $S_5$ below. Determine whether each schedule is strict, cascadeless, recoverable, or nonrecoverable. (Determine the strictest recoverability condition that each schedule satisfies.) $$\begin{array}{l}{S}_3:r_1(X);r_2(Z);r_1(Z);r_3(X);r_3(Y);w_1(X);c_1;w_3(Y);c_3;r_2(Y);w_2(Z);w_2(Y);c_2\\ S_4:r_1(X);r_2(Z);r_1(Z);r_3(X);r_3(Y);w_1(X);w_3(Y);r_2(Y);w_2(Z);w_2(Y);c_1;c_2;c_3\\ S_5:r_1(X);r_2(Z);r_3(X);r_1(Z);r_2(Y);r_3(Y);w_1(X);c_1;w_2(Z);w_3(Y);w_2(Y);c_3;c_2\end{array}$$