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.