ACID properties in MySQL - Mini Project: Build & Apply

Understanding ACID Properties with MySQL Transactions

📖 Scenario: You are managing a small online bookstore database. You want to ensure that when a customer places an order, the database updates the stock and records the order correctly without errors or data loss.

🎯 Goal: Build a simple MySQL transaction that demonstrates the ACID properties by updating stock and inserting an order record safely.

📋 What You'll Learn

Create a table called books with columns id, title, and stock

Create a table called orders with columns order_id, book_id, and quantity

Set a variable @order_quantity to 2

Write a transaction that decreases the stock of a book with id = 1 by @order_quantity

Insert a new order record with book_id = 1 and quantity = @order_quantity

Commit the transaction to save changes

💡 Why This Matters

🌍 Real World

Online stores and inventory systems use transactions to keep data accurate and consistent when multiple changes happen together.

💼 Career

Understanding ACID transactions is essential for database administrators and backend developers to ensure reliable data operations.

1

Create the initial tables

Create a table called books with columns id (integer primary key), title (varchar 100), and stock (integer). Also create a table called orders with columns order_id (integer primary key auto_increment), book_id (integer), and quantity (integer).

MySQL

-- Your code here to create tables books and orders

Hint

Use CREATE TABLE statements with the exact column names and types.

2

Set the order quantity variable

Set a MySQL user-defined variable called @order_quantity to the value 2.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  stock INT
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  book_id INT,
  quantity INT
);

-- Your code here to set @order_quantity

Hint

Use SET @order_quantity = 2; to create the variable.

3

Write the transaction to update stock and insert order

Start a transaction with START TRANSACTION;. Then write an UPDATE statement to reduce the stock of the book with id = 1 by @order_quantity. Next, write an INSERT statement to add a new row into orders with book_id = 1 and quantity = @order_quantity.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  stock INT
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  book_id INT,
  quantity INT
);

SET @order_quantity = 2;

-- Your code here to start transaction, update stock, and insert order

Hint

Use START TRANSACTION; then UPDATE and INSERT statements as described.

4

Commit the transaction

Add the statement COMMIT; to save the changes made in the transaction.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  stock INT
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  book_id INT,
  quantity INT
);

SET @order_quantity = 2;

START TRANSACTION;
UPDATE books SET stock = stock - @order_quantity WHERE id = 1;
INSERT INTO orders (book_id, quantity) VALUES (1, @order_quantity);

-- Your code here to commit the transaction

Hint

Use COMMIT; to finalize the transaction.

Practice

1. Which of the following is NOT one of the ACID properties in databases?

easy

A. Durability and Speed

B. Consistency

C. Isolation

D. Atomicity

Solution

1. Step 1: Recall the ACID properties

   ACID stands for Atomicity, Consistency, Isolation, and Durability.

2. Step 2: Identify the incorrect option

   Durability and Speed adds "Speed" which is not part of ACID properties.

3. Final Answer:

   Durability and Speed -> Option A

4. Quick Check:

   ACID excludes Speed [OK]

Hint: Remember ACID = Atomicity, Consistency, Isolation, Durability [OK]

Common Mistakes:

• Confusing performance terms with ACID properties
• Thinking Speed is part of ACID
• Mixing up Isolation with Integrity

2. Which SQL statement correctly starts a transaction in MySQL?

easy

A. BEGIN TRANSACTION;

B. START TRANSACTION;

C. OPEN TRANSACTION;

D. INIT TRANSACTION;

Solution

1. Step 1: Recall MySQL transaction syntax

   MySQL uses START TRANSACTION; to begin a transaction.

2. Step 2: Check other options

   Options A, B, and D are not valid MySQL commands for starting transactions.

3. Final Answer:

   START TRANSACTION; -> Option B

4. Quick Check:

   MySQL uses START TRANSACTION [OK]

Hint: Use START TRANSACTION to begin in MySQL [OK]

Common Mistakes:

• Using BEGIN TRANSACTION which is not valid in MySQL
• Confusing with other SQL dialects
• Typing incorrect keywords like OPEN or INIT

3. Consider this sequence in MySQL:

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

What ACID property does this sequence mainly demonstrate?

medium

A. Consistency - data types are checked

B. Isolation - changes are visible immediately

C. Durability - changes are temporary

D. Atomicity - all changes happen or none

Solution

1. Step 1: Understand the transaction sequence

   The two updates are grouped inside a transaction with COMMIT at the end.

2. Step 2: Identify the ACID property

   Atomicity ensures either both updates happen or none do, preventing partial changes.

3. Final Answer:

   Atomicity - all changes happen or none -> Option D

4. Quick Check:

   Transaction groups changes atomically [OK]

Hint: Atomicity means all or nothing in transactions [OK]

Common Mistakes:

• Confusing Isolation with Atomicity
• Thinking Durability means temporary changes
• Assuming Consistency only checks data types

4. You wrote this MySQL transaction:

START TRANSACTION;
UPDATE inventory SET quantity = quantity - 5 WHERE product_id = 10;
COMMIT;

But sometimes the quantity becomes negative. What ACID property is likely violated?

medium

A. Consistency

B. Isolation

C. Durability

D. Atomicity

Solution

1. Step 1: Analyze the problem

   Quantity becoming negative means data rules are broken.

2. Step 2: Identify violated ACID property

   Consistency ensures data stays valid and follows rules; here it is violated.

3. Final Answer:

   Consistency -> Option A

4. Quick Check:

   Consistency keeps data valid [OK]

Hint: Consistency means data must follow rules always [OK]

Common Mistakes:

• Thinking Atomicity prevents negative values
• Confusing Isolation with data validity
• Assuming Durability fixes data rules

5. In a multi-user MySQL environment, two transactions run:

Transaction 1:
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- waits before COMMIT

Transaction 2:
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

Which ACID property ensures Transaction 2 does not see the uncommitted balance change from Transaction 1?

hard

A. Atomicity

B. Durability

C. Isolation

D. Consistency

Solution

1. Step 1: Understand concurrent transactions

   Transaction 1 updates but delays commit; Transaction 2 reads the same data.

2. Step 2: Identify ACID property controlling visibility

   Isolation ensures uncommitted changes are hidden from other transactions.

3. Final Answer:

   Isolation -> Option C

4. Quick Check:

   Isolation hides uncommitted changes [OK]

Hint: Isolation hides uncommitted changes from others [OK]

Common Mistakes:

• Confusing Isolation with Atomicity
• Thinking Durability controls visibility
• Assuming Consistency hides uncommitted data