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

Need a 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

Need a 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

Need a 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

Need a hint?

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