Transactions and concurrency control

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

Answer: (B)

GATE CS 2012 Transactions and concurrency control 50 DBMS MCQs with Answers
Discuss it

See http://www.geeksforgeeks.org/database-management-system-set-3/

Question 2

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 II

Answer: (B)

Transactions and concurrency control 50 DBMS MCQs with Answers
Discuss it

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: 

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

Answer: (A)

GATE CS 2010 Transactions and concurrency control
Discuss it

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.

• A. A
• B. B
• C. C
• D. D

Answer: (D)

GATE-CS-2014-(Set-1) Transactions and concurrency control
Discuss it

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:
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

Answer: (C)

GATE-CS-2014-(Set-2) Transactions and concurrency control
Discuss it

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.

Answer: (A)

GATE-CS-2014-(Set-3) Transactions and concurrency control
Discuss it

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 crashes 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

Answer: (B)

GATE-CS-2006 Transactions and concurrency control
Discuss it

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

Answer: (D)

GATE-CS-2003 Transactions and concurrency control
Discuss it

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.
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

Answer: (D)

GATE-CS-2003 Transactions and concurrency control
Discuss it

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

Answer: (B)

GATE-CS-2015 (Set 2) Transactions and concurrency control
Discuss it

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.