3,357 views

​​​​​Let $S$ be the following schedule of operations of three transactions $T_1$, $T_2$ and $T_3$ in a relational database system:

$$R_2(Y), R_1(X), R_3(Z), R_1(Y)W_1(X), R_2(Z), W_2(Y), R_3(X), W_3(Z)$$

Consider the statements $P$ and $Q$ below:

• $P$: $S$ is conflict-serializable.
• $Q$: If $T_3$ commits before $T_1$ finishes, then $S$ is recoverable.

Which one of the following choices is correct?

1. Both $P$ and $Q$ are true
2. $P$ is true and $Q$ is false
3. $P$ is false and $Q$ is true
4. Both $P$ and $Q$ are false

Answer should be (B) as Q is false.

The statement should be “S is not Recoverable”.Then it wud have been correct.

I am confused. If $T_{3}$ were to commit before $T_{1}$, it means that $T_{3}$ commits  some time after the $R_{3}(Z)$ operation and before $W_{1}(X)$. This raises two questions:

1. Since normally no operations are performed after commit, shall we consider that $T_{3}$ would still perform its 2 remaining operations even after commit?
1. Before $T_{1}$ finishes, neither $T_{2}$ nor $T_{3}$ is performing a dirty read from $T_{1}$ and committing before $T_{1}$. So, how is the schedule not recoverable?

What am I missing here @Arjun sir?

@deb_141 Here $T3$ is performing dirty read on $T1$. Hence in order to get recoverable schedule, $T3$ must commit after $T1$ commits and not the other way round.

So, $T3$ can perform its 2 remaining operations even after $T1$ commits. In this case it would become cascadless schedule.

$$\small \begin{array}{|c|c|c|}\hline T_{1} & T_{2} & T_{3} \\\hline & R(Y) & \\ R(X) & &\\&& R(Z) \\ R(Y) & & \\ W(X) & & \\ & R(Z) & \\ & W(Y) & \\ & & R(X) \\ & & W(Z) \\ \hline\end{array}$$

• $T_{1} \rightarrow T_{2}$ due to $R_1(Y)$ being before $W_2(Y)$
• $T_{1} \rightarrow T_{3}$ due to $W_1(X)$ being before $R_3(X)$
• $T_{2} \rightarrow T_{3}$ due to $R_2(Z)$ is being $W_3(Z)$ in the schedule.

There are no other conflicts and the discovered conflicts are not forming the cycle.

Therefore, the given schedule is Conflict Serializable.

Statement $Q:$ If $T_{3}$ commits, before $T_{1}$ finishes, then $S$ is recoverable.

Schedule S is recoverable, if Tj creating the dirty read by reading the written data by Ti and Tj commits after Ti commits.

By the above definition, $Q$ is wrong.

Option B is correct.

if T3 commits before T2 finishes then S will be recoverable right?

@Pranavpurkar

There are no dirty reads between T2 and T3. For a dirty read to happen, T2 must write a data item and T3 must read the same data item when T2 is in uncommitted state

But, there is a dirty read between T1 and T3 since T1 has written X and T2 has read the value of X that T1 wrote and T1 hasnt committed yet

If T3 commits before T1 then schedule is irrecoverable in case T1 aborts

So, in order to ensure recovery T3 must commit ONLY AFTER T1 commits

Thanks:)

B

Given Transactions written in tabular form,

$\hspace{125pt}\begin{array}{|c|c|c|}\hline T_1&T_2&T_3\\\hline &R(Y) \\R(X) \\&&R(Z) \\R(Y) \\W(X) \\&R(Z) \\&W(Y) \\&&R(X) \\&&W(Z) \\\hline \end{array}$

Precedence graph for the same,

From the above, we can infer that the above schedule in conflict-serializable.

For commit operations, $T_1$ & $T_2$ must commit before $T_3$ which can be inferred from the above too.

Why since, if $T_3$ commits before $T_1$ and $T_1$ suddenly does an abort it would be as if $T_3$ was a successful transaction but in reality all it’s changes would be swept away by $T_1$’s rollback operations.

I think there should be an edge from T1 to T2 as well because R(y) in T1 is conflicting with W(y) in T2.
Yeah, I did a mistake… lol

Much obliged...