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

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

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

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

Hint

Use COMMIT; to save the transaction changes.

Practice

1. What is the main purpose of a SAVEPOINT in MySQL transactions?

easy

A. To start a new transaction automatically

B. To permanently save changes to the database

C. To mark a point in a transaction to which you can roll back later

D. To commit all changes immediately

Solution

1. Step 1: Understand what a savepoint does

   A savepoint marks a specific point inside a transaction to which you can roll back without affecting earlier changes.

2. Step 2: Differentiate from commit and start

   Unlike commit, which saves all changes permanently, savepoints allow partial rollback. It does not start a new transaction.

3. Final Answer:

   To mark a point in a transaction to which you can roll back later -> Option C

4. Quick Check:

   Savepoint = partial rollback marker [OK]

Hint: Savepoints let you undo part of a transaction, not all [OK]

Common Mistakes:

• Confusing savepoint with commit
• Thinking savepoint starts a new transaction
• Assuming savepoint permanently saves changes

2. Which of the following is the correct syntax to create a savepoint named sp1 in MySQL?

easy

A. SAVEPOINT sp1;

B. CREATE SAVEPOINT sp1;

C. SET SAVEPOINT sp1;

D. BEGIN SAVEPOINT sp1;

Solution

1. Step 1: Recall MySQL savepoint syntax

   The correct syntax to create a savepoint is simply SAVEPOINT savepoint_name;.

2. Step 2: Check other options for correctness

   Options B, C, and D use incorrect keywords like CREATE, SET, or BEGIN which are not used for savepoints.

3. Final Answer:

   SAVEPOINT sp1; -> Option A

4. Quick Check:

   Savepoint syntax = SAVEPOINT name; [OK]

Hint: Use just SAVEPOINT plus name to create one [OK]

Common Mistakes:

• Adding CREATE or SET before SAVEPOINT
• Using BEGIN with SAVEPOINT
• Missing semicolon at end

3. Consider this transaction sequence:

START TRANSACTION;
INSERT INTO accounts VALUES (1, 100);
SAVEPOINT sp1;
UPDATE accounts SET balance = 200 WHERE id = 1;
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

What will be the final balance for account with id=1?

medium

A. 100

B. 200

C. 0

D. NULL

Solution

1. Step 1: Follow the transaction steps

   First, a row with balance 100 is inserted. Then a savepoint sp1 is created. Next, balance is updated to 200.

2. Step 2: Rollback to savepoint sp1

   Rolling back to sp1 undoes the update but keeps the insert. So balance returns to 100.

3. Final Answer:

   100 -> Option A

4. Quick Check:

   Rollback to savepoint undoes changes after it [OK]

Hint: Rollback to savepoint undoes later changes only [OK]

Common Mistakes:

• Thinking rollback undoes entire transaction
• Assuming balance stays 200 after rollback
• Confusing commit effects

4. What is wrong with this MySQL transaction code?

START TRANSACTION;
SAVEPOINT sp1;
ROLLBACK TO sp2;
COMMIT;

medium

A. You must commit before creating a savepoint

B. ROLLBACK TO requires a semicolon after savepoint name

C. SAVEPOINT must come after ROLLBACK

D. You cannot rollback to a savepoint that was not created

Solution

1. Step 1: Check savepoint creation

   The code creates savepoint sp1 but tries to rollback to sp2 which does not exist.

2. Step 2: Understand rollback to savepoint rules

   You can only rollback to savepoints that were previously created in the current transaction.

3. Final Answer:

   You cannot rollback to a savepoint that was not created -> Option D

4. Quick Check:

   Rollback to unknown savepoint = error [OK]

Hint: Rollback only to existing savepoints in current transaction [OK]

Common Mistakes:

• Trying to rollback to a non-existent savepoint
• Misordering savepoint and rollback commands
• Assuming semicolon placement causes error

5. You want to update multiple rows in a transaction but be able to undo only the last two updates if needed. Which sequence correctly uses savepoints to achieve this?

hard

A. START TRANSACTION; SAVEPOINT sp1; UPDATE row1; SAVEPOINT sp2; UPDATE row2; UPDATE row3; ROLLBACK TO sp2; COMMIT;

B. START TRANSACTION; UPDATE row1; SAVEPOINT sp1; UPDATE row2; SAVEPOINT sp2; UPDATE row3; ROLLBACK TO sp1; COMMIT;

C. START TRANSACTION; UPDATE row1; UPDATE row2; SAVEPOINT sp1; UPDATE row3; ROLLBACK TO sp1; COMMIT;

D. START TRANSACTION; UPDATE row1; SAVEPOINT sp1; UPDATE row2; SAVEPOINT sp2; UPDATE row3; ROLLBACK TO sp2; COMMIT;

Solution

1. Step 1: Understand the goal

   You want to undo only the last two updates, meaning updates to row2 and row3, but keep update to row1.

2. Step 2: Analyze each option

   START TRANSACTION; UPDATE row1; SAVEPOINT sp1; UPDATE row2; SAVEPOINT sp2; UPDATE row3; ROLLBACK TO sp2; COMMIT;: START TRANSACTION; UPDATE row1; SAVEPOINT sp1; UPDATE row2; SAVEPOINT sp2; UPDATE row3; ROLLBACK TO sp2; COMMIT; sets sp1 after row1 update, sp2 after row2 update, then updates row3. Rolling back to sp2 undoes only the update to row3, keeping updates to row1 and row2.

3. Step 3: Re-examine options carefully

   START TRANSACTION; UPDATE row1; SAVEPOINT sp1; UPDATE row2; SAVEPOINT sp2; UPDATE row3; ROLLBACK TO sp1; COMMIT; rolls back to sp1, undoing updates to row2 and row3, which matches the goal of undoing last two updates.

4. Final Answer:

   START TRANSACTION; UPDATE row1; SAVEPOINT sp1; UPDATE row2; SAVEPOINT sp2; UPDATE row3; ROLLBACK TO sp1; COMMIT; -> Option B

5. Quick Check:

   Rollback to earlier savepoint undoes all later changes [OK]

Hint: Rollback to savepoint before last two updates to undo them [OK]

Common Mistakes:

• Rolling back to wrong savepoint
• Not placing savepoints after each update
• Confusing which updates get undone