ACID Properties - Free MCQ Practice Test with solutions, GATE CSE (CSE)

ACID properties of database transaction:

• Atomicity: The entire transaction takes place at once or doesn’t happen at all.
• Consistency: The database must be consistent before and after the transaction.
• Isolation: Multiple transactions occur independently without interference.
• Durability: The changes of a successful transaction occurs even if the system failure occurs.

So, deadlock freedom is not the ACID property of database transaction.

Atomicity requires that each transaction be "all or nothing": if one part of the transaction fails, then the entire transaction fails, and the database state is left unchanged.

The consistency property ensures that any transaction will bring the database from one valid state to another.

The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed sequentially.

The durability property ensures that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors.

Statement:

“The changes applied to the database by a committed transaction must persist in the database, and these changes must not be lost because of any failure”

ACID Properties:

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 its operations are executed or none.

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.

Isolation − In a database system where more than one transaction is 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.

Durability − The database should be durable enough to hold all its latest updates even if the system fails or restarts.

Therefore, Durability is the answer.

Option 1: Incorrect
As, it is not giving any surety about T1, there is no commit in T1.  Rolling back of T2 may not be required in this.

Option 2: Correct
In given schedule, T2 is reading data item A that is written by T1 so it means commit of T1 must before the commit of T2, but here in this schedule it is not happening. So, this is non – recoverable schedule. In this after T1 fails, if we want to undo all the operations, still we cannot undo a committed transaction. So, it cannot ensure transaction atomicity.

Option 3: Incorrect
It is redoing all the operations of T2. But in this, it is reading data item A that is written by T1 and T1 is not committed. So, it results in dirty read problem.

Option 4: Incorrect
Since Schedule S is non – recoverable. So, it is incorrect. Also, it cannot ensure atomicity.
 

ACID Properties:

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 its operations are executed or none.There are no partial transactions

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.

Isolation − In a database system where more than one transaction is 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.

Durability − The database should be durable enough to hold all its latest updates even if the system fails or restarts.

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 constraint that the sum of the accounts x and y should remain constant is that of consistency.

Above operation are shown in table as:

Now, as T1 and T3 are uncommitted, they must be undone. T2 is committed but it is after checkpoint so, it must be redone. As, T4 is already committed before checkpoint so, it neither comes in undo list nor in redo list.

ACID Properties:

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 its operations are executed or none.

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.

Isolation − In a database system where more than one transaction is 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.

Durability − The database should be durable enough to hold all its latest updates even if the system fails or restarts.

ACID Properties:

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 its operations are executed or none.

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.

Isolation − In a database system where more than one transaction is 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.

Durability − The database should be durable enough to hold all its latest updates even if the system fails or restarts.

Therefore, Option 2 is correct

ACID Properties:

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 its operations are executed or none.

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.

Isolation − In a database system where more than one transaction is 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.

Durability − The database should be durable enough to hold all its latest updates even if the system fails or restarts.

Therefore, Isolation is the correct answer.