ACID properties mental model in SQL - Mini Project: Build & Apply

ACID Properties Mental Model

📖 Scenario: You are managing a small online bookstore database. You want to understand how the database keeps your data safe and consistent when multiple customers buy books at the same time.

🎯 Goal: Build a simple SQL example that shows how the ACID properties (Atomicity, Consistency, Isolation, Durability) work together to keep the bookstore database reliable.

📋 What You'll Learn

Create a table called books with columns id (integer), title (text), and stock (integer).

Insert three books with exact titles and stock values.

Create a transaction block that tries to sell one book by reducing its stock by 1.

Include a condition to rollback if the stock is not enough (simulate atomicity).

💡 Why This Matters

🌍 Real World

Online stores and banking systems use transactions to keep data accurate and safe when many users interact at once.

💼 Career

Understanding ACID properties is essential for database administrators and developers to build reliable applications.

1

Create the books table

Write SQL to create a table called books with columns id as integer primary key, title as text, and stock as integer.

SQL

-- Your code here to create the books table

Hint

Use CREATE TABLE with the exact column names and types.

2

Insert initial book data

Insert three books into the books table with these exact entries: (1, 'Learn SQL', 5), (2, 'Database Basics', 3), and (3, 'ACID Explained', 2).

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  stock INTEGER
);
-- Your code here to insert the three books

Hint

Use one INSERT INTO statement with all three rows.

3

Start a transaction to sell a book

Write SQL to begin a transaction, then update the stock of the book with id = 3 by subtracting 1 only if the current stock is greater than 0.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  stock INTEGER
);

INSERT INTO books (id, title, stock) VALUES
  (1, 'Learn SQL', 5),
  (2, 'Database Basics', 3),
  (3, 'ACID Explained', 2);

-- Your code here to start a transaction and update stock if available

Hint

Use BEGIN TRANSACTION; and an UPDATE with a condition on stock.

4

Complete the transaction with rollback condition

Add SQL to check if the stock was updated. If no rows were updated (stock was 0), rollback the transaction; otherwise, commit it.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  stock INTEGER
);

INSERT INTO books (id, title, stock) VALUES
  (1, 'Learn SQL', 5),
  (2, 'Database Basics', 3),
  (3, 'ACID Explained', 2);

BEGIN TRANSACTION;

UPDATE books
SET stock = stock - 1
WHERE id = 3 AND stock > 0;

-- Your code here to rollback if no rows updated, else commit

Hint

Use ROLLBACK and COMMIT to complete the transaction. In real systems, you check rows affected to decide.

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