Savepoints in MySQL - Mini Project: Build & Apply

Using Savepoints in MySQL Transactions

📖 Scenario: You are managing a small online bookstore database. You want to update the stock and prices of some books, but you want to be able to undo some changes if needed without losing all your work.

🎯 Goal: Learn how to use SAVEPOINT in MySQL transactions to mark points you can roll back to, allowing partial undo of changes.

📋 What You'll Learn

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

Insert three specific books with given stock and price values.

Start a transaction and create a savepoint named before_update.

Update stock and price for one book, then roll back to the savepoint.

Commit the transaction to save final changes.

💡 Why This Matters

🌍 Real World

Savepoints help manage complex database changes by allowing partial undo without losing all progress, useful in financial systems, inventory management, and more.

💼 Career

Understanding savepoints is important for database administrators and developers to write reliable and safe transactional code.

1

Create the books table and insert initial data

Write SQL statements to create a table called books with columns id (integer primary key), title (varchar 100), stock (integer), and price (decimal 5,2). Then insert these three rows exactly: (1, 'Learn SQL', 10, 29.99), (2, 'Python Basics', 5, 39.99), and (3, 'Web Design', 8, 24.99).

MySQL

-- Create the books table and insert initial data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Start a transaction and create a savepoint

Write SQL statements to start a transaction with START TRANSACTION; and then create a savepoint named before_update using SAVEPOINT before_update;.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  stock INT,
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, stock, price) VALUES
(1, 'Learn SQL', 10, 29.99),
(2, 'Python Basics', 5, 39.99),
(3, 'Web Design', 8, 24.99);

-- Start transaction and create savepoint
-- Your code here

Need a hint?

Use START TRANSACTION; to begin and SAVEPOINT before_update; to mark a savepoint.

3

Update a book and roll back to the savepoint

Write an UPDATE statement to change the stock of the book with id = 2 to 3 and the price to 34.99. Then write a statement to roll back to the savepoint before_update using ROLLBACK TO SAVEPOINT before_update;.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  stock INT,
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, stock, price) VALUES
(1, 'Learn SQL', 10, 29.99),
(2, 'Python Basics', 5, 39.99),
(3, 'Web Design', 8, 24.99);

START TRANSACTION;
SAVEPOINT before_update;

-- Update book and rollback to savepoint
-- Your code here

Need a hint?

Use UPDATE books SET stock = 3, price = 34.99 WHERE id = 2; and then ROLLBACK TO SAVEPOINT before_update;.

4

Commit the transaction

Write the SQL statement to commit the transaction using COMMIT; so that all changes except those rolled back are saved.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  stock INT,
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, stock, price) VALUES
(1, 'Learn SQL', 10, 29.99),
(2, 'Python Basics', 5, 39.99),
(3, 'Web Design', 8, 24.99);

START TRANSACTION;
SAVEPOINT before_update;

UPDATE books SET stock = 3, price = 34.99 WHERE id = 2;
ROLLBACK TO SAVEPOINT before_update;

-- Commit the transaction
-- Your code here

Need a hint?

Use COMMIT; to save the transaction changes.