ACID properties in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Atomicity in Transactions

Which statement best describes the Atomicity property in database transactions?

A Transactions are isolated from each other to prevent interference.

B Data remains consistent before and after a transaction.

C All parts of a transaction must complete successfully or none at all.

D Once a transaction is committed, changes are permanent even after a crash.

💡 Hint

Think about whether a transaction can be partially done or not.

Atomicity means a transaction is all or nothing. If any part fails, the whole transaction is undone.

📋 Factual

intermediate

Identifying Durability in ACID

What does the Durability property guarantee in a database system?

A Transactions do not affect each other’s data during execution.

B Changes made by a committed transaction are saved permanently, even if the system crashes.

C Data is always valid before and after a transaction.

D A transaction can be partially completed if needed.

💡 Hint

Consider what happens to data after a power failure.

Durability means once a transaction commits, its changes survive system failures.

🔍 Analysis

advanced

Analyzing Isolation Levels

Which scenario best illustrates a violation of the Isolation property in transactions?

A Two transactions read and write the same data simultaneously, causing inconsistent results.

B A transaction fails halfway and rolls back all changes.

C Data remains consistent before and after a transaction commits.

D Once a transaction commits, its changes are permanent.

💡 Hint

Think about what happens when transactions interfere with each other.

Isolation prevents transactions from seeing intermediate states of others. Simultaneous conflicting access breaks this.

❓ Comparison

advanced

Comparing Consistency and Atomicity

How does the Consistency property differ from Atomicity in ACID?

A Both mean the same and can be used interchangeably.

B Atomicity guarantees transaction completion; Consistency guarantees data rules.

C Consistency means transactions are isolated; Atomicity means changes are permanent.

D Consistency ensures data rules are followed after a transaction; Atomicity ensures the transaction is all or nothing.

💡 Hint

Think about data correctness versus transaction completeness.

Consistency means data stays valid according to rules after a transaction; Atomicity means the transaction fully happens or not at all.

❓ Reasoning

expert

Determining the Outcome of a Transaction Sequence

Consider two transactions running concurrently on a bank database:

Transaction 1: Withdraw $100 from Account A
Transaction 2: Deposit $100 to Account A

If the system does not enforce Isolation, what problem might occur?

A The account balance might show an incorrect value due to overlapping operations.

B The withdrawal will always fail due to locking.

C The deposit will be lost permanently.

D The system will automatically rollback both transactions.

💡 Hint

Think about what happens if transactions see each other's partial changes.

Without isolation, transactions can interfere, causing incorrect balances due to reading uncommitted data.

Practice

1. Which of the following ACID properties ensures that a transaction is completed fully or not at all?

easy

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Solution

1. Step 1: Understand Atomicity in transactions

   Atomicity means a transaction must be all or nothing; it cannot be partially done.

2. Step 2: Compare with other ACID properties

   Consistency ensures data correctness, Isolation manages concurrent transactions, Durability guarantees data permanence after commit.

3. Final Answer:

   Atomicity -> Option A

4. Quick Check:

   All or nothing = Atomicity [OK]

Hint: All or nothing means Atomicity [OK]

Common Mistakes:

• Confusing Atomicity with Consistency
• Mixing Isolation with Durability
• Thinking Durability means transaction completeness

2. Which ACID property ensures that once a transaction is committed, its changes are permanent even if the system crashes?

easy

A. Consistency

B. Isolation

C. Atomicity

D. Durability

Solution

1. Step 1: Define Durability in ACID

   Durability guarantees that committed data is saved permanently and survives system failures.

2. Step 2: Differentiate from other properties

   Isolation controls transaction interference, Atomicity ensures full completion, Consistency maintains valid data states.

3. Final Answer:

   Durability -> Option D

4. Quick Check:

   Permanent changes after commit = Durability [OK]

Hint: Permanent data after commit means Durability [OK]

Common Mistakes:

• Confusing Durability with Isolation
• Thinking Atomicity means permanence
• Mixing Consistency with Durability

3. Consider a database transaction that updates two tables. If the transaction is interrupted after updating the first table but before the second, which ACID property prevents partial updates?

medium

A. Atomicity

B. Isolation

C. Durability

D. Consistency

Solution

1. Step 1: Analyze partial update scenario

   Partial updates mean some changes are saved while others are not, which is not allowed.

2. Step 2: Identify ACID property preventing partial updates

   Atomicity ensures the transaction is fully completed or fully rolled back, preventing partial changes.

3. Final Answer:

   Atomicity -> Option A

4. Quick Check:

   All or nothing update = Atomicity [OK]

Hint: Partial updates prevented by Atomicity [OK]

Common Mistakes:

• Choosing Durability for partial update
• Confusing Isolation with Atomicity
• Assuming Consistency handles partial updates

4. A developer notices that two transactions running at the same time cause incorrect data results. Which ACID property should be checked to fix this concurrency issue?

medium

A. Atomicity

B. Durability

C. Isolation

D. Consistency

Solution

1. Step 1: Understand concurrency problem

   Concurrent transactions interfering cause data errors due to lack of proper separation.

2. Step 2: Identify ACID property managing concurrency

   Isolation ensures transactions do not affect each other's intermediate states, preventing conflicts.

3. Final Answer:

   Isolation -> Option C

4. Quick Check:

   Concurrency control = Isolation [OK]

Hint: Concurrency issues fixed by Isolation [OK]

Common Mistakes:

• Mixing Isolation with Atomicity
• Thinking Durability controls concurrency
• Confusing Consistency with Isolation

5. A banking system must ensure that money transfer transactions are safe and reliable. Which combination of ACID properties best guarantees that money is neither lost nor duplicated during transfers?

hard

A. Consistency and Isolation

B. Atomicity and Isolation

C. Atomicity and Durability

D. Durability and Consistency

Solution

1. Step 1: Identify requirements for safe money transfer

   Money must not be lost (all steps complete) and no interference between transactions.

2. Step 2: Match requirements to ACID properties

   Atomicity ensures full completion or rollback, Isolation prevents concurrent transaction conflicts.

3. Step 3: Evaluate other options

   Durability ensures permanence but does not prevent duplication; Consistency ensures valid data but not transaction completeness or isolation.

4. Final Answer:

   Atomicity and Isolation -> Option B

5. Quick Check:

   Complete and separate transactions = Atomicity + Isolation [OK]

Hint: Safe transfers need Atomicity + Isolation [OK]

Common Mistakes:

• Choosing Durability alone
• Ignoring Isolation for concurrency
• Confusing Consistency with transaction completeness