Question 1
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 drawn
GATE CS 2012    Transactions and concurrency control    
Discuss it


Question 2
Which of the following concurrency control protocols ensure both conflict serialzability 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 II
GATE CS 2010    Transactions and concurrency control    
Discuss it


Question 2 Explanation: 

2 Phase Locking (2PL) is a concurrency control method that guarantees serializability. The protocol utilizes locks, applied by a transaction to data, which may block (interpreted as signals to stop) other transactions from accessing the same data during the transaction’s life. 2PL may be lead to deadlocks that result from the mutual blocking of two or more transactions. See the following situation, neither T3 nor T4 can make progress.

Timestamp-based concurrency control algorithm is a non-lock concurrency control method. In Timestamp based method, deadlock cannot occur as no transaction ever waits.

Question 3
Consider the following schedule for transactions T1, T2 and T3: GATE2010DBMS1 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
GATE CS 2010    Transactions and concurrency control    
Discuss it


Question 3 Explanation: 

T1 can complete before T2 and T3 as there is no conflict between Write(X) of T1 and the operations in T2 and T3 which occur before Write(X) of T1 in the above diagram.
T3 should can complete before T2 as the Read(Y) of T3 doesn’t conflict with Read(Y) of T2. Similarly, Write(X) of T3 doesn’t conflict with Read(Y) and Write(Y) operations of T2.
Another way to solve this question is to create a dependency graph and topologically sort the dependency graph. After topologically sorting, we can see the sequence T1, T3, T2.

Question 4
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. GATECS2014Q39
A
A
B
B
C
C
D
D
Transactions and concurrency control    GATE-CS-2014-(Set-1)    
Discuss it


Question 4 Explanation: 
In option D, there is no interleaving of operations. The option D has first all operations of transaction 2, then 3 and finally 1 There can not be any conflict as it is a serial schedule with sequence 2 --> 3 -- > 1
Question 5
Consider the following schedule S of transactions T1, T2, T3, T4: GATECS2014Q29 Which 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 recoverable
Transactions and concurrency control    GATE-CS-2014-(Set-2)    
Discuss it


Question 5 Explanation: 
To check for conflict-serializable, we need to make a precedence graph, if the graph contains a cycle, then it's not conflict serializable, else it is. Here, for the precedence graph there will be only two directed edges, one from T2 -> T3 ( Read- Write Conflict), and another from T2 -> T1( Read- Write Conflict), hence no cycle, so the schedule is conflict serializable. Now to check for Recoverable, we need to check for a dirty-read operation( Write by Transaction Ti, followed by Read by Transaction Tj but before Ti commits) between any pair of operations. If no dirty-read then recoverable schedule, if a dirty read is there then we need to check for commit operations. Here no dirty read operation ( as T3 and T1 commits before T4 reads the Write(X) of T3 and T1 , and T2 commits before T4 reads the Write(Y) of T2 ). Therefore the schedule is recoverable. Hence, Option C.
Question 6
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.
Transactions and concurrency control    GATE-CS-2014-(Set-3)    
Discuss it


Question 6 Explanation: 
For conflict serializability of a schedule( which gives same effect as a serial schedule ) we should check for conflict operations, which are Read-Write, Write-Read and Write-Write between each pair of transactions, and based on those conflicts we make a precedence graph, if the graph contains a cycle, it's not a conflict serializable schedule. To make a precedence graph: if Read(X) in Ti followed by Write(X) in Tj ( hence a conflict ), then we draw an edge from Ti to Tj ( Ti -> Tj) If we make a precedence graph for S1 and S2 , we would get directed edges for S1 as T2->T1, T2->T3, T3->T1, and for S2 as T2->T1, T2->T3, T3->T1, T1->T2. In S1 there is no cycle, but S2 has a cycle. Hence only S1 is conflict serializable. Note : The serial order for S1 is T2 -> T3 -> T1.
Question 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.
  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 cra shes 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 idempotent
Transactions and concurrency control    GATE-CS-2006    
Discuss it


Question 7 Explanation: 
We must undo log record 6 to set B to 10000 and then redo log records 2 and 3 bcoz system fail before commit operation. So we need to undone active transactions(T2) and redo committed transactions (T1) Note:Here we are not using checkpoints. Checkpoint : Checkpoint is a mechanism where all the previous logs are removed from the system and stored permanently in a storage disk. Checkpoint declares a point before which the DBMS was in consistent state, and all the transactions were committed. Recovery: When a system with concurrent transactions crashes and recovers, it behaves in the following manner − =>The recovery system reads the logs backwards from the end to the last checkpoint. =>It maintains two lists, an undo-list and a redo-list. =>If the recovery system sees a log with and or just , it puts the transaction in the redo-list. =>If the recovery system sees a log with but no commit or abort log found, it puts the transaction in undo-list. All the transactions in the undo-list are then undone and their logs are removed. All the transactions in the redo-list and their previous logs are removed and then redone before saving their logs. So Answer is B redo log records 2 and 3 and undo log record 6
Question 8
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
Transactions and concurrency control    GATE-CS-2003    
Discuss it


Question 8 Explanation: 
Option C is a normal operation. Option B is also fine as no write operation is involved. Option A can be recovered, but option D can't be. See this for an example.
Question 9
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. GATECS2003Q87 Which of the following statements is correct?
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
Transactions and concurrency control    GATE-CS-2003    
Discuss it


Question 9 Explanation: 
T1 and T2 have conflicting operations between them forming a cycle in the precedence graph. R(D2) of T2, and W(D2) of T1 ( Read-Write Conflict) R(D1) of T1, and W(D1) of T2 ( Read-Write Conflict) Hence in the precedence graph of the schedule there would be a cycle between T1 and T2 vertices. Therefore not a serializable schedule.
Question 10
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
Durability
Transactions and concurrency control    GATE-CS-2015 (Set 2)    
Discuss it


Question 10 Explanation: 
Consistency in database systems refers to the requirement that any given database transaction must only change affected data in allowed ways, that is sum of x and y must not change.
Question 11
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: T2
Transactions and concurrency control    GATE-CS-2015 (Set 2)    
Discuss it


Question 11 Explanation: 
Since T1 and T3 are not committed yet, they must be undone. The transaction T2 must be redone because it is after the latest checkpoint.
Question 12
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). Q39 Suppose that the transaction T1 fails immediately after time instance 9. Which one of the following statements is correct?
A
T2 must be aborted and then both T1 and T2 must be re-started to ensure transaction atomicity
B
Schedule S is non-recoverable and cannot ensure transaction atomicity
C
Only T2 must be aborted and then re-started to ensure transaction atomicity
D
Schedule S is recoverable and can ensure atomicity and nothing else needs to be done
Transactions and concurrency control    GATE-CS-2015 (Set 3)    
Discuss it


Question 12 Explanation: 
  if transaction fails, atomicity requires effect of transaction to be undone. Durability states that once transaction commits, its change cannot be undone (without running another, compensating, transaction). Recoverable schedule: A schedules exactly where, for every set of transaction Ti and Tj. If Tj reads a data items previously written by Ti, then the commit operation of Ti precedes the commit operation of Tj. Aborting involves undoing the operations and redoing them since by the time stamp it is aborted. Option (A): T2 must be aborted and then both T1 and T2 must be re-started to ensure transaction atomicity. It is incorrect because it says abort transaction T2 and then redo all the operations. But there is no gaurantee that it will succeed this time as again T1 may be fail. Option(B): Schedule S is non-recoverable and cannot ensure transaction atomicity. Correct, it is by definition an irrecoverable schedule so now even if we start to undo the actions one by one(after t1 fails) in order to ensure transaction atomicity. Still we cannot undo a commited transaction. hence this schedule is irrecoverable by definition and also not atomic since it leaves the database in an inconsistent state. Simply dirty read so nonrecoverable. Option (C): Only T2 must be aborted and then re-started to ensure transaction atomicity. It is incorrect because it says abort only transaction T2 and then redo all the T2 operations. But this is dirty read problem as it is reading the data item A which is written by T1 and T1 is not committed. Again it will be the dirty read problem. So incorrect. Option (D): Schedule S is recoverable and can ensure transaction atomicity and nothing else needs to be done. Incorrect, it is clearly saying that schedule s is recoverable but it is irrecoverable because T2 read the data item A which is written by T1 and T1 failed and rollback, at the rollback T1 start undo all operations and modified the value of A with previous value but T2 is already committed so T2 can't change the read value of A which was earlier taken from T1.   This solution is contributed by Nitika Bansa.
Question 13
Which level of locking provides the highest degree of concurrency in a relational data base?  
A
Page
B
Table
C
Row
D
Page, table and row level locking allow the same degree of concurrency
Transactions and concurrency control    GATE-IT-2004    
Discuss it


Question 13 Explanation: 
  • Page level locking locks whole page i.e all rows therefore highly restrictive
  • Table locking is mainly used for concurrency control with DDL operations
  • A row share table lock is the least restrictive, and has the highest degree of concurrency for a table.It indicates the transaction has locked rows in the table and intends to update them.
Therefore Row level provides highest level of concurrency.Hence Answer is C
Question 14
Which one of the following is NOT a part of the ACID properties of database transactions?
A
Atomicity
B
Consistency
C
Isolation
D
Deadlock-freedom
Transactions and concurrency control    GATE-CS-2016 (Set 1)    
Discuss it


Question 14 Explanation: 
D refers to Durability.
Question 15
Consider the following two phase locking protocol. Suppose a transaction T accesses (for read or write operations), a certain set of objects {O1,...,Ok}. This is done in the following manner: Step 1. T acquires exclusive locks to O1, . . . , Ok in increasing order of their addresses. Step 2. The required operations are performed. Step 3. All locks are released. This protocol will
A
guarantee serializability and deadlock-freedom
B
guarantee neither serializability nor deadlock-freedom
C
guarantee serializability but not deadlock-freedom
D
guarantee deadlock-freedom but not serializability
Transactions and concurrency control    GATE-CS-2016 (Set 1)    
Discuss it


Question 15 Explanation: 
The above scenario is Conservative 2PL( or Static 2PL). In Conservative 2PL protocol, a transaction has to lock all the items it access before the transaction begins execution. It is used to avoid deadlocks. Also, 2PL is  conflict serializable, therefore it guarantees serializability. Therefore option A Advantages of Conservative 2PL :
  • No possibility of deadlock.
  • Ensure serializability.
Drawbacks of Conservative 2PL :
  • Less throughput and resource utilisation because it holds the resources before the transaction begins execution.
  • Starvation is possible since no restriction on unlock operation.
  • 2pl is a deadlock free protocol but it is difficult to use in practice.
Question 16
Suppose a database schedule S involves transactions T1, ....Tn. Construct the precedence graph of S with vertices representing the transactions and edges representing the conflicts. If S is serializable, which one of the following orderings of the vertices of the precedence graph is guaranteed to yield a serial schedule?
A
Topological order
B
Depth-first order
C
Breadth-first order
D
Ascending order of transaction indices
Transactions and concurrency control    GATE-CS-2016 (Set 2)    
Discuss it


Question 16 Explanation: 
Cycle in precedence graph tells that schedule is not conflict serializable. DFS and BFS traversal of graph are possible even if graph contains cycle. And hence DFS and BFS are also possible for non serializable graphs. But Topological sort of any cyclic graph is not possible. Thus topological sort guarantees graph to be serializable . Option D is not valid because in a transaction with more indices might have to come before lower one. Also two non- conflicting schedule can occur simultaneously. This explanation has been contributed by Abhishek Kumar.
Question 17
Consider the following database schedule with two transactions, T1 and T2.
S = r2(X); r1(X); r2(Y); w1(X); r1(Y); w2(X); a1; a2;
where ri(Z) denotes a read operation by transaction Ti on a variable Z, wi(Z) denotes a write operation by Ti on a variable Z and ai denotes an abort by transaction Ti . Which one of the following statements about the above schedule is TRUE?
A
S is non-recoverable
B
S is recoverable, but has a cascading abort
C
S does not have a cascading abort
D
S is strict
Transactions and concurrency control    GATE-CS-2016 (Set 2)    
Discuss it


Question 17 Explanation: 
gate-cs2016-61-set-2 As we can see in figure,
  • T2 overwrites a value that T1 writes
  • T1 aborts: its “remembered” values are restored.
  • Cascading Abort could have arised if - > Abort of T1 required abort of T2 but as T2 is already aborted , its not a cascade abort. Therefore, Option C
Option A - is not true because the given schedule is recoverable Option B - is not true as it is recoverable and avoid cascading aborts; Option D - is not true because T2 is also doing abort operation after T1 does, so NOT strict.
Question 18
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 transac­tion 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 1WB
Which 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
Transactions and concurrency control    Gate IT 2008    
Discuss it


Question 18 Explanation: 
2008_63_sol All the conflicting pairs like (3WA, 1WA) are in the same order in both S1 and S2.
Question 19
Which of the following statement is/are incorrect?
A: A schedule following strict two phase locking protocol is conflict serializable as well as recoverable.
B: Checkpoint in schedules are inserted to ensure recoverability.
A
Only 1
B
Only 2
C
Both 1 and 2
D
None
Transactions and concurrency control    GATE 2017 Mock    
Discuss it


Question 19 Explanation: 
Basic two phase locking protocol ensures only conflict serializability and strict two phase locking protocol ensures recoverability as well. So statement A is correct. Checkpoints are inserted to minimize the task of undo-redo in recoverability. So, statement B is not correct. Hence correct option B is correct choice.
There are 19 questions to complete.

GATE CS Corner


See Placement Course for placement preparation, GATE Corner for GATE CS Preparation and Quiz Corner for all Quizzes on GeeksQuiz.