ACID properties mental model in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Atomicity in Transactions

Which of the following best describes the Atomicity property in database transactions?

A Data must remain consistent before and after a transaction.

B All parts of a transaction must complete successfully; otherwise, none are applied.

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

D Transactions must be isolated from each other to prevent interference.

💡 Hint

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

Atomicity means a transaction is all or nothing: either all steps succeed or none do.

🧠 Conceptual

intermediate

Identifying Isolation in ACID

What does the Isolation property ensure in concurrent database transactions?

A Transactions appear to run one after another, not simultaneously.

B Data is saved permanently after commit.

C Transactions must be atomic and indivisible.

D Data remains consistent before and after transactions.

💡 Hint

Think about how transactions behave when multiple users access the database at the same time.

Isolation means transactions do not see each other's intermediate states, making them appear sequential.

❓ query_result

advanced

Effect of Durability After Crash

Consider a transaction that updates a bank account balance and commits successfully. The system crashes immediately after commit. What will be the state of the updated balance after recovery?

A The updated balance will be permanently saved and visible after recovery.

B The updated balance will be lost and revert to the old value.

C The balance will be partially updated causing inconsistency.

D The balance will be locked and inaccessible until manual fix.

💡 Hint

Think about what durability guarantees after a commit.

Durability ensures that once a transaction commits, its changes survive crashes and are permanent.

🧠 Conceptual

advanced

Consistency in Database Transactions

Which statement best explains the Consistency property in ACID?

A All parts of a transaction must succeed or none at all.

B Transactions must be isolated from each other to avoid conflicts.

C Transactions transform the database from one valid state to another, preserving rules.

D Changes made by a transaction are permanent after commit.

💡 Hint

Think about database rules and constraints before and after a transaction.

Consistency means transactions must keep the database valid according to all rules and constraints.

🔧 Debug

expert

Diagnosing Isolation Level Effects

Given two concurrent transactions:
Transaction 1 reads a value, waits, then updates it.
Transaction 2 updates the same value and commits during Transaction 1's wait.
Which isolation level will cause Transaction 1 to see the updated value from Transaction 2 during its read?

A Read Committed

B Repeatable Read

C Serializable

D Read Uncommitted

💡 Hint

Consider which isolation level allows reading uncommitted or dirty data.

Read Uncommitted allows reading data from other transactions even before they commit, causing dirty reads.

Practice

1. Which ACID property ensures that a transaction is completed fully or not at all, like finishing a whole recipe or not starting it?

easy

A. Isolation

B. Consistency

C. Atomicity

D. Durability

Solution

1. Step 1: Understand Atomicity meaning

   Atomicity means a transaction is all-or-nothing, like a recipe that must be fully done or not started.

2. Step 2: Compare with other properties

   Consistency means data rules are followed, Isolation means transactions don't interfere, Durability means changes are saved permanently.

3. Final Answer:

   Atomicity -> Option C

4. Quick Check:

   All or nothing = Atomicity [OK]

Hint: All or nothing means Atomicity [OK]

Common Mistakes:

• Confusing Consistency with Atomicity
• Thinking Isolation means all-or-nothing
• Mixing Durability with Atomicity

2. Which of the following SQL transaction commands is used to make all changes permanent, supporting the Durability property?

easy

A. ROLLBACK

B. COMMIT

C. SAVEPOINT

D. BEGIN TRANSACTION

Solution

1. Step 1: Identify command for saving changes

   COMMIT saves all changes permanently, ensuring Durability.

2. Step 2: Understand other commands

   ROLLBACK undoes changes, SAVEPOINT marks a point to rollback to, BEGIN TRANSACTION starts a transaction.

3. Final Answer:

   COMMIT -> Option B

4. Quick Check:

   Permanent save = COMMIT [OK]

Hint: COMMIT makes changes permanent [OK]

Common Mistakes:

• Choosing ROLLBACK to save changes
• Confusing SAVEPOINT with COMMIT
• Thinking BEGIN TRANSACTION saves data

3. Consider two transactions running at the same time: Transaction 1 adds 100 to an account balance, Transaction 2 subtracts 50. Which ACID property ensures the final balance is correct as if transactions ran one after another?

medium

A. Isolation

B. Consistency

C. Atomicity

D. Durability

Solution

1. Step 1: Understand concurrent transactions

   When two transactions run together, Isolation ensures they don't interfere and produce correct results.

2. Step 2: Differentiate from other properties

   Atomicity is about all-or-nothing, Consistency is about data rules, Durability is about saving changes permanently.

3. Final Answer:

   Isolation -> Option A

4. Quick Check:

   Transactions separated = Isolation [OK]

Hint: No interference means Isolation [OK]

Common Mistakes:

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

4. A developer notices that after a system crash, some committed transactions are lost. Which ACID property is likely violated, and what should be fixed?

medium

A. Atomicity; ensure transactions are all-or-nothing

B. Consistency; fix data validation rules

C. Isolation; improve transaction locking

D. Durability; improve data saving and recovery

Solution

1. Step 1: Identify property related to crash recovery

   Durability ensures committed transactions survive crashes and are saved permanently.

2. Step 2: Match problem to property

   Lost committed transactions after crash means Durability is violated and saving/recovery must be improved.

3. Final Answer:

   Durability; improve data saving and recovery -> Option D

4. Quick Check:

   Crash survival = Durability [OK]

Hint: Lost data after crash means Durability issue [OK]

Common Mistakes:

• Confusing Atomicity with crash recovery
• Thinking Isolation fixes lost data
• Mixing Consistency with durability problems

5. A bank system must ensure that transferring money from one account to another never leaves the database in an incorrect state, even if the system crashes or many users act at once. Which combination of ACID properties best guarantees this?

hard

A. Atomicity, Consistency, and Durability

B. Consistency and Isolation

C. Isolation and Durability

D. Atomicity and Durability

Solution

1. Step 1: Identify properties needed for correctness and crash safety

   Atomicity ensures full transfer or none, Consistency keeps data valid, Durability saves changes permanently.

2. Step 2: Understand why Isolation alone is not enough

   Isolation prevents interference but does not guarantee correctness or crash safety alone.

3. Step 3: Combine properties for full guarantee

   Atomicity + Consistency + Durability together ensure safe, correct, and permanent transfers.

4. Final Answer:

   Atomicity, Consistency, and Durability -> Option A

5. Quick Check:

   Safe + correct + permanent = Atomicity + Consistency + Durability [OK]

Hint: All but Isolation ensure correctness and crash safety [OK]

Common Mistakes:

• Ignoring Consistency in correctness
• Thinking Isolation alone is enough
• Missing Durability for crash safety