Transactions

Question 1

Consider the following two statements about database transaction schedules:

    I. Strict two-phase locking protocol generates conflict serializable schedules that are also recoverable.
    II. Timestamp-ordering 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?

A
Both I and II
B
Neither I nor II
C
II only
D
I only
       Database-Management-System       Transactions       GATE 2019
Question 1 Explanation: 
(Memory-based question)
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 non-conflict-serializable schedules allowed satisfy the definition of view serializable schedules.
(Ref: Database System Concepts by Silberschatch, Korth and Sudarshan, 6e Pg No. 686)
Question 2

Two transactions T1 and T2 are given as

    T1: r1(X)w1(X)r1(Y)w1(Y)
    T2: r2(Y)w2(Y)r2(Z)w2(Z)

where ri(V) denotes a read operation by transaction Ti on a variable V and wi(V) denotes a write operation by transaction Ti on a variable V. The total number of conflict serializable schedules that can be formed by T1 and T2 is ____________.

A
54
B
55
C
56
D
57
       Database-Management-System       Transactions       GATE 2017 [Set-2]
Question 2 Explanation: 
From the given transactions T1 and T2, total number of schedules possible = (4+4)!/4!4!
= 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 3

NOT a part of the ACID properties of database transactions?

A
Atomicity
B
Consistency
C
Isolation
D
Deadlock-freedom
       Database-Management-System       Transactions       GATE 2016 [Set-1]
Question 3 Explanation: 
A transaction in a database system must maintain Atomicity, Consistency, Isolation and Durability – commonly known as ACID properties.
So, Deadlock – freedom is not there in the ACID properties.
Question 4

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
       Database-Management-System       Transactions       GATE 2016 [Set-2]
Question 4 Explanation: 
If a schedule is conflict serializable then no cycle in precedence graph should be present.
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 5

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
       Database-Management-System       Transactions       GATE 2016 [Set-2]
Question 5 Explanation: 
Given schedule is,

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 T2 which written the value of x which is written by T1 before T1 has aborted. So, not strict.
Question 6

Consider the following transaction involving two bank account 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
       Database-Management-System       Transactions       GATE 2015 [Set-2]
Question 6 Explanation: 
The consistency property ensures that the database remains in a consistent state before the (start of the transaction and after the transaction is over. Here sum of the accounts x & y should remain same before & after execution of the given transactions which refers to the consistency of the sum.
Question 7

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
       Database-Management-System       Transactions       GATE 2015 [Set-2]
Question 7 Explanation: 
As T1 & T3 are not yet committed they must be undone. The transactions which are after the latest checkpoint must be redone. So T2 must be redone. No need to redo the records which are before the last checkpoint, so T4 need not be redone.
Question 8

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

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
       Database Management System       Transactions       GATE 2015 [Set-3]
Question 8 Explanation: 
T2 is reading the value written by T1 and getting committed before T1 commits. So it is non-recoverable schedule.
Question 9

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)
       Database-Management-System       Transactions       GATE 2014 [Set-1]
Question 9 Explanation: 
Option: A

- Polygraph contains cycle. So, not a conflict serializable.
Option: B

-Cyclic
Option: C

- Cyclic
Option: D

- Acyclic, so conflict serializable.
Question 10

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
       Database-Management-System       Transactions       GATE 2014 [Set-2]
Question 10 Explanation: 

In the precedence graph, there are no cycles. So, it is conflict serializable and recoverable also.
Question 11

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
       Database-Management-System       Transactions       GATE 2012
Question 11 Explanation: 

The above schedule is not conflict serializable.
Question 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 II
       Database-Management-System       Transactions       GATE 2010
Question 12 Explanation: 
― Two-phase locking protocol (2PLP) ensures the conflict serializable schedule but it may not free from deadlock.
― Timestamp ordering protocol ensures conflict serializability and free from deadlock.
Question 13

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
       Database-Management-System       Transactions       GATE 2010
Question 13 Explanation: 
The given schedule is

― Precedence graph for the above schedule is,

― From the precedence graph the correct serialization is,
Question 14

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 conflict-serializable?

A
S1 and S2
B
S2 and S3
C
S3 only
D
S4 only
       Database-Management-System       Transactions       GATE 2009
Question 14 Explanation: 
S1 has a cycle from T1→T2 and T2→T1 Schedule S2,

Dependency graph is,

So, there is no cycle.
Schedule S3,

Dependency graph is,

S4 also has a cycle T1→T2 and T2→T1.
So, S2 and S3 are conflict serializable.
Question 15

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 serializable.
       Database-Management-System       Transactions       GATE 2007
Question 15 Explanation: 

In precedence graph of S1 since cycle is formed so not conflict serializable.
But in precedence graph of S2 No cycle is formed so it is conflict serializable.
Question 16

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.
       Database-Management-System       Transactions       GATE 2006
Question 16 Explanation: 
When the database system crashes after the transactions have committed then we need to redo the log records. And if the database system crashes before the transactions have committed then we need to undo the log records.
So from above theory we can say that option (B) is the correct answer.
Question 17

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
       Database-Management-System       Transactions       GATE 2003
Question 17 Explanation: 
Irrecoverable error occurs when a transaction reads a data item after it is written by uncommitted transaction.
Question 18

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
       Database-Management-System       Transactions       GATE 2003
Question 18 Explanation: 
Precedence graph:

Cycle formed so not serializable.
Question 19

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 serialized 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 serialiable but can occur in a scheme using 2PL protocol
D
This schedule is not seralisable and cannot occur in a scheme using 2PL protocol.
       Database-Management-System       Transactions       GATE 1999
Question 19 Explanation: 
Let's draw precedence graph,

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 20

Consider a schedule of transactions T1 and T2:

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?

A
B
C
D
       Database-Management-System       Transactions       GATE 2020
Question 20 Explanation: 
• Two schedules are said to be conflict equivalent, if conflict operations in both the schedules are executed in the same order.
• 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 21

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
       Database-Management-System       Transactions       GATE 2008-IT
Question 21 Explanation: 
Two schedules are conflict equivalent when the precedence graph are isomorphic.
For S1:

For S2:

For S3:

Hence, S1 is conflict equivalent to S2, but not to S3.
Question 22

Consider the following two transactions: T1 and T2.

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

Which of the following schemes, using shared and exclusive locks, satisfy the requirements for strict two phase locking for the above transactions?

A
B
C
D
       Database-Management-System       Transactions       GATE 2007-IT
Question 22 Explanation: 
For strict 2PL the requirements are,
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 1st requirement.
(C) Correct.
(D) Wrong because violating the 1st requirement.
Question 23
Let us assume that transaction T1 has arrived before transaction T2. Consider the schedule
S=r1(A); r2(B) ; w2(A); w1(B)
Which of the following is true?
A
Allowed under basic timestamp protocol.
B
Not allowed under basic timestamp protocols because T1 is rolled back
C
Not allowed under basic timestamp protocols because T2 is rolled back
D
None of these
       Database-Management-System       Transactions       ISRO-2018
Question 23 Explanation: 

→ There are 2 conflicting actions a and b is shown in above diagram.
→ In timestamp ordering protocol, conflicting actions in ascending order time-stamps are allowed i.e 'a' is allowed but not 'b'.
→ So we need to roll back T1 after that only it will be allowed. Because of all conflicting actions in ascending order timestamps in below diagram.

Question 24
Which of the following concurrency control protocol ensures both conflict serializability and free from deadlock?
A
Timestamp ordering
B
2 Phase Locking
C
Both (a) and (b)
D
None of the above
       Database-Management-System       Transactions       ISRO-2017 May
Question 24 Explanation: 
→ Two-phase locking protocol (2PL) ensures the conflict serializable schedule but it may not free from deadlock.
→ Timestamp ordering protocol ensures conflict serializability and free from deadlock.
Question 25
ACID properties of a transactions are
A
Atomicity, consistency, isolation, database
B
Atomicity, consistency, isolation, durability
C
Atomicity, consistency, integrity, durability
D
Atomicity, consistency, integrity, database
       Database-Management-System       Transactions       ISRO-2017 May
Question 25 Explanation: 
Atomicity: Execute the all the operations or none of them
→ Responsible for Transaction Manager
Consistency: Database should be consistent before and after the execution of the transaction
→ Responsible for user/application manager
Isolation: Each transaction Tie must be executed without knowing what is happening with other transactions. Responsible for Concurrency control manager
Durability: All updates done by a transaction must become permanent.
→ Responsible for recovery manager
Question 26
A locked database file can be
A
Accessed by only one user
B
Modified by users with the correct password
C
Used to hide sensitive information
D
Updated by more than one user
       Database-Management-System       Transactions       ISRO CS 2009
Question 26 Explanation: 
File locking is a data management feature that restricts other users from changing a specific file.
This allows only one user or process access to this file at any given time.
This is to prevent the problem of interceding updates on the same files.
Question 27
Which of the following contains complete record of all activity that affected the contents of a database during a certain period of time?
A
Transaction log
B
Query language
C
Report writer
D
Data manipulation language
       Database-Management-System       Transactions       ISRO CS 2009
Question 27 Explanation: 
The transaction log is an integral part of database.
Every database has a transaction log that is stored within the log file that is separate from the data file.
A transaction log basically records all database modifications. When a user issues an INSERT, for example, it is logged in the transaction log.
This enables the database to roll back or restore the transaction if a failure were to occur and prevents data corruption.
Question 28
What is the equivalent serial schedule for the following transactions?
A
T1 − T2 − T3
B
T3 − T1 − T2
C
T2 − T1 − T3
D
T1 − T3 − T2
       Database-Management-System       Transactions       ISRO CS 2011
Question 28 Explanation: 
From the following precedence graph, T3 → T1→ T2

Question 29
Which of the following is the highest isolation level in transaction management?
A
Serializable
B
Repeated Read
C
Committed Read
D
Uncommitted Read
       Database-Management-System       Transactions       ISRO CS 2013
Question 29 Explanation: 
→Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions.
→Isolation levels are described in terms of which concurrency side effects, such as dirty reads or phantom reads, are allowed.
→The highest isolation level, serializable, guarantees that a transaction will retrieve exactly the same data every time it repeats a read operation, but it does this by performing a level of locking that is likely to impact other users in multi-user systems.
Question 30

Consider the following schedules involving two transactions.

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)

Which one of the following statements is correct with respect to above ?

A
Both S1 and S2 are conflict serializable.
B
Both S1 and S2 are not conflict serializable.
C
S1 is conflict serializable and S2 is not conflict serializable.
D
S1 is not conflict serializable and S2 is conflict serializable.
       Database-Management-System       Transactions       UGC-NET CS 2018 JUNE Paper-2
Question 30 Explanation: 
Question 31
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.
  1. r1(x); r2(x); w1(x); r3(x); w2(x)
  2. r2(x); r1(x); w2(x); r3(x); w1(x)
  3. r3(x); r2(x); r1(x); w2(x); w1(x)
  4. r2(x); w2(x); r3(x); r1(x); w1(x)
A
1
B
2
C
3
D
4
       Database-Management-System       Transactions       Nielit Scientist-B CS 22-07-2017
Question 31 Explanation: 



Question 32
Global locks
A
synchronize access to local resources
B
synchronize access to global resources
C
are used to avoid local locks
D
prevent access to global resources
       Database-Management-System       Transactions       Nielit Scientist-B CS 2016 march
Question 32 Explanation: 
● Many of the resources protected by local locks in single-instance Oracle require global exposure in a parallel server database.
● Whenever one of these local locks is needed, a corresponding instance lock must be held as well, to protect the resource across all instances. The instance locks used to protect local locks globally are called global locks
Question 33

Consider the following sequence of two transactions on a bank account(A) with initial balance 20,000 that transfers 5,000 to another account (B) and then apply 10% interest.

    (i)   T1 start
    (ii)  T1 A old=20000 new 15,000
    (iii) T1 B old=12000 new=17000
    (iv)  T1 commit
    (v)   T2 start
    (vi)  T2 A old=15000 new=16500
    (vii) T2 commit

Suppose the database system crashes out just before log record (vii) is written. When the system is restricted, which one statement is true of the recovery process ?

A
We can apply redo and undo operation in arbitrary order because they are idempotent.
B
We must redo log record (vi) to set A to 16,500.
C
We must undo log record (vi) to set A to 16,500 and then redo log record (ii) and (iii).
D
We need not redo records (ii) and (iii) because transaction T1 has committed.
       Database-Management-System       Transactions       UGC-NET CS 2018 DEC Paper-2
Question 33 Explanation: 
In log based recovery we must perform 'Redo' operation for those transactions that contains both start and commit log record.
We perform 'undo' operation for those transaction that contains only start but not commit log record.
Therefore we perform 'Redo' of T1 and 'Undo' of T2.
Note: Actually they given option-3 is “ We must redo log record (vi) to set A to 16,500 and then redo log record (ii) and (iii)”. But we found it is wrong. Given correct option instead of wrong one.
Question 34
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=1200 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.
       Database-Management-System       Transactions       ISRO CS 2015
Question 34 Explanation: 
→ When the database system crashes after the transactions have committed then we need to redo the log records.
→ And if the database system crashes before the transactions have committed then we need to undo the log records.
So from above theory we can say that option (B) is the correct Solution.
Question 35
When transaction T​ i​ requests a data item currently held by T​ j​ , T​ j​ is allowed to wait only if it has a timestamp smaller than that of T​ j​ (that is, T​ i​ older than T​ j​ ). Otherwise, T​ i​ is rolled back(dies). this is
A
Wait-die
B
Wait-wound
C
Wound-Wait
D
Wait
       Database-Management-System       Transactions       Nielit Scientific Assistance IT 15-10-2017
Question 35 Explanation: 
Wait-Die method
In this method, if a transaction requests to lock a resource (data item), which is already held with a conflicting lock by another transaction, then one of the two possibilities may occur −
● If TS(T​ i​ ) < TS(T​ j​ ) − that is T​ i​ , which is requesting a conflicting lock, is older than T​ j​ − then T​ i​ is allowed to wait until the data-item is available.
● If TS(T​ i​ ) > TS(t​ j​ ) − that is T​ i​ is younger than T​ j​ − then T​ i​ dies. T​ i​ is restarted later with a random delay but with the same timestamp.
This method allows the older transaction to wait but kills the younger one.
Question 36
Assume transaction A holds a shared lock R. If transaction B also requests for a shared lock on R. It will
A
result in deadlock situation
B
immediately be granted
C
immediately be rejected
D
be granted as soon as it is released by A
       Database-Management-System       Transactions       Nielit Scientific Assistance IT 15-10-2017
Question 36 Explanation: 
● Shared locks exist when two transactions are granted read access.
● One transaction gets the shared lock on data and when the second transaction requests the same data it is also given a shared lock.
● Both transactions are in a read-only mode, updating the data is not allowed until the shared lock is released.
● There is no conflict with the shared lock because nothing is being updated.
● Shared locks last as long as they need to last; it depends on the level of the transaction that holds the lock.
Question 37
Assume transaction A holds a shared lock R. If transaction B also requests for a shared lock on R. It will
A
result in deadlock situation
B
immediately be granted
C
immediately be rejected
D
be granted as soon as it is released by A
       Database-Management-System       Transactions       Nielit Scientific Assistance CS 15-10-2017
Question 37 Explanation: 
● Shared locks exist when two transactions are granted read access.
● One transaction gets the shared lock on data and when the second transaction requests the same data it is also given a shared lock.
● Both transactions are in a read-only mode, updating the data is not allowed until the shared lock is released.
● There is no conflict with the shared lock because nothing is being updated.
● Shared locks last as long as they need to last; it depends on the level of the transaction that holds the lock.
Question 38
Which of the following is not a transaction management SQL command?
A
Rollback
B
Commit
C
Select
D
Savepoint
       Database-Management-System       Transactions       KVS DEC-2017
Question 38 Explanation: 
Transaction Control Language(TCL) commands are used to manage transactions in the database. These are used to manage the changes made to the data in a table by DML statements. It also allows statements to be grouped together into logical transactions.
1. Rollback
2. Savepoint
3. Commit
Question 39

In transaction management od database, ‘After a transaction completes successfully the changes it has made to the database persists even if there are system failures’. This property is referred to as:

A
Atomicity
B
Consistency
C
Isolation
D
Durability
       Database-Management-System       Transactions       JT(IT) 2016 PART-B Computer Science
Question 39 Explanation: 
• The database should be durable enough to hold all its latest updates even if the system fails or restarts.
• If a transaction updates a chunk of data in a database and commits, then the database will hold the modified data.
• If a transaction commits but the system fails before the data could be written on to the disk, then that data will be updated once the system springs back into action.
Question 40

In concurrency control a situation where ‘ A transaction may be waiting for an x-lock on an item, while a sequence of other transactions request and are granted on s-lock on the same item’. May lead to:

A
Deadlock
B
Starvation
C
Conflict
D
Lock failure
       Database-Management-System       Transactions       JT(IT) 2016 PART-B Computer Science
Question 40 Explanation: 
In concurrency control a situation where ‘ A transaction may be waiting for an x-lock on an item, while a sequence of other transactions request and are granted on s-lock on the same item’ may lead to starvation.
Reasons of Starvation:
1. If waiting scheme for locked items is unfair. ( priority queue )
2. Victim selection. ( same transaction is selected as a victim repeatedly )
3. Resource leak. (Via denial-of-service attack)
Question 41
Which of the following concurrency protocol ensures both conflict serializability and freedom from deadlock?
(a) Z-Phase Locking
(b) Timestamp ordering
A
Both (a) and (b)
B
(a) only
C
(b) only
D
Neither (a) nor (b)
       Database-Management-System       Transactions       UGC NET CS 2015 Jun- paper-2
Question 41 Explanation: 
Timestamp ordering provides a schedule which is conflict serializable and free from deadlock but Z-phase locking provides a schedule which is conflict serializable only.
Question 42
Two phase protocol in a database management system is :
A
a concurrency mechanism that is not deadlock free
B
a recovery protocol used for restoring a database after a crash
C
Any update to the system log done in 2-phases
D
not effective in Database
       Database-Management-System       Transactions       UGC NET CS 2006 Dec-paper-2
Question 42 Explanation: 
The two phase protocol in DBMS refers to a protocol in which locking and unlocking of shared data includes two phases:
1. Growing Phase: In this locks are acquired according to the need as the transaction proceeds.
2. Shrinking Phase: In this release locks as transaction starts reaching to completion.
A transaction can obtain locks but can't release locks in growing phase. Similarly a transaction can release locks but can't obtain lock in shrinking phase.
There are various types of two phase locking mechanism which can provide a conflict serializable schedule but can't provide a schedule which is deadlock free and can be implemented practically.
Question 43
Immediate updates as a recovery protocol is preferable, when :
A
Database reads more than writes
B
Writes are more than reads
C
It does not matter as it is good in both the situations
D
There are only writes
       Database-Management-System       Transactions       UGC NET CS 2006 June-Paper-2
Question 43 Explanation: 
→ Immediate updates as a recovery protocol is preferable when writes are more than reads.
→ In this technique, when a transaction issues an update command, the database on disk can be updated immediately, without any need to wait for the transaction to reach its commit point.
Question 44
Which of the following statement is wrong ?
A
2 - phase locking protocol suffers from dead locks
B
Time - Stamp protocol suffers from more abort
C
Time stamp protocol suffers from cascading rollbacks where as 2 - phase locking protocol do not
D
None of these
       Database-Management-System       Transactions       UGC NET CS 2006 June-Paper-2
Question 44 Explanation: 
Option (A) is correct because 2-Phase locking protocol provides a schedule which is conflict serializable but do not deadlock free schedule.
Option (B) is true because of timestamp ordering of the transactions, this protocol suffer more number of aborts.
Option (C) is true. Whenever some transaction T tries to issue a read_item(X) or a write_item(X) operation, the basic Time out algorithm compares the Timestamp of T with read_TS(X) and write_TS(X) to ensure that the timestamp order of transaction execution is not violated. If this order is violated, then transaction T is aborted and resubmitted to the system as a new transaction with a new timestamp.
If T is aborted and rolled back, any transaction T1 that may have used a value written by T must also be rolled back. Similarly, any transaction T2 that may have used a value written by T1 must also be rolled back, and so on. This effect is known as cascading rollback.
But in case of 2-Phase locking, to avoid cascading aborts Strict two-phase locking is introduced in which a transaction holds an exclusive locks until the transaction commits/aborts. Rigorous two-phase locking is even more stricter in which both Exclusive lock and shared lock are hold by the transaction until the transaction commits/abort.
Question 45
Consider the following sequence of two transactions on a bank account(A) with initial balance 20,000 that transfers 5,000 to another account (B) and then apply 10% interest.
(i) T1 start
(ii) T1 A old=20000 new 15,000
(iii) T1 B old=12000 new=17000
(iv) T1 commit
(v) T2 start
(vi) T2 A old=15000 new=16500
(vii) T2 commit
Suppose the database system crashes out just before log record (vii) is written. When the system is restricted, which one statement is true of the recovery process ?
A
We can apply redo and undo operation in arbitrary order because they are idempotent.
B
We must redo log record (vi) to set A to 16,500.
C
We must undo log record (vi) to set A to 16,500 and then redo log record (ii) and (iii).
D
We need not redo records (ii) and (iii) because transaction T1 has committed.
       Database-Management-System       Transactions       UGC NET CS 2018-DEC Paper-2
Question 45 Explanation: 
In log based recovery we must perform 'Redo' operation for those transactions that contains both start and commit log record.
We perform 'undo' operation for those transaction that contains only start but not commit log record.
Therefore we perform 'Redo' of T1 and 'Undo' of T2.
Note:​ Actually they given option-3 is “​ ​ We must redo log record (vi) to set A to 16,500 and then redo log record (ii) and (iii)”. But we found it is wrong. Given correct option instead of wrong one.
Question 46
Usage of Preemption and Transaction Rollback prevents ______.
A
Unauthorised usage of data file
B
Deadlock situation
C
Data manipulation
D
File preemption
       Database-Management-System       Transactions       UGC NET CS 2013 Sep-paper-2
Question 46 Explanation: 
Usage of Preemption and Transaction Rollback prevents deadlock.
Question 47
Thomas-write rule is ______.
A
Two phase locking protocol
B
Timestamp ordering protocol
C
One phase locking protocol
D
Sliding window proto
       Database-Management-System       Transactions       UGC NET CS 2013 Sep-paper-2
Question 47 Explanation: 
→ The Thomas write rule is a rule in timestamp-based concurrency control. It can be summarized as ignore outdated writes.
→ The Thomas write rule is applied in situations where a predefined logical order is assigned to transactions when they start.
Question 48
Consider the following schedules involving two transactions.
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)
Which one of the following statements is correct with respect to above ?
A
Both S1 and S2 are conflict serializable.
B
Both S1 and S2 are not conflict serializable.
C
S1 is conflict serializable and S2 is not conflict serializable.
D
S1 is not conflict serializable and S2 is conflict serializable.
       Database-Management-System       Transactions       UGC NET CS 2018 JUNE Paper-2
Question 48 Explanation: 
Question 49
A Transaction Manager is which of the following ?
A
Maintains a log of transactions
B
Maintains before and after database images
C
Maintains appropriate concurrency control
D
All of the above
       Database-Management-System       Transactions       UGC NET CS 2012 June-Paper2
Question 49 Explanation: 
A transaction manager is one who is responsible for the ACID properties of a transaction.
→ He maintains the log of all transactions and ensures that only each transaction should be either performed completely or not performed at all.
→ Transaction manager ensure consistency preservation by maintaining the before and after database images of a transaction.
→ Multiple transactions can be executed simultaneously but it is the responsibility of transaction manager that the execution of a transaction should not be interfered with by any other transactions executing concurrently. So for that it maintains a appropriate concurrency control.
→ Transaction manager is also responsible for the durability of a transaction that is the changes applied to the database by a committed transaction must persist in the database. These changes must not be lost because of any failure.
Question 50
The basic variants of time-stamp based method of concurrency control are
A
Total timestamp-ordering
B
Partial timestamp ordering
C
Multiversion Timestamp ordering
D
All of the above
       Database-Management-System       Transactions       UGC NET CS 2011 June-Paper-2
Question 50 Explanation: 
The basic variants of time-stamp based method of concurrency control are
1. Total timestamp-ordering
2. Partial timestamp ordering
3. Multiversion Timestamp ordering
Question 51
A transaction can include following basic database access operations :
A
Read_item(X)
B
Write_item(X)
C
Both (A) and (B)
D
None of these
       Database-Management-System       Transactions       UGC NET CS 2011 June-Paper-2
Question 51 Explanation: 
Basic operations are read and write
1. read_item(X): Reads a database item named X into a program_variable. To simplify our notation, we assume that the program variable is also named X, i.e X=r(x)
steps:
1. Find the address of the disk block that contains item X.
2. Copy that disk block into a buffer in main memory (if that disk block is not already in some main memory buffer).
3. Copy item X from the buffer to the program variable named X.
2. write_item(X): Writes the value of program variable X into the database item named X.
steps:
1. Find the address of the disk block that contains item X.
2. Copy that disk block into a buffer in main memory (if that disk block is not already in some main memory buffer).
3. Copy item X from the program variable named X into its correct location in the buffer.
4. Store the updated block from the buffer back to disk (either immediately or at some later point in time).
Question 52
Assume transaction A holds a shared lock R. If transaction B also requests for a shared lock on R, it will
A
result in a deadlock situation
B
immediately be granted
C
immediately be rejected
D
be granted as soon as it is released by A
       Database-Management-System       Transactions       NIELIT Technical Assistant_2016_march
Question 53
Which of the following is an optimistic concurrency control method ?
A
Validation based
B
Timestamp ordering
C
Lock-based
D
None of these
       Database-Management-System       Transactions       UGC NET CS 2010 Dec-Paper-2
Question 53 Explanation: 

Question 54
Which of the following statement is wrong ?
I. 2-phase locking protocol suffer from deadlock.
II. Time stamp protocol suffer from more aborts.
III. A block hole in a DFD is a data store with only inbound flows.
IV. Multivalued dependency among attribute is checked at 3 NF level.
V. An entity-relationship diagram is a tool to represent event model.
A
I, II, II
B
II, III, IV
C
III, IV, V
D
II, IV, V
       Database-Management-System       Transactions       UGC NET CS 2009 Dec-Paper-2
Question 54 Explanation: 
ER- model: This model and its variations are frequently used for the conceptual design of database applications, and many database design tools employ its concepts. Hence statement 5 is wrong.
3NF: This normal form is responsible for checking transitive dependency. It is 4NF which checks multivalued dependency. Hence statement 4 is wrong.
2-Phase locking: It provides a serializable schedule but does not provides a deadlock free serializable schedule. Deadlock might occur using this concurrency control mechanism.
Time Stamp Protocol: It suffers more aborts in comparison of other concurrency control protocols because of allowing the execution of the transactions based upon their time stamp ordering. A transaction with higher timestamp is allowed to execute otherwise if a transaction with lower timestamp requests for execution then in that case that transaction will be aborted.
Hence only option A is correct.
Question 55
Which of the following statements is wrong ?
A
2-phase Locking Protocols suffer from dead locks.
B
Time - Stamp Protocols suffer from more aborts.
C
Timestamp Protocols suffer from cascading rollback where as 2-phase locking Protocol donot.
D
None of these
       Database-Management-System       Transactions       UGC NET CS 2007 June-Paper-2
Question 55 Explanation: 
2-Phase locking: It provides a serializable schedule but does not provides a deadlock free serializable schedule. Deadlock might occur using this concurrency control mechanism.
Time Stamp Protocol: It suffers more aborts in comparison of other concurrency control protocols because of allowing the execution of the transactions based upon their time stamp ordering. A transaction with higher timestamp is allowed to execute otherwise if a transaction with lower timestamp requests for execution then in that case that transaction will be aborted.
Question 56
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 ?
S1: r1(X); r2(X); w1(X); r3(X); w2(X)
S2: r2(X); r1(X); w2(X); r3(X); w1(X)
S3: r3(X); r2(X); r1(X); w2(X); w1(X)
S4: r2(X); w2(X); r3(X); r1(X); w1(X)
A
S1
B
S2
C
S3
D
S4
       Database-Management-System       Transactions       UGC NET CS 2017 Nov- paper-3
Question 56 Explanation: 



Question 57
Suppose a database schedule S involves transactions T1, T2, .............,Tn. Consider 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
       Database-Management-System       Transactions       UGC NET CS 2017 Nov- paper-3
Question 57 Explanation: 
If a schedule is conflict serializable then no cycle in precedence graph should be present.
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 58

Consider following schedules involving two transactions:

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)

Which of the following statement is true?
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 serializable.
       Database-Management-System       Transactions       UGC NET CS 2017 Jan- paper-3
Question 58 Explanation: 

Question 59

Two concurrent executing transactions T1 and T2 are allowed to update same stock item say ‘A’ in an uncontrolled manner. In such a scenario, following problems may occur:

(a) Dirty read problem

(b) Lost update problem

(c) Transaction failure

(d) Inconsistent database state

Which of the following option is correct if database system has no concurrency module and allow concurrent execution of above two transactions?
A
(a), (b) and (c) only
B
(c) and (d) only
C
(a) and (b) only
D
(a), (b) and (d) only
       Database-Management-System       Transactions       UGC-NET DEC-2019 Part-2
Question 59 Explanation: 
Following problems can occur when concurrent transactions execute in an uncontrolled manner:
The Lost Update Problem. This problem occurs when two transactions that access the same database items have their operations interleaved in a way that makes the value of some database items incorrect.
The Temporary Update (or Dirty Read) Problem. This problem occurs when one transaction updates a database item and then the transaction fails for some reason
The Incorrect Summary Problem. If one transaction is calculating an aggregate summary function on a number of database items while other transactions are updating some of these items, the aggregate function may calculate some values before they are updated and others after they are updated.
The Unrepeatable Read Problem. Another problem that may occur is called unrepeatable read, where a transaction T reads the same item twice and the item is changed by another transaction T between the two reads. Hence, T receives different values for its two reads of the same item.
Question 60
To schedules are said to be______. If the order of any two conflicting operations is same in both the schedules.
A
conflict equivalent
B
schema equivalent
C
result equivalent
D
view equivalent
       Database-Management-System       Transactions       CIL Part - B
Question 60 Explanation: 
View-serializability of a schedule is defined by equivalence to a serial schedule (no overlapping transactions) with the same transactions, such that respective transactions in the two schedules read and write the same data values ("view" the same data values).
Conflict-serializability is defined by equivalence to a serial schedule (no overlapping transactions) with the same transactions, such that both schedules have the same sets of respective chronologically ordered pairs of conflicting operations (same precedence relations of respective conflicting operations).
Question 61
Consider a schedule generated by the execution of several SQL transactions, each of which has READ ONLY access-mode. Which of the following is guaranteed?
A
Conflict-serializable
B
Serializable
C
Recoverable
D
All the given options
       Database-Management-System       Transactions       APPSC-2016-DL-CS
Question 61 Explanation: 
Since there is no WRITE operation so no conflicts will be there, hence all the given options are correct.
Question 62
The following example is a ----------------- T1:R(X), T2:R(X), T2:W(X), T1:W(X), T2:Commit, T1:Commit
A
Serializable schedule and it is strict
B
Not serializable schedule and it’s not strict
C
Serializable schedule, but it’s not strict
D
Not serializable schedule, but it is strict
       Database-Management-System       Transactions       APPSC-2016-DL-CS
Question 62 Explanation: 
Question 63

In database management system ACID property refers to:

A
Authenticity, consistency, Isolation and Durability
B
Atomicity, Confidentiality, Isolation and Durability
C
Atomicity, consistency, Isolation and Durability
D
Atomicity, Confidentiality, Integrity and Durability
       Database-Management-System       Transactions       CIL 2020
Question 63 Explanation: 
A transaction is a very small unit of a program and it may contain several low level tasks. A transaction in a database system must maintain Atomicity, Consistency, Isolation, and Durability − commonly known as ACID properties − in order to ensure accuracy, completeness, and data integrity.
Atomicity − This property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none. There must be no state in a database where a transaction is left partially completed. States should be defined either before the execution of the transaction or after the execution/abortion/failure of the transaction.
Consistency − The database must remain in a consistent state after any transaction. No transaction should have any adverse effect on the data residing in the database. If the database was in a consistent state before the execution of a transaction, it must remain consistent after the execution of the transaction as well.
Durability − The database should be durable enough to hold all its latest updates even if the system fails or restarts. If a transaction updates a chunk of data in a database and commits, then the database will hold the modified data. If a transaction commits but the system fails before the data could be written on to the disk, then that data will be updated once the system springs back into action.
Isolation − In a database system where more than one transaction are being executed simultaneously and in parallel, the property of isolation states that all the transactions will be carried out and executed as if it is the only transaction in the system. No transaction will affect the existence of any other transaction.
Question 64

While restarting recovery, which sequence is followed?

A
Undo transactions of undo-list; redo transactions of redo-list.
B
Reo transactions of redo-list; undo transactions of undo-list.
C
Undo transactions of undo-list.
D
Redo transactions of redo-list.
       Database-Management-System       Transactions       APPSC-2012-DL CA
Question 64 Explanation: 
First we have to do UNDO list ==> system back to some consistence state, then we have to do REDO list.
Question 65

Which of the following properties is implemented by concurrency control algorithms.

A
Durability
B
Consistency
C
Atomicity
D
Isolation
       Database-Management-System       Transactions       APPSC-2012-DL CA
Question 65 Explanation: 
Concurrency control method is used to apply isolation through mutual exclusion between conflicting transactions.
Question 66
Examples of transaction systems are
A
Banking
B
Railway Ticket Booking
C
Stock Trading
D
All the above
       Database-Management-System       Transactions       APPSC-2012-DL-CS
Question 66 Explanation: 
All of the above are examples of transaction systems.
Question 67
ALL or NONE refers:
A
Consistency
B
Isolation
C
Durability
D
Atomicity
       Database-Management-System       Transactions       TNPSC-2017-Polytechnic-CS
Question 67 Explanation: 
There are four properties of transactions that must be followed,
A-Atomicity
C-Consistency
I-Isolation
D-Durability
And the question is asking all or none refers to which of the property.
All or None refers to Atomicity.
Question 68
The physical location of a record is determined by a mathematical formula that transforms a file key into a record location is:
A
B – Tree File
B
Hashed File
C
Indexed File
D
Sequential File
       Database-Management-System       Transactions       TNPSC-2017-Polytechnic-CS
Question 68 Explanation: 
The physical location of a record is determined by a mathematical formula, which transforms a file key into a record location in hashed file. Hashing is the transformation of a string of characters into a usually shorter fixed-length value or key that represents the original string.
Question 69
Consider the following transactions with data items X and y initialized to zero :
T1: read x;
      read (y)
      if x=0 then y≔y+1;
      write y;
T2: read y;
      write x;
      if y=0 then x≔x+1
     write x; 
The concurrent execution of T1 and T2 leads to
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
       Database-Management-System       Transactions       TNPSC-2017-Polytechnic-CS
Question 69 Explanation: 
One of the schedule that is possible is,

Let’s check for conflict serializability,
There are 69 questions to complete.
PHP Code Snippets Powered By : XYZScripts.com
error: Content is protected !!