topic
Transactions
Question 1 
Consider a schedule of transactions T_{1} and T_{2}:
Here, RX stands for “Read(X)” and WX stands for “Write(X)”. Which one of the following schedules is conflict equivalent to the above schedule?
• First, let’s list the conflict operations of each of the schedule given in the options and compare with the conflict operations of schedule which is given in the question.
Given schedule:
Conflict operations:
R2(B) → W1(B)
W2(B) → W1(B)
R1(C) → W2(C)
R2(D) → W1(D)
Option(1):
Conflict operations:
R1(C) → W2(C)
W1(D) → R2(D)
W1(B) → R2(B)
W1(B) → W2(B)
Option(2):
Conflict operations:
R2(B) → W1(B)
W2(B) → W1(B)
R2(D) → W1(D)
R1(C) → W2(C)
Option(3):
Conflict operations:
R2(B) → W1(B)
W2(B) → W1(B)
R2(D) → W1(D)
W2(C) → R1(C)
Option(4):
Conflict operations:
R1(C) → W2(C)
W1(D) → R2(D)
R2(B) → W1(B)
W2(B) → W1(B)
The conflict operations in the option (2) and given schedule are appearing in the same sequence order, so option (2) is the answer.
Question 2 
S: R4(x) R2(x) R3(x) R1(y) W1(y) W2(x) W3(y) R4(y)
Which one of the following serial schedules is conflict equivalent to S
Precedence Graph
T1 → T3 → T4 → T2
Question 3 
1 Read A
2 Read B
3 Write A
4 Read A
5 Write A
6 Write B
7 Read B
8 Write B
This schedule is serialized and can occur in a scheme using 2PL protocol  
This schedule is serializable but cannot occur in a scheme using 2PL protocol  
This schedule is not serialiable but can occur in a scheme using 2PL protocol  
This schedule is not seralisable and cannot occur in a scheme using 2PL protocol. 
Since cycle exist so not conflict serializable.
And we know that if the schedule is not serializable then it is not 2PL.
Hence correct option is (D).
Question 4 
Which of the following scenarios may lead to an irrecoverable error in a database system?
A transaction writes a data item after it is read by an uncommitted transaction  
A transaction reads a data item after it is read by an uncommitted transaction  
A transaction reads a data item after it is written by a committed transaction  
A transaction reads a data item after it is written by an uncommitted transaction

Question 5 
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.
Which of the following statements is correct?
The schedule is serializable as T2; T3; T1
 
The schedule is serializable as T2; T1; T3  
The schedule is serializable as T3; T2; T1  
The schedule is not serializable 
Cycle formed so not serializable.
Question 6 
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.
1. T1 start 2. T1 B old=12000 new=10000 3. T1 M old=0 new=2000 4. T1 commit 5. T2 start 6. T2 B old=10000 new=10500 7. T2 commit
Suppose the database system crashes just before log record 7 is written. When the system is restarted, which one statement is true of the recovery procedure?
We must redo log record 6 to set B to 10500.
 
We must undo log record 6 to set B to 10000 and then redo log records 2 and 3.
 
We need not redo log records 2 and 3 because transaction T1 has committed.
 
We can apply redo and undo operations in arbitrary order because they are idempotent. 
So from above theory we can say that option (B) is the correct answer.
Question 7 
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 conflictserializable.
Q: If T_{3}commits before T_{1}finishes, then S is recoverable.
Which one of the following choices is correct?
P is true and Q is false.  
Both P and Q are false.  
Both P and Q are true.  
P is false and Q is true. 
Question 8 
S_{1}: r_{1}(x) r_{1}(y) r_{2}(x) r_{2}(y) w_{2}(y) w_{1}(x)
S_{2}: r_{1}(x) r_{2}(x) r_{2}(y) w_{2}(y) r_{1}(y) w_{1}(x)
Which one of the following options is correct?
S_{1}is not conflict serializable, and S_{2} is conflict serializable.  
Neither S_{1}nor S_{2}is conflict serializable.
 
Both S_{1}and S_{2}are conflict serializable.  
S_{1}is conflict serializable, and S_{2}is not conflict serializable. 
Question 9 
Consider the following two transactions: T_{1} and T_{2}.
T_{1}: read(A); read(B); if A=0 then B ← B+1; write(B); T_{2}: read(B); read(A); if B≠0 then A ← A1; write(A);Which of the following schemes, using shared and exclusive locks, satisfy the requirements for strict two phase locking for the above transactions?
i) Exclusive locks should be released after the commit.
ii) No locking can be done after the first unlock.
(A) Wrong because to write x you need Exclusive Lock.
(B) Wrong because violating the 1^{st} requirement.
(C) Correct.
(D) Wrong because violating the 1^{st} requirement.
Question 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?
S1, S2 and S3 are all conflict equivalent to each other  
No two of S1, S2 and S3 are conflict equivalent to each other  
S2 is conflict equivalent to S3, but not to S1  
S1 is conflict equivalent to S2, but not to S3 
For S1:
For S2:
For S3:
Hence, S1 is conflict equivalent to S2, but not to S3.
Question 11 
Consider the following schedules involving two transactions. Which one of the following statements is TRUE?
 S_{1}: r_{1}(X); r_{1}(Y); r_{2}(X); r_{2}(Y); w_{2}(Y); w_{1}(X)
S_{2}: r_{1}(X); r_{2}(X); r_{2}(Y); w_{2}(Y); r_{1}(Y); w_{1}(X)
Both S_{1} and S_{2} are conflict serializable.  
S_{1} is conflict serializable and S_{2} is not conflict serializable.  
S_{1} is not conflict serializable and S_{2} is conflict serializable.  
Both S_{1} and S_{2} are not conflict serializable. 
In precedence graph of S_{1} since cycle is formed so not conflict serializable.
But in precedence graph of S_{2} No cycle is formed so it is conflict serializable.
Question 12 
Consider the following transaction involving two bank account x and y.
read(x); x:= x50; 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
Atomicity  
Consistency  
Isolation  
Durability 
Question 13 
Consider a simple checkpointing protocol and the following set of operations in the log.

(start, T_{4}); (write, T_{4}, y, 2, 3); (start, T_{1}); (commit, T_{4}); (write, T_{1}, z, 5, 7);
(checkpoint);
(start, T_{2}); (write, T_{2}, x, 1, 9); (commit, T_{2}); (start, T_{3}); (write, T_{3}, 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
Undo T_{3}, T_{1}; Redo T_{2}  
Undo T_{3}, T_{1}; Redo T_{2}, T_{4}  
Undo: none; redo: T_{2}, T_{4}, T_{3}, T_{1}  
Undo T_{3}, T_{1}; T_{4}; Redo: T_{2}

Question 14 
Suppose a database schedule S involves transactions T_{1}, ..., T_{n}. Construct the precedence graph of S with vertices representing the transactions and edges representing the conﬂicts. If S is serializable, which one of the following orderings of the vertices of the precedence graph is guaranteed to yield a serial schedule?
Topological order  
Depthﬁrst order  
Breadthﬁrst order  
Ascending order of transaction indices 
But BFS and DFS are also possible for cyclic graphs.
And topological sort is not possible for cyclic graph.
Moreover option (D) is also wrong because in a transaction with more indices might come before lower one.
Question 15 
S = r_{2}(X); r_{1}(X); r_{2}(Y); w_{1}(X); r_{1}(Y); w_{2}(X); a_{1}; a_{2}
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?
S is nonrecoverable  
S is recoverable, but has a cascading abort  
S does not have a cascading abort  
S is strict 
Now let's check statements one by one,
A) False, because there is no dirty read. So, it is recoverable.
B) False, because there is to dirty read. So, no cascading aborts.
C) True.
D) False, because there is Transaction T_{2} which written the value of x which is written by T_{1} before T_{1} has aborted. So, not strict.
Question 16 
Atomicity  
Consistency  
Isolation  
Deadlockfreedom 
So, Deadlock – freedom is not there in the ACID properties.
Question 17 
T_{1}: r_{1}(X)w_{1}(X)r_{1}(Y)w_{1}(Y)
T_{2}: r_{2}(Y)w_{2}(Y)r_{2}(Z)w_{2}(Z)
where r_{i}(V) denotes a read operation by transaction T_{i} on a variable V and w_{i}(V) denotes a write operation by transaction T_{i} on a variable V. The total number of conflict serializable schedules that can be formed by T_{1} and T_{2} is ____________.
54  
55  
56  
57 
= 8!/(4×3×2×4×3×2)
= (8×7×6×5×4×3×2×1)/(4×3×2×4×3×2)
= 70
Following two conflict actions are possible:
∴# Permutations = 4 × 3 = 12
#permutations = 4 × 1 = 4
∴ Total no. of conflict serial schedules possible = 70  12  4 = 54
Question 18 
I. Strict twophase locking protocol generates conflict serializable schedules that are also recoverable.
II. Timestampordering concurrency control protocol with Thomas Write Rule can generate view serializable schedules that are not conflict serializable.
Which of the above statements is/are TRUE?
Both I and II  
Neither I nor II  
II only  
I only 
In strict 2PL, a transaction T does not release any of its exclusive (write) locks until after it commits or aborts.
Hence, no other transaction can read or write an item that is written by T unless T has committed, leading to a strict schedule for recoverability.
(Ref: Fundamentals of Database Systems by Elmasri and Navathe, 7e Pg. No. 789)
By ignoring the write, Thomas write rule allows schedules that are not conflict serializable but are nevertheless correct.
Those nonconflictserializable schedules allowed satisfy the definition of view serializable schedules.
(Ref: Database System Concepts by Silberschatch, Korth and Sudarshan, 6e Pg No. 686)
Question 19 
Consider the following partial Schedule S involving two transactions T1 and T2. Only the read and the write operations have been shown. The read operation on data item P is denoted by read(P) and the write operation on data item P is denoted by write(P).
T2 must be aborted and then both T1 and T2 must be restarted to ensure transaction atomicity  
Schedule S is nonrecoverable and cannot ensure transaction atomicity  
Only T2 must be aborted and then restarted to ensure transaction atomicity  
Schedule S is recoverable and can ensure atomicity and nothing else needs to be done 
Question 20 
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?
r_{1} (x); r_{2} (x); w_{1} (x); r_{3} (x); w_{2} (x)  
r_{2} (x);r_{1} (x);w_{2} (x);r_{3} (x);w_{1} (x)  
r_{3} (x);r_{2} (x);r_{1} (x);w_{2} (x);w_{1} (x)  
r_{2} (x);w_{2} (x);r_{3} (x);r_{1} (x);w_{1} (x) 
 Polygraph contains cycle. So, not a conflict serializable.
Option: B
Cyclic
Option: C
 Cyclic
Option: D
 Acyclic, so conflict serializable.
Question 21 
Consider two transactions T1 and T2, and four schedules S1, S2, S3, S4 of T1 and T2 as given below:
 T1 = R1[X] W1[X] W1[Y]
T2 = R2[X] R2[Y] W2[Y]
S1 = R1[X] R2[X] R2[Y] W1[X] W1[Y] W2[Y]
S2 = R1[X] R2[X] R2[Y] W1[X] W2[Y] W1[Y]
S3 = R1[X] W1[X] R2[X] W1[Y] R2[Y] W2[Y]
S1 = R1[X] R2[Y]R2[X]W1[X] W1[Y] W2[Y]
Which of the above schedules are conflictserializable?
S_{1} and S_{2}  
S_{2} and S_{3}  
S_{3} only  
S_{4} only 
Dependency graph is,
So, there is no cycle.
Schedule S3,
Dependency graph is,
S_{4} also has a cycle T_{1}→T_{2} and T_{2}→T_{1}.
So, S_{2} and S_{3} are conflict serializable.