Introduction
Before going ahead by solving the previous year's gate question let us understand transactions and concurrency control in DBMS in brief. Transactions and concurrency control are two different discrete functions but they are mutually dependent. How to let's see the definition of both transactions and concurrency control in DBMS
Transaction:
Transaction in DBMS is an action or sequence of actions passed out by a single user to read or update the contents of the Database. A transaction is also defined as a logical piece of work of any database; it may be a complete program, a fraction or even a single command (like the SQL command), which involves several processes on the database.
Concurrency Control:
Concurrency Control in DBMS is defined as managing the concurrent operations in the database without getting any obstruction from one another.
Problems
Now, we will see gate questions on Transactions and concurrency control in DBMS with their solutions and explanations.
-
Data items P and Q in below transaction are 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 simultaneous execution will lead to
A) A serializable schedule.
B) A schedule that does not conflict with serializable.
C) A serializable conflict schedule.
D) A schedule for which we cannot draw a precedence graph.
ANSWER: B
Explanation:
Two or more actions are in conflict if:
1) Actions belong to different transactions.
2) At least one of the actions has a written operation.
3) Actions access the same object, i.e., read or write.
S1 and S2 schedules are said to be conflict-equivalent only if the following conditions are satisfied:
1) Both schedules, S1 and S2, are involved in the same set of transactions (including ordering of actions within each transaction).
2) Each pair of conflicting actions in S1 and S2 are the same.
A conflict-serializable schedule is when the schedule is conflict-equivalent to one or more serial schedules.
-
Consider the following transaction defined below involving two bank accounts, A and B.
read(A); A := A – 50; write(A); read(B); B := B + 50; write(B)
Which constraint, where the sum of the accounts A and B should remain constant, is
A) Atomicity
B) Consistency
C) Isolation
D) Durability
ANSWER: B
Explanation: Consistency in database systems is if any given transaction of the database must only change the affected data in the allowed ways. Hence the Sum of A and B will not change.
-
Out of the following options, which among these is not a part of ACID Properties of the database transactions?
A) Atomicity
B) Consistency
C) Isolation
D) Deadlock-freedom
ANSWER:- D
Explanation : ACID stands for Atomicity(Entire transaction should happen at once or not at all), Consistency(entire database needs to be consistent), Isolation (Multiple transactions occur independently without interference) and Durability(System failure doesn't affect the chances of a successful transaction). Hence Deadlock-freedom is not a part of the ACID properties of the database transaction.
-
The 'Durability' property among the ACID properties of a transaction requires that the changes made to the database by a successful transaction persist.
A) Except in case OS crashes
B) Except in case Disk crashes
C) Except in case of the power failure
D) Always, even in the presence of a failure of any kind.
ANSWER:- D
Explanation : Durability in ACID properties of a transaction is that System failure doesn't affect the chances of a successful transaction. Any changes made by a committed transaction must be permanent irrespective of the failure.
-
Which concurrency control protocols ensure conflict serializability and freedom from the deadlock?
A) 2-phase locking
B) Time-stamp Ordering
C) Both 2-phase locking and Time-stamp ordering
D) None of the above
ANSWER:- B
Explanation :2PL, i.e., 2 Phase Locking, is a concurrency control method that guarantees serializability. This protocol uses locks applied to data by a transaction, which blocks other transactions from accessing the same data during the transaction's life. Mutual blocking of 2 or more transactions may lead to deadlock in 2PL.
-
Below are the transactions T1, T2, T3 and the schedules S1 and S2 and mark the correct statement about the schedule.
A) Both S1 and S2 are conflict-serializable.
B) None of the S1 and S2 is conflict-serializable.
C) S1 is conflict-serializable.
D) S2 is conflict-serializable.
ANSWER:- C
Explanation :For conflict serializability of a schedule, we check for conflict operations, which are Write-Read, Read-Write, and Write-Write, between each pair of transactions, and we make a precedence graph based on those conflicts. The schedule is not a conflict serializable if the precedence graph contains a cycle after drawing the Precedence graph of S1 and S2. In S1, there is no cycle, but S2 has a cycle. Hence only S1 is conflict serializable.
-
Which among the following discussed scenarios may lead us to an irrecoverable error in a database system?
A) A transaction writes a data item after an uncommitted transaction reads it.
B) A transaction reads a data item after an uncommitted transaction reads it.
C) A transaction reads a data item after a committed transaction writes it.
D) A transaction reads a data item after an uncommitted transaction writes it.
ANSWER:- D
Explanation :Among the following Options, Option C is normal, Option B is also normal as no write operation got involved, In Option A could be recovered, but Option D can't be.
-
Suppose a database system crashes once again while recovering from the previous crash. If we assume that checkpointing is not done by the database during the transactions or the recovery—Mark the correct statements.
A) Undo and Redo list will be used to recover
B) Database becomes inconsistent
C) Any transactions that are already done or undone will not get recovered again.
D) The system will not recover further.
ANSWER:- A
Explanation :Even after multiple crashes of the database system, the same undo and redo lift will be used
-
The highest degree of concurrency is provided by which level of locking in a relational database?
A) Page
B) Table
C) Row
D) All Page, table and row-level locking allow the same degree of concurrency
ANSWER:- C
Explanation :Page-level locking is highly restrictive since it locks the whole Page, i.e. all rows. Table locking is mainly used for concurrency control with DDL operations. A row share table locking 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, the Row level provides the highest level of concurrency. Option (C) is correct.
-
Which among the following statements is/are incorrect?
1) A schedule following a strict two-phase locking protocol is conflict serializable and recoverable.
2) Checkpoint in schedules inserted to ensure recoverability.
A) Only 1
B) Only 2
C) Both 1 and 2
D) None of the above
ANSWER:- B
Explanation :A basic two-phase locking protocol ensures only conflict serializability. In contrast, a strict two-phase locking protocol ensures conflict serializability and recoverability. So statement (1) is correct. i.e., checkpoints are inserted to minimize the task of undo-redo in recoverability. And hence statement (2) is not correct. So Option (B) is correct.