5.1 [GATE 1999 : 2 Marks]

For the schedule given below, which of the following is correct?
1 Read A
2 Read B
3 Write A
4 Read A
5 Write A
6 Write B
7 Read B
8 Write B(a) This schedule is serializable and can occur in a scheme using 2PL protocol.
(b) This schedule is serializable but cannot occur in a scheme using 2PL protocol.
(c) This schedule is not serializable but can occur in a scheme using 2PL protocol.
(d) This schedule is not serializable and cannot occur in a scheme using 2PL protocol.

Solution:
Answer: (d)
Reason:
T1 -> T2 conflict hai A par (Write A by T1 before Read/Write A by T2)
T2 -> T1 conflict hai B par (Write B by T2 before Read B by T1)
Cycle ban gaya = Not Serializable.
Ye 2PL me bhi possible nahi hai.

[ GATE 2003 : 1 Mark]
5.2 Which of the following scenarios may lead to an irrecoverable error in a database system?

(a) A transaction writes a data item after it is read by an uncommitted transaction
(b) A transaction reads a data item after it is read by an uncommitted transaction
(c) A transaction reads a data item after it is written by a committed transaction
(d) A transaction reads a data item after it is written by an uncommitted transaction

Solution:
Answer: (d)
Irrecoverable tab hota hai jab T2 uncommitted T1 ka likha hua data padh le aur commit ho jaye, fir T1 rollback ho jaye to.
So (d) A transaction reads a data item after it is written by an uncommitted transaction.

[GATE 2003 : 2 Marks]

5.3 Consider three data items D1, D2, and D3, and the following execution schedule of transactions T1, T2 and T3. In the diagram, R(D) and W(D) denote the actions reading and writing the data item D respectively.T1: R(D1); W(D1); R(D2); W(D2);
T2: R(D3); R(D2); W(D2); R(D1); W(D1);
T3: R(D2); R(D3); W(D2); W(D3);(a) The schedule is serializable as T2; T3; T1
(b) The schedule is serializable as T2; T1; T3
(c) The schedule is serializable as T3; T2; T1
(d) The schedule is not serializable

Solution:
Answer: (d) Not Serializable
Precedence Graph:
T1 -> T3? No. Check karo to D2 par T1->T2->T3->T1 ka cycle ban raha hai.

[GATE 2004 : 1 Mark]
5.4 Which level of locking provides the highest degree of concurrency in a relational database?

(a) Page
(b) Table
(c) Row
(d) Page, table and row level locking allow the same degree of concurrency

Solution:
Answer: (c) Row
Row level sabse chhota lock hota hai, to sabse zyada concurrency deta hai.

[GATE 2004 : 2 Marks]

5.5 Consider the following schedule S of transactions T1 and T2:T1: Read(A), A=A-10, Write(A), Read(B), B=B+10, Write(B)
T2: Read(A), Temp=0.2*A, Write(A), Read(B), B=B+Temp, Write(B)Interleaving is as given in diagram.Which of the following is TRUE about schedule S ?
(a) S is serializable only as T1, T2
(b) S is serializable only as T2, T1
(c) S is serializable both as T1, T2 and T2, T1
(d) S is not serializable either as T1 or as T2

Solution:
Answer: (a) S is serializable only as T1, T2
Is schedule me A ka conflict T1->T2 hai, B ka bhi T1->T2 hai. No cycle. So T1->T2 ke jaisa serializable hai.

[GATE 2005 : 1 Mark]

5.6 Amongst the ACID properties of a transaction, the ‘Durability’ property requires that the changes made to the database by a successful transaction persist(a) Except in case of an Operating System crash
(b) Except in case of a Disk crash
(c) Except in case of a power failure
(d) Always, even if there is a failure of any kind

Solution:
Answer: (d) Always, even if there is a failure of any kind
Durability ka matlab hi yahi hai, ek baar commit ho gaya to data disk par permanent hai, koi bhi failure ho jaye.

[GATE 2006 : 1 Mark]
5.7: Consider the following log sequence of two transactions on a bank account, with initial balance 12000, that transfer 2000 to a mortgage payment and, then apply a 5% interest.T1: startT1: B old = 12000, new = 10000T1: M old = 0, new = 2000T1: commitT2: startT2: B old = 10000, new = 10500T2: commit
Suppose the database system crashed just before log record 7 is written. When the system is restarted, which one statement is true of the recovery procedure?(a) We must redo log record 6 to set B to 10500
(b) We must undo log record 6 to set B to 10000 and then redo log records 2 and 3
(c) We need not redo log records 2 and 3 because transaction T1 has committed
(d) We can apply redo and undo operations in arbitrary order because they are idempotentSolution:

Answer: (b)
Explanation: T1 committed hai to usko REDO karna padega (2,3). T2 commit nahi hua crash se pehle, to usko UNDO karna padega (6 ko undo karke B=10000). Isliye (b) sahi hai.

[GATE 2007 : 2 Marks]
5.8 Consider the following schedules involving two transactions. Which one of the following statements is TRUE?
S1: r1(X); r1(Y); r2(X); r2(Y); w2(Y); w1(X)
S2: r1(X); r2(X); r2(Y); W2(Y); r1(Y); w1(X)(a) Both S1 and S2 are conflict serializable
(b) S1 is conflict serializable and S2 is not conflict serializable
(c) S1 is not conflict serializable and S2 is conflict serializable
(d) Both S1 and S2 are not conflict serializableSolution:
Answer: (c) S1 is not conflict serializable and S2 is conflict serializable
Explanation:
S1 me: X par T1->T2 (r1(X) before r2(X) but w1(X) after) aur Y par T2->T1 (r2(Y) before w2(Y) but r1(Y) before w2(Y) actually). Check karne par cycle banta hai – S1 not serializable.
S2 me: Dono conflict T1->T2 ya T2->T1 ek hi direction me hai, cycle nahi hai – S2 serializable hai

(a) S1 :
lock S(A); read(A); lock S(B); read(B); if A = 0 then B <- B+1; write(B); commit; unlock(A); unlock(B);
S2 :
lock S(B); read(B); lock S(A); read(A); if B != 0 then A <- A-1; write(A); commit; unlock(B); unlock(A);(b) S1 :
lock X(A); read(A); lock X(B); read(B); if A = 0 then B <- B+1; write(B); unlock(A); commit; unlock(B);
S2 :
lock X(B); read(B); lock X(A); read(A); if B != 0 then A <- A-1; write(A); unlock(A); commit; unlock(A);(c) S1 :
lock S(A); read(A); lock X(B); read(B); if A = 0 then B <- B+1; write(B); unlock(A); commit; unlock(B);
S2 :
lock S(B); read(B); lock X(A); read(A); if B != 0 then A <- A-1; write(A); unlock(A); commit; unlock(A);(d) S1 :
lock S(A); read(A); lock X(B); read(B); if A = 0 then B <- B+1; write(B); unlock(A); unlock(B); commit;
S2 :
lock S(B); read(B); lock X(A); read(A); if B != 0 then A <- A-1; write(A); unlock(A); unlock(B); commit;

Solution:
Answer: (c)
Explanation:
(a) me dono ne S lock liya hai, lekin write kar rahe hain, X lock chahiye – galat.
(b) me X lock shuru me le liya, deadlock ho sakta hai, aur unlock commit se pehle kar diya.
(d) me unlock commit se pehle kar diya, Strict 2PL violate ho raha hai.
(c) sahi hai kyunki S(A)/S(B) se read kiya, fir X(B)/X(A) se write kiya, aur unlock commit ke baad me hai + order aisa hai ki deadlock nahi hoga.

[GATE 2008 : 2 Marks]
5.10: Consider the following three schedules of transactions T1, T2 and T3. [Notation: In the following NYO represents the action Y (R for read, W for write) performed by transaction N on object O].
S1: 2RA 2WA 3RC 2WB 3WA 3WC 1RA 1RB 1WA 1WB
S2: 3RC 2RA 2WA 2WB 3WA 1RA 1RB 1WA 1WB 3WC
S3: 2RA 3RC 3WA 2WA 2WB 3WC 1RA 1RB 1WA 1WBWhich of the following statements is TRUE?
(a) S1, S2 and S3 are all conflict equivalent to each other
(b) No two of S1, S2 and S3 are conflict equivalent to each other
(c) S2 is conflict equivalent to S3, but not to S1
(d) S1 is conflict equivalent to S2, but not to S3

Solution:
Answer: (c) S2 is conflict equivalent to S3, but not to S1
Explanation: S1 me T2->T3 conflict hai jo S2/S3 me alag hai. S2 aur S3 ka final precedence order same hai (T2->T3->T1), isliye wo conflict equivalent hain.

[GATE 2009 : 2 Marks]
5.11: Consider two transactions T1 and T2, and four schedules S1, S2, S3, S4 of T1 and T2 as given below:
T1 : R1 W1 W1
T2 : R2 R2 W2
S1 : R1 R2 R2 W1 W1 W2
S2 : R1 R2 R2 W1 W2 W1
S3 : R1 W1 R2 W1 R2 W2
S4 : R2 R2 R1 W1 W1 W2[x][y]Which of the above schedules are conflict-serializable?
(a) S1 and S2 (b) S2 and S3 (c) S3 only (d) S4 only

Solution:
Answer: (b) S2 and S3
Explanation:
S1 me R2->W1 aur W1->W2 se cycle T1->T2 aur T2->T1 ban raha hai = Not serializable.
S2 me order T1->T2 hai, no cycle = Serializable.
S3 me T1 pura pehle, fir T2 = Serializable.
S4 me bhi cycle hai. Isliye S2 aur S3 sahi hain.[y]

[ GATE 2010 : 1 Mark]
5.12:Which of the following concurrency control protocols ensure both conflict serializability and freedom from deadlock?
I. 2-phase locking
II. Time-stamp ordering(a) I only (b) II only (c) Both I and II (d) Neither I nor IISolution:
Answer: (b) II only
Explanation: 2PL conflict serializability to deta hai, lekin deadlock guarantee nahi deta, deadlock ho sakta hai. Time-stamp ordering deadlock free hota hai aur conflict serializable bhi hota hai. Isliye sirf II

[GATE 2010 : 2 Marks]
5.13: Consider the following schedule for transactions T1, T2 and T3:
T1: Read(X)
T2: Read(Y)
T3: Read(Y)
T2: Write(Y)
T1: Write(X)
T3: Write(X)
T2: Read(X)
T2: Write(X)Which one of the schedules below is the correct serialization of the above?
(a) T1 -> T3 -> T2
(b) T2 -> T1 -> T3
(c) T2 -> T3 -> T1
(d) T3 -> T1 -> T2

Solution:
Answer: (a) T1 -> T3 -> T2
Explanation:
Y par T2 -> T3? Actually Read(Y) dono ne kiya, Write(Y) T2 ne kiya. X par T1->T3->T2 conflict hai. Precedence graph: T1->T3->T2. Koi cycle nahi, aur serial order T1->T3->T2 sahi baithta hai

[GATE 2012 : 2 Marks]
5.14: Consider the following transactions with data items P and Q initialized to zero:
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);Any non-serial interleaving of T1 and T2 for concurrent execution leads to
(a) a serializable schedule
(b) a schedule that is not conflict serializable
(c) a conflict serializable schedule
(d) a schedule for which a precedence graph cannot be drawnSolution:
Answer: (b) a schedule that is not conflict serializable
Explanation: Dono transactions me P aur Q par read-write conflict ulta hai. Agar non-serial interleaving karoge to T1->T2 aur T2->T1 dono direction me edge banega, cycle ban jayega. Isliye conflict serializable nahi hoga.

[GATE 2013 : 1 Mark]
5.15: An index is clustered, if
(a) it is on a set of fields that form a candidate key.
(b) it is on a set of fields that include the primary key.
(c) the data record of the file are organized in the same order as the data entries of the index
(d) the data records of the file are organized not in the same order as the data entries of the index

Solution:
Answer: (c) the data record of the file are organized in the same order as the data entries of the index
Explanation: Clustered index ka definition hi yahi hai ki data file ka physical order index ke order jaisa hota hai.

[ GATE 2014 (Set-1) : 2 Marks]

5.16: Consider the following four schedules due to three transactions (indicated by the subscript) using read and write on a data item x, denoted by r(x) and w(x) respectively. Which one of them is conflict serializable?(a) r1(x); r2(x); w1(x); r3(x); w2(x)
(b) r2(x); r1(x); w2(x); r3(x); w1(x)
(c) r3(x); r2(x); r1(x); w2(x); w1(x)
(d) r2(x); w2(x); r3(x); r1(x); w1(x)Solution:
Answer: (d) r2(x); w2(x); r3(x); r1(x); w1(x)
Explanation:
(a) me r2(x)->w1(x) = T2->T1, r1(x)->w2(x) = T1->T2 = cycle.
(b) me bhi cycle.
(c) me bhi cycle.
(d) me sirf T2->T3->T1 ek hi direction me hai, no cycle. Isliye conflict serializable hai.

[GATE 2014 (Set-2) : 2 Marks]
5.17: Consider the following schedule S of transactions T1, T2, T3 and T4:
Schedule order:T2: Reads(X)T3: Writes(X), CommitT1: Writes(X), CommitT2: Writes(Y), Reads(Z), CommitT4: Reads(X), Reads(Y), CommitWhich one of the following statements is CORRECT?
(a) S is conflict-serializable but not recoverable
(b) S is not conflict-serializable but is recoverable
(c) S is both conflict-serializable and recoverable
(d) S is neither conflict-serializable nor is it recoverableSolution:
Answer: (c) S is both conflict-serializable and recoverable
Explanation:
Conflict: X par T2->T3->T1->T4 order hai, no cycle, serializable hai.
Recoverable: T4 ne T1 aur T2 ka likha hua data padha hai, lekin T1, T2 pehle hi commit ho chuke hain (T1 commit before T4 read, T2 commit before T4 read), isliye recoverable hai.

[GATE 2014 : 2 Marks]
5.18: Consider the transactions T1, T2, and T3 and the schedules S1 and S2 given below.
T1: r1(X); r1(Z); w1(X); w1(Z)
T2: r2(Y); r2(Z); w2(Z)
T3: r3(Y); 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)Which one of the following statements about the schedules is TRUE?
(a) Only S1 is conflict-serializable.
(b) Only S2 is conflict-serializable.
(c) Both S1 and S2 are conflict-serializable.
(d) Neither S1 nor S2 is conflict-serializable.Solution:
Answer: (a) Only S1 is conflict-serializable.
Explanation:
S1 me precedence T1->T2->T3 jaisa hai, no cycle.
S2 me X par T3->T1 aur Z par T1->T2->T1 ka cycle ban jata hai, isliye S2 not serializable.

[GATE 2015 (Set-2) : 1 M]

5.19:Consider the following transaction involving two bank accounts x and y.
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
(a) Atomicity
(b) Consistency
(c) Isolation
(d) DurabilitySolution:
Answer: (b) Consistency
Explanation: x + y = constant ye database ka consistency rule hai. Transaction se pehle aur baad me database consistent rehna chahiye, isliye ye Consistency property hai.

[GATE 2015 (Set-2) : 1 M]
5.20:Consider a simple checkpointing protocol and the following set of operations in the log.
(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 recover using both undo and redo operations, what are the contents of the undo list and the redo list(a) Undo: T3, T1; Redo: T2
(b) Undo: T3, T1; Redo: T2, T4
(c) Undo: none; Redo: T2, T4, T3, T1
(d) Undo: T3, T1, T4; Redo: T2Solution:
Answer: (a) Undo: T3, T1; Redo: T2
Explanation:
Checkpoint ke time T1 active tha (start hua, commit nahi). T4 checkpoint se pehle commit ho gaya tha, to uska redo/undo nahi chahiye (kyunki saara data already disk par hai).
Crash ke time:T2: checkpoint ke baad start aur commit dono ho gaya = REDO list me.T1: checkpoint se pehle start, abhi tak commit nahi = UNDO list me.T3: checkpoint ke baad start, commit nahi = UNDO list me.
Isliye Undo: T3, T1; Redo: T2