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

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

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

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

Need a hint?

Use COMMIT; to finalize the transaction.