BEGIN, COMMIT, ROLLBACK in MySQL - Mini Project: Build & Apply

Using BEGIN, COMMIT, and ROLLBACK in MySQL Transactions

📖 Scenario: You are managing a small online store database. You want to update the stock quantity of a product and record the sale in a sales table. To keep the data safe, you will use transactions to make sure both actions happen together or not at all.

🎯 Goal: Build a MySQL transaction that starts with BEGIN, updates the stock quantity in the products table, inserts a record into the sales table, and then either commits or rolls back the transaction.

📋 What You'll Learn

Create a products table with columns product_id (INT), product_name (VARCHAR), and stock (INT).

Create a sales table with columns sale_id (INT AUTO_INCREMENT), product_id (INT), and quantity_sold (INT).

Start a transaction using BEGIN.

Update the stock of a product by subtracting the sold quantity.

Insert a new sale record into the sales table.

Use COMMIT to save changes if all queries succeed.

Use ROLLBACK to undo changes if there is an error.

💡 Why This Matters

🌍 Real World

Transactions are used in real-world databases to keep data consistent and safe when multiple related changes happen together.

💼 Career

Understanding BEGIN, COMMIT, and ROLLBACK is essential for database administrators and developers to manage data integrity and error recovery.

1

Create the products and sales tables

Write SQL statements to create a products table with columns product_id (INT), product_name (VARCHAR(50)), and stock (INT). Also create a sales table with columns sale_id (INT AUTO_INCREMENT PRIMARY KEY), product_id (INT), and quantity_sold (INT).

MySQL

-- Your code here to create the products and sales tables

Need a hint?

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

2

Start the transaction with BEGIN

Write the SQL statement to start a transaction using the keyword BEGIN.

MySQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(50),
  stock INT
);

CREATE TABLE sales (
  sale_id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT,
  quantity_sold INT
);

-- Start the transaction
-- Your code here

Need a hint?

Use the BEGIN; statement to start the transaction.

3

Update stock and insert sale record

Write SQL statements to update the stock in the products table by subtracting 3 from the product with product_id = 1. Then insert a new row into the sales table with product_id = 1 and quantity_sold = 3.

MySQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(50),
  stock INT
);

CREATE TABLE sales (
  sale_id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT,
  quantity_sold INT
);

BEGIN;

-- Update stock and insert sale
-- Your code here

Need a hint?

Use UPDATE to reduce stock and INSERT INTO to add a sale record.

4

Complete the transaction with COMMIT or ROLLBACK

Add the SQL statement COMMIT; to save the changes made in the transaction. Also add a comment line showing how to use ROLLBACK; to undo changes if needed.

MySQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(50),
  stock INT
);

CREATE TABLE sales (
  sale_id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT,
  quantity_sold INT
);

BEGIN;

UPDATE products SET stock = stock - 3 WHERE product_id = 1;
INSERT INTO sales (product_id, quantity_sold) VALUES (1, 3);

-- Add COMMIT statement below
-- Your code here

Need a hint?

Use COMMIT; to save and add a comment with ROLLBACK; for undoing.