Transactions and Concurrency · Database Management System · GATE CSE

An audit of a banking transactions system has found that on an earlier occasion, two joint holders of account $A$ attempted simultaneous transfers of Rs. 10000 each from account $A$ to account $B$. Both transactions read the same value, Rs. 11000, as the initial balance in $A$ and were allowed to go through. $B$ was credited Rs. 10000 twice. $A$ was debited only once and ended up with a balance of Rs. 1000. Which of the following properties is/are certain to have been violated by the system?

A schedule of three database transactions $T_1, T_2$, and $T_3$ is shown. $R_i(A)$ and $W_i(A)$ denote read and write of data item $A$ by transaction $T_i, i=1,2,3$. The transaction $T_1$ aborts at the end. Which other transaction(s) will be required to be rolled back?

$$R_1(X) W_1(Y) R_2(X) R_2(Y) R_3(Y) \operatorname{ABORT}\left(T_1\right)$$

Once the DBMS informs the user that a transaction has been successfully completed, its effect should persist even if the system crashes before all its changes are reflected on disk. This property is called

Which of the following statements about the Two Phase Locking (2PL) protocol is/are TRUE?

Suppose a database system crashes again while recovering from a previous crash. Assume checkpointing is not done by the database either during the transactions or during recovery.

Which of the following statements is/are correct?

read (x) ; x: = x - 50; write (x); read (y); y: = y + 50;   write (y)

The constraint that the sum of the accounts x and y should remain constant is that of

I. 2-phase locking
II. Time-stamp ordering

Consider the database transactions T 1 and T 2, and data items X and Y . Which of the schedule(s) is/are conflict serializable?

Consider the following read-write schedule $S$ over three transactions $T_{1}$, $T_{2}$, and $T_{3}$, where the subscripts in the schedule indicate transaction IDs:

$S: r_{1}(z); w_{1}(z); r_{2}(x); r_{3}(y); w_{3}(y); r_{2}(y); w_{2}(x); w_{2}(y);$

Which of the following transaction schedules is/are conflict equivalent to $S$?

Let R_i(z) and W_i(z) denote read and write operations on a data element z by a transaction T_i, respectively. Consider the schedule S with four transactions.

S : R₄(x), R₂(x), R₃(x), R₁(y), W₁(y), W₂(x), W₃(y), R₄(y)

Which one of the following serial schedules is conflict equivalent to S?

Let S be the following schedule of operations of three transactions T₁, T₂ and T₃ in a relational database system:

R₂(Y), R₁(X), R₃(Z), R₁(Y), W₁(X), R₂(Z), W₂(Y), R₃(X), W₃(Z)

Consider the statements P and Q below:

P: S is conflict-serializable.

Q: If T₃ commits before T₁ finishes, then S is recoverable.

Which one of the following choices is correct?

Let r_i(z) and w_i(z) denote read and write operations respectively on a data item z by a transaction T_i. Consider the following two schedules.

S₁ : r₁(x) r₁(y) r₂(x) r₂(y) w₂(y) w₁(x)

S₂ : r1(x) r₂(x) r2(y) w2(y) r₁(y) w1(x)

Which one of the following options is correct?

 S = r2(X); r1(X); r2(Y); w1(X); r1(Y); w2(X); a1; a2

where r_i(Z) denotes a read operation by transaction T_i on a variable Z, w_i(Z) denotes a write operation by T_i on a variable Z and a_i denotes an abort by transaction T_i .

Which one of the following statements about the above schedule is TRUE?

Step 1. T acquires exclusive locks to $${{O_1},...,{O_k}}$$ in increasing order of their
addresses.
Step 2. The required operations are performed.
Step 3. All locks are released.

This protocol will

Suppose that the transaction $$T1$$ fails immediately after time instance $$9.$$ Which one of the following statements is correct?

(start, $$T4$$); (write, $$T4, y, 2, 3$$); (start, $$T1$$); (commit, $$T4$$); (write, $$T1, z, 5, 7$$);
(checkpoint);
(start, $$T2$$); (write, $$T2, x, 1, 9$$); (commit, $$T2$$); (start, $$T3$$), (write, $$T3, z, 7, 2$$);

If a crash happens now and the system tries to recovver using both undo and redo operations, what are the contents of the undo list and the redo list?

T1 : r1 (X) ; r1 (Z) ; w1 (X) ; w1 (Z)

T2 : r2 (X) ; r2 (Z) ; w2 (Z)

T3 : r3 (X) ; r3 (X) ; w3 (Y)

S1: r1(X); r3(Y); r3(X); r2(Y); r2(Z); w3(Y); w2(Z); r1(Z); w1(X); w1(Z)

S2: r1(X); r3(Y); r2(Y); r3(X); r1(Z); r2(Z); w3(Y); w1(X); w2(Z); w1(Z)

T1 : read (P) ; 
     read (Q) ; 
     if P = 0 then Q : = Q + 1 ; 
     write (Q). 

T2 : read (Q) ; 
     read (P) 
     if Q = 0 then P : = P + 1 ; 
     write (P).

T₁: R₁[ x ] W₁[ x ] W₁[ y ]
T₂: R₂[ x ] R₂[ y ] W₂[ y ]
S₁: R₁[ x ] R₂[ x ] R₂[ y ] W₁[ x ] W₁[ y ] W₂[ y ]
S₂: R₁[ x ] R₂[ x ] R₂[ y ] W₁[ x ] W₂[ y ] W₁[ y ]
S₃: R₁[ x ] W₁[ x ] R₂[ x ] W₁[ y ] R₂[ y ] W₂[ y ]
S₄: R₂[ x ] R₂[ y ] R₁[ x ] W₁[ x ] W₁[ y ] W₂[ y ]

I. $$\pi_{R-S}(r) - \pi_{R-S} \left (\pi_{R-S} (r) \times s - \pi_{R-S,S}(r)\right )$$

II. $$\left\{t \mid t \in \pi_{R-S} (r) \wedge \forall u \in s \left(\exists v \in r \left(u = v[S] \wedge t = v\left[R-S\right]\right )\right )\right\}$$

III.$$\left\{t \mid t \in \pi_{R-S} (r) \wedge \forall v \in r \left(\exists u \in s \left(u = v[S] \wedge t = v\left[R-S\right]\right )\right ) \right\}$$

IV. Select R.a, R.b
    From R, S
    Where R.c = S.c

(S1) 2RA 2WA 3RC 2WB 3WA 3WC 1RA 1RB 1WA 1WB

(S2) 3RC 2RA 2WA 2WB 3WA 1RA 1RB 1WA 1WB 3WC

(S3) 2RZ 3RC 3WA 2WA 2WB 3WC 1RA 1RB 1WA 1WB

S₁: r₁(X); r₁(Y); r₂(Y); r₂(X); w₂(Y); w₁(X);

S₂: r₁(X); r₂(X); r₂(Y); w₂(Y); r₁(Y); w₁(X);

T1: read (A);                  T2: read (B);
    read (B);                      read (A);
    if A = 0 then B ← B + 1;       if B ≠ 0 then A ← A - 1;
    write (B);                     write (A);

Which of the following statements is correct?