ACID properties in DBMS Theory - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the acronym ACID stand for in database systems?

ACID stands for Atomicity, Consistency, Isolation, and Durability. These are key properties that ensure reliable processing of database transactions.

beginner

Explain Atomicity in the context of ACID properties.

Atomicity means a transaction is treated as a single unit. Either all its operations complete successfully, or none do. This prevents partial updates to the database.

beginner

What does Consistency ensure in a database transaction?

Consistency ensures that a transaction brings the database from one valid state to another, following all rules like constraints and data integrity.

intermediate

Describe Isolation in ACID properties with a real-life example.

Isolation means transactions run independently without interfering with each other. For example, two people booking seats online won't see each other's incomplete bookings, avoiding conflicts.

beginner

What is Durability in ACID properties and why is it important?

Durability means once a transaction is committed, its changes are permanent even if the system crashes. This ensures data is not lost after confirmation.

Which ACID property ensures that a transaction is all-or-nothing?

A Durability

B Atomicity

C Isolation

D Consistency

Atomicity guarantees that all parts of a transaction succeed or none do, preventing partial updates.

Which property ensures the database remains valid after a transaction?

A Isolation

B Durability

C Atomicity

D Consistency

Consistency ensures the database follows all rules and constraints after a transaction.

What does Isolation prevent during concurrent transactions?

A Interference between transactions

B Partial updates

C Data loss

D Invalid data states

Isolation prevents transactions from affecting each other while running at the same time.

Durability means that after a transaction is committed, the data is:

A Permanently saved

B Temporary

C Lost if system crashes

D Only saved in memory

Durability ensures data changes are permanent even after failures.

If a transaction fails halfway, which ACID property ensures no changes are saved?

A Isolation

B Consistency

C Atomicity

D Durability

Atomicity ensures that incomplete transactions do not affect the database.

Describe each of the ACID properties and explain why they are important for database transactions.

Think about how each property helps keep data correct and safe.

Give a real-life example that illustrates the Isolation property in a database system.

Consider situations like online bookings or banking.

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