Transaction MCQ Quiz - Objective Question with Answer for Transaction - Download Free PDF

The correct option is (1)

Partially aborted

Concept:-

Abort, active, committed, partially committed, failed, and committed are the different transaction states.

At a particular instant of time, a transaction can be in one of the following states -

1. Active - Transaction is executing.
2. Failed - Transaction fails to complete successfully.
3. Abort - Changes made by a transaction are canceled (rollback).
4. Partially commit - Final statement of a transaction is executed.
5. Commit - Transaction completes its execution successfully.
6. Terminated - Transaction is finished.

Key Points

• A group of operations that come together to constitute a single logical unit of work are referred to as transactions.
• A group of operations that come together to constitute a single logical unit of work are referred to as transactions.
• A group of tasks might be referred to as a transaction. The smallest unit of processing that cannot be further subdivided is a single task.

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.

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.

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:

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) 

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.

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.

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

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.

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.

ACID 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 T₁ transaction should execute first then T₂

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 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 T₂ is reading value updated or written by some other transaction T₁, then the commit of T₁ must occur after the commit of T₂.

Example:

R1(x), W1(x), R2(x), R1(y), R2(y), W2(x), W1(y), C1, C2;

Hence the correct answer is recoverable.

Additional Information Strict Schedule:

A schedule is strict if for any two transactions T₁, T₂, if a write operation of T₁ precedes a conflicting operation of T₂ (either read or write), then the commit or abort event of T₁ also precedes that conflicting operation of T₂.

In other words, T₂ can read or write updated or written values of T₁ only after T₁ commits/aborts.

Example:

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:

Serializable schedule:

The Serializable schedule is a type of schedule where one transaction is executed completely before starting another transaction.Example: R₁(X), W₁(X) Commit₁, W₂(X) ,R₂(X), Commit₂.

Database Schedules Relationship:

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

Concept:

Cascadeless schedule

It is one in which each pair of transaction T_i and T_j such that T_j reads the data that is written by T_i than the commit or abort operation of T_i should appear before the read operation of T_j

Since there are no write-in T₁ read by T₂. Hence S is cascadeless schedule, that is, S does not have a cascading abort

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

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.

c)T3 reads X after N is subtracted and reads Y before N is added; a wrong summary is a result(off by N).

∴ Hence the correct answer is (A), (B) and (C) only

Concept:

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:

S₁: r₁(X); r₁(Y); r₂(X) ; r₂(Y) ; w₂(Y) ; w₁(X)

There are two transaction involved.

Precedence graph:

S2: r₁(X) ; r₂(X) ; r₂(Y) ; w₂(Y) ; r₁(Y) ; w₁(X)

Precedence graph :

Concept:

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:

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. 

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