Transaction MCQ Quiz - Objective Question with Answer for Transaction - Download Free PDF
Last updated on Apr 14, 2025
Latest Transaction MCQ Objective Questions
Transaction Question 1:
Which of the following statement(s) is/are true?
A. Selection operation yield horizontal subset of a relation
B. Selection operation yields vertical subset of a relation.
C. Union operator can work on relation with different degree.
D. Intersection and cartesian product operations are not binary.
E. A record is a column on a datasheet.
Choose the correct answer from the options given below:
Answer (Detailed Solution Below)
Transaction Question 1 Detailed Solution
The correct answer is 1) A only.
Key Points
- The selection operation yields a horizontal subset of a relation.
- This means it selects specific rows from a table based on a given condition.
- For example, if you want to select all rows where the age is greater than 25, the selection operation will return all such rows.
Additional Information
- The selection operation does not yield a vertical subset (which would be a projection operation).
- The union operator requires the relations to have the same degree (number of columns).
- Both intersection and cartesian product operations are binary operations, meaning they operate on two relations.
- A record is a row in a datasheet, not a column.
Transaction Question 2:
We can say that a schedule is conflict serializable?
Answer (Detailed Solution Below)
Transaction Question 2 Detailed Solution
Key Points
- Conflict serializability is a concept used in the context of database systems to ensure that a schedule (sequence of operations) is equivalent to some serial schedule (where transactions are executed one after another without overlapping).
- Two operations conflict if they belong to different transactions, access the same data item, and at least one of the operations is a write.
- If a schedule can be transformed into a serial schedule by swapping non-conflicting operations, it ensures that the final state of the database will be the same as if the transactions were executed serially.
- Conflict serializability is a stricter form of serializability that avoids the complexities of non-conflicting operations leading to the same final state.
Important Points
- Conflict serializability is easier to check and implement compared to other forms of serializability.
- It ensures a more strict ordering of transactions which helps in maintaining data integrity and consistency.
Additional Information
- Conflict serializability is often used in concurrency control mechanisms to ensure that the concurrent execution of transactions does not lead to inconsistency.
- Understanding conflict serializability is crucial for database administrators and developers working on transaction management and isolation levels.
Transaction Question 3:
Consider the transactions T1, T2, T3 and the schedules S1 and S2 given below.
T1 : r1(x); r1(z); 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); w3(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 ?
Answer (Detailed Solution Below)
Transaction Question 3 Detailed Solution
The correct answer is Only S1 is conflict-serializable
Concept:
- Using precedence graph method, we can find out Schedule is conflict serializable or not.
- If precedence graph has any loop then that schedule is going to be not conflict-serializable.
- If precedence graph doesn’t have any loop then that schedule is going to be conflict-serializable.
Explanation:
Schedule S1:
T1 |
T2 |
T3 |
r(X) | ||
|
|
r(Y) |
|
|
r(X) |
|
r(Y) |
|
|
r(Z) |
|
|
|
w(Y) |
|
|
w(Z) |
r(Z) |
|
|
w(X) |
|
|
w(Z) |
|
|
Precedence graph of S1:
Since there is no loop in S1, it is is conflict serializable
Order: T2 → T1 → T2
Schedule S2:
r1(X); r3(Y); r2(Y); r3(X); r1(Z); r2(Z); w3(Y); w1(X); w2(Z); w1(Z)
T1 |
T2 |
T3 |
r(X) | ||
|
|
r(Y) |
|
r(Y) |
|
|
|
r(X) |
r(Z) |
|
|
|
r(Z) |
|
|
|
w(Y) |
w(X) |
|
|
|
w(Z) |
|
w(Z) |
|
|
Precedence graph of S2:
- So there is loop in S2 so S2 is not conflict serializable.
- Schedule S1 is equivalent to only this serial schedule T2 : T3 : T1.
Hence option 1 is the correct answer.
Transaction Question 4:
The techniques used to handle the phantom problem are ________.
A. Time stamping
B. Index locking
C. Predicate locking
D. Execution indexing
Choose the correct answer from the options given below:
Answer (Detailed Solution Below)
Transaction Question 4 Detailed Solution
The correct answer is 2) B and C Only.
Key Points
- The phantom problem occurs in database systems when a transaction reads a set of rows that satisfy a condition, but another transaction inserts or deletes rows that would have satisfied the condition. This can lead to inconsistent results.
- Index Locking is a technique where locks are placed on the index entries that match the search condition. This prevents other transactions from inserting or deleting rows that would affect the result set.
- Predicate Locking involves locking the entire set of rows that satisfy a condition. This ensures that no new rows can be inserted or deleted that would affect the result set.
Additional Information
- Time Stamping is a concurrency control mechanism that assigns a unique timestamp to each transaction. It is not specifically designed to handle the phantom problem.
- Execution Indexing is not a standard term in database concurrency control and is not related to solving the phantom problem.
Transaction Question 5:
If transaction T1 reads a change made by transaction T2, then T1 commits only after T2 commits. This property of a transaction schedule is known as:
Answer (Detailed Solution Below)
Transaction Question 5 Detailed Solution
The correct answer is option 3.
Concept:
Schedules in which transactions commit only after all transactions whose changes they read commit are called recoverable schedules. In other words, if some transaction T2 is reading value updated or written by some other transaction T1, then the commit of T1 must occur after the commit of T2.
Example:
R1(x), W1(x), R2(x), R1(y), R2(y), W2(x), W1(y), C1, C2;
T1 | T2 |
R(X) W(X)
R(Y)
W(Y) Commit |
R(X)
W(X)
Commit |
Hence the correct answer is recoverable.
Additional Information Strict Schedule:
A schedule is strict if for any two transactions T1, T2, if a write operation of T1 precedes a conflicting operation of T2 (either read or write), then the commit or abort event of T1 also precedes that conflicting operation of T2.
In other words, T2 can read or write updated or written values of T1 only after T1 commits/aborts.
Example:
T1 | T2 |
R(X)
W(X) Commit
|
R(X)
W(X) R(X) Commit |
Cascadeless Schedule:
Schedules in which transactions read values only after all transactions whose changes they are going to read commit are called cascadeless schedules. Avoids that a single transaction abort leads to a series of transaction rollbacks.
Example:
T1 | T2 |
R(X) W(X)
Commit
|
W(X)
R(X) Commit |
Serializable schedule:
The Serializable schedule is a type of schedule where one transaction is executed completely before starting another transaction.Example: R1(X), W1(X) Commit1, W2(X) ,R2(X), Commit2.
T1 | T2 |
R(X) W(X) Commit |
W(X) R(X) Commit |
Database Schedules Relationship:
Top Transaction MCQ Objective Questions
Two concurrent executing transactions T1 and T2 are allowed to update same stock item say ‘A’ in an uncontrolled manner. In such 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 allows concurrent execution of above two transactions?
Answer (Detailed Solution Below)
Transaction Question 6 Detailed Solution
Download Solution PDFProblems that can occur during concurrent execution of two transactions in an uncontrolled manner:
a) Lost update problem: Lost update 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 item incorrect.
b) Dirty read problem: It occurs when a transaction is allowed to retrieve or update a record that has been updated by another transaction. Because it has not yet been committed, there is always a possibility that it will never be committed but rather rollback.
d) Inconsistent database state: It occurs when a transaction reads several values, but a second transaction updates some of these values during the execution of first. This causes an inconsistency. For example: if one transaction is calculating an aggregate summary function on a number of records while other transactions are updating some of records.
Which one of the following makes permanently recorded transaction in the database?
Answer (Detailed Solution Below)
Transaction Question 7 Detailed Solution
Download Solution PDFTransaction 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.
Commit Command:
- Commit command is used to permanently save any transaction into the database.
- When we use any DML command like INSERT, UPDATE or DELETE, the changes made by these commands are not permanent, until the current session is closed, the changes made by these commands can be rolled back.
- To avoid that, we use the COMMIT command to mark the changes as permanent.
the syntax for the commit command is as shown:
COMMIT;
Rollback Command:
- This command restores the database to last committed state.
- It is also used with the SAVEPOINT command to jump to a savepoint in an ongoing transaction.
Flashback:
It provides ways to view past states of database objects, or to return database objects to a previous state.
Flashback features of the database can be used to:
- Perform queries that return past data.
- Perform queries that return metadata showing a detailed history of changes to the database.
- Recover tables or individual rows to a previous point in time.
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 ofAnswer (Detailed Solution Below)
Transaction Question 8 Detailed Solution
Download Solution PDFACID Properties:
ATOMICITY: It states that either all the operations of a transaction are executed or NULL.
ISOLATION: It states that each transaction of the concurrent schedule is unaware from another transaction that is parallel executed even if that transaction is executed on same data item.
DURABILITY: It states that after the successful execution of the transaction on the database all the modifications must be reflected on the database even after the system failure.
CONSISTENCY: It states that after the execution of a transaction the database must be in the consistent state (as that before execution).
Explanation:
Let us assume that RS.50 is debited from one account and credited to another account, in such case T1 transaction should execute first then T2
T1 |
T2 |
read(x) |
|
x := x – 50 |
|
write(x) |
|
|
read(y) |
|
y ≔ y+50 |
|
write(y) |
Before transaction: Initially
x = 100 and y = 150 then x + y = 250
After transaction: Finally
x = 50 and y = 200 then x + y = 250
The constraint that the sum of the accounts x and y should remain constant is that of consistency.If transaction T1 reads a change made by transaction T2, then T1 commits only after T2 commits. This property of a transaction schedule is known as:
Answer (Detailed Solution Below)
Transaction Question 9 Detailed Solution
Download Solution PDFThe correct answer is option 3.
Concept:
Schedules in which transactions commit only after all transactions whose changes they read commit are called recoverable schedules. In other words, if some transaction T2 is reading value updated or written by some other transaction T1, then the commit of T1 must occur after the commit of T2.
Example:
R1(x), W1(x), R2(x), R1(y), R2(y), W2(x), W1(y), C1, C2;
T1 | T2 |
R(X) W(X)
R(Y)
W(Y) Commit |
R(X)
W(X)
Commit |
Hence the correct answer is recoverable.
Additional Information Strict Schedule:
A schedule is strict if for any two transactions T1, T2, if a write operation of T1 precedes a conflicting operation of T2 (either read or write), then the commit or abort event of T1 also precedes that conflicting operation of T2.
In other words, T2 can read or write updated or written values of T1 only after T1 commits/aborts.
Example:
T1 | T2 |
R(X)
W(X) Commit
|
R(X)
W(X) R(X) Commit |
Cascadeless Schedule:
Schedules in which transactions read values only after all transactions whose changes they are going to read commit are called cascadeless schedules. Avoids that a single transaction abort leads to a series of transaction rollbacks.
Example:
T1 | T2 |
R(X) W(X)
Commit
|
W(X)
R(X) Commit |
Serializable schedule:
The Serializable schedule is a type of schedule where one transaction is executed completely before starting another transaction.Example: R1(X), W1(X) Commit1, W2(X) ,R2(X), Commit2.
T1 | T2 |
R(X) W(X) Commit |
W(X) R(X) Commit |
Database Schedules Relationship:
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?
Answer (Detailed Solution Below)
Transaction Question 10 Detailed Solution
Download Solution PDFSerial schedule is possible only when precedence graph doesn’t contain cycle. If precedence graph contains a cycle, it means schedule is not conflict serializable.
Breadth first search and Depth first search of a graph are possible even if graph contains cycle.
Topological sort in a graph will not work if graph contains a cycle.
Consider a directed acyclic graph:
Here Two orders possible: V2, V3, V1, V4, V5, V6 OR V3, V2, V1, V4, V5, V6.
In case of ascending order of transaction indices, two non-conflicting schedules can occur simultaneously.
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?Answer (Detailed Solution Below)
Transaction Question 11 Detailed Solution
Download Solution PDFConcept:
Cascadeless schedule
It is one in which each pair of transaction Ti and Tj such that Tj reads the data that is written by Ti than the commit or abort operation of Ti should appear before the read operation of Tj
T1 |
T2 |
|
r(x) |
r(x) |
|
|
r(y) |
w(x) |
|
r(y) |
|
|
w(x) |
abort(a) |
|
|
abort(a) |
Since there are no write-in T1 read by T2. Hence S is cascadeless schedule, that is, S does not have a cascading abort
In the context of concurrency control, a given pair of operations in a schedule is called conflict schedule if
(A) At least one of the operations is write operation
(B) Both the operations are performed on the same data item
(C) Both the operations are performed by different transactions
(D) Both the operations are performed on different data items
Choose the correct answer from the options given below:
Answer (Detailed Solution Below)
Transaction Question 12 Detailed Solution
Download Solution PDFThe correct answer is option 2.
Key Points
Two functions are said to be incompatible if any of the following conditions are met:
- They are associated with various transactions.
- They both work with the same data object.
- One of them must be a write operation.
If you note the issues mentioned below, it's because different transactions have performed at least one write operation on the same data object.
a) Item X has an incorrect value because its update by T1is lost(overwritten).
T1 | T2 |
read_item(X); write_item(X);
Y=Y+N; write_item(Y); |
read_item(X);
write_item(X);
|
b) Transaction T1 fails and must change the value of X back to its old value; meanwhile, T2 has read the temporary incorrect value of x.
T1 | T2 |
read_item(X); write_item(X); read_item(Y); |
read_item(X); write_item(X);
|
c)T3 reads X after N is subtracted and reads Y before N is added; a wrong summary is a result(off by N).
T1 | T3 |
read_item(X);
read_item(Y);
|
sum=0 . . read_item(X);
|
∴ Hence the correct answer is (A), (B) and (C) only
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?Answer (Detailed Solution Below)
Transaction Question 13 Detailed Solution
Download Solution PDFConcept:
Draw the precedence graph for the above two schedules:
1) If precedence graph contains cycle, then that schedule will not be conflict serializable
2) If graph does not contain any cycle, then schedule will be conflict serializable.
Explanation:
S1: r1(X); r1(Y); r2(X) ; r2(Y) ; w2(Y) ; w1(X)
There are two transaction involved.
T1 |
T2 |
r1(x) |
|
r1(y) |
|
|
r2(x) |
|
r2(y) |
|
w2(y) |
w1(x) |
|
Precedence graph:
S2: r1(X) ; r2(X) ; r2(Y) ; w2(Y) ; r1(Y) ; w1(X)
T1 |
T2 |
r1(x) |
|
|
r2(x) |
|
r2(y) |
|
w2(y) |
r1(y) |
|
w1(x) |
|
Precedence graph :
Consider a schedule of transactions T1 and T2:
T1 |
RA |
|
|
RC |
|
WD |
|
WB |
Commit |
|
T2 |
|
RB |
WB |
|
RD |
|
WC |
|
|
Commit |
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?
Answer (Detailed Solution Below)
T1 |
|
|
|
RA |
RC |
WD |
WB |
|
Commit |
|
T2 |
RB |
WB |
RD |
|
|
|
|
WC |
|
Commit |
Transaction Question 14 Detailed Solution
Download Solution PDFConcept:
Two schedules S1 and S2 are termed to be conflict equivalent if the conflict operations in both the schedules are executed in same order. The conflict operations are identified by RW, WR, and WW pairs.
Explanation:
The given Schedule in question is:
T1 |
T2 |
R(A)
R(C)
W(D)
W(B) Commit |
R(B) W(B)
R(D)
W(C)
Commit |
The conflict pairs are:
R2(B)-> W1(B)
W2(B)-> W1(B)
R1(C)-> W2(C)
R2(D)-> W1(D)
The schedule in which these pairs are executed in the same order would be conflict equivalent to this given schedule.
Only the schedule in option 1 has all these 4 pairs in the same order of execution and thus is conflict equivalent to a given schedule.
Suppose a database system crashes again while recovering from a previous crash. Assume checkpointing is not done by the database either during the transactions or during recovery.
Which of the following statements is/are correct?
Answer (Detailed Solution Below)
Transaction Question 15 Detailed Solution
Download Solution PDFAnswer: Option 1
Explanation:
Option 1:The same undo and redo list will be used while recovering again.
This Option is correct. Because in log files operations are idempotent, doing undo-redo operations multiple times yields the same results.
Option 2:The database will become inconsistent.
This option is not correct.
Option 3: All the transactions that are already undone and redone will not be recovered again.
This Option is not correct because transactions that are already undone and redone will be recovered with undo and redo list; performing undo and redo again will produce the same result in the Database.
Option 4: The system cannot recover any further.
This Option is not correct.