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

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

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

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

Hint

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

Practice

1. What does the BEGIN statement do in MySQL transactions?

easy

A. It permanently saves all changes made to the database.

B. It starts a transaction block where changes can be grouped safely.

C. It cancels all changes made in the current transaction.

D. It deletes the current database table.

Solution

1. Step 1: Understand the role of BEGIN

   The BEGIN statement marks the start of a transaction block where multiple changes can be grouped.

2. Step 2: Differentiate from COMMIT and ROLLBACK

   COMMIT saves changes, ROLLBACK cancels changes, but BEGIN only starts the grouping.

3. Final Answer:

   It starts a transaction block where changes can be grouped safely. -> Option B

4. Quick Check:

   BEGIN starts transaction [OK]

Hint: BEGIN always starts a safe group of changes [OK]

Common Mistakes:

• Confusing BEGIN with COMMIT
• Thinking BEGIN saves changes
• Assuming BEGIN cancels changes

2. Which of the following is the correct syntax to start a transaction in MySQL?

easy

A. SET TRANSACTION;

B. BEGIN TRANSACTION;

C. BEGIN;

D. OPEN TRANSACTION;

Solution

1. Step 1: Recall MySQL transaction start syntax

   The simplest and most common syntax in MySQL to start a transaction is BEGIN;.

2. Step 2: Check options for exact syntax

   BEGIN TRANSACTION; and OPEN TRANSACTION; are not valid MySQL syntax.

3. Final Answer:

   BEGIN; -> Option C

4. Quick Check:

   BEGIN; is correct syntax [OK]

Hint: Use just BEGIN; to start a transaction in MySQL [OK]

Common Mistakes:

• Using BEGIN TRANSACTION; which is invalid in MySQL
• Confusing with other SQL dialects
• Adding extra keywords like OPEN

3. Consider this sequence of commands in MySQL:
BEGIN;
INSERT INTO users (name) VALUES ('Alice');
ROLLBACK;
SELECT * FROM users WHERE name = 'Alice';
What will the SELECT query return?

medium

A. It will return an empty result (no rows).

B. It will return the row with name 'Alice'.

C. It will cause a syntax error.

D. It will return all rows in the users table.

Solution

1. Step 1: Analyze the transaction commands

   The BEGIN; starts a transaction, then an INSERT adds 'Alice'. The ROLLBACK; cancels this change.

2. Step 2: Understand the effect of ROLLBACK

   Since the INSERT was rolled back, the row with 'Alice' was never saved.

3. Final Answer:

   It will return an empty result (no rows). -> Option A

4. Quick Check:

   ROLLBACK cancels changes [OK]

Hint: ROLLBACK undoes changes made after BEGIN [OK]

Common Mistakes:

• Assuming INSERT stays after ROLLBACK
• Thinking ROLLBACK saves changes
• Confusing ROLLBACK with COMMIT

4. You run this MySQL code:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
ROLLBACK;
What is the effect of the final ROLLBACK;?

medium

A. It undoes the UPDATE and restores the original balance.

B. It causes an error because there is no active transaction.

C. It commits the changes permanently.

D. It does nothing because COMMIT already saved changes.

Solution

1. Step 1: Follow the transaction flow

   BEGIN; starts a transaction, UPDATE changes balance, COMMIT; saves changes permanently.

2. Step 2: Understand ROLLBACK after COMMIT

   After commit, no active transaction exists, so ROLLBACK; does nothing.

3. Final Answer:

   It does nothing because COMMIT already saved changes. -> Option D

4. Quick Check:

   ROLLBACK after COMMIT has no effect [OK]

Hint: ROLLBACK after COMMIT does nothing [OK]

Common Mistakes:

• Thinking ROLLBACK can undo after COMMIT
• Expecting an error on ROLLBACK without active transaction
• Confusing order of COMMIT and ROLLBACK

5. You want to transfer $200 from account A (id=1) to account B (id=2) safely using transactions. Which sequence correctly ensures both updates happen together or not at all?

hard

A. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2; COMMIT;

B. UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2;

C. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; ROLLBACK; UPDATE accounts SET balance = balance + 200 WHERE id = 2; COMMIT;

D. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2; ROLLBACK;

Solution

1. Step 1: Identify the need for atomicity

   Both updates must succeed or fail together to keep balances consistent.

2. Step 2: Check each option's transaction handling

   BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2; COMMIT; uses BEGIN and COMMIT to group both updates safely. UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2; has no transaction, so partial updates can happen. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; ROLLBACK; UPDATE accounts SET balance = balance + 200 WHERE id = 2; COMMIT; rolls back after first update, so second update is outside transaction. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2; ROLLBACK; rolls back after both updates, canceling changes.

3. Final Answer:

   BEGIN; UPDATE ...; UPDATE ...; COMMIT; -> Option A

4. Quick Check:

   Use BEGIN and COMMIT to group related updates [OK]

Hint: Group related updates between BEGIN and COMMIT [OK]

Common Mistakes:

• Not using transactions for multi-step updates
• Rolling back too early or too late
• Missing COMMIT to save changes