Savepoints in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a savepoint in MySQL?

A savepoint is a marker within a transaction that allows you to roll back part of the transaction instead of the whole thing.

beginner

How do you create a savepoint in MySQL?

Use the SQL command SAVEPOINT savepoint_name; to create a savepoint inside a transaction.

intermediate

What happens when you roll back to a savepoint?

Rolling back to a savepoint undoes all changes made after that savepoint but keeps the transaction active.

intermediate

How do you release a savepoint?

Use RELEASE SAVEPOINT savepoint_name; to remove a savepoint. After releasing, you cannot roll back to it.

beginner

Can you use savepoints outside a transaction?

No, savepoints only work inside transactions. You must start a transaction before creating savepoints.

Which command creates a savepoint named 'sp1' in MySQL?

A START SAVEPOINT sp1;

B CREATE SAVEPOINT sp1;

C SAVEPOINT sp1;

D SET SAVEPOINT sp1;

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

What does ROLLBACK TO SAVEPOINT sp1; do?

A Rolls back changes made after savepoint 'sp1'

B Commits the transaction up to 'sp1'

C Rolls back the entire transaction

D Deletes the savepoint 'sp1'

It undoes all changes made after the savepoint 'sp1' but keeps the transaction active.

What happens if you try to create a savepoint outside a transaction?

A Savepoint is created successfully

B An error occurs

C Transaction starts automatically

D Savepoint is ignored

Savepoints require an active transaction; otherwise, MySQL returns an error.

Which command removes a savepoint named 'sp1'?

A RELEASE SAVEPOINT sp1;

B DELETE SAVEPOINT sp1;

C DROP SAVEPOINT sp1;

D REMOVE SAVEPOINT sp1;

The correct command to remove a savepoint is RELEASE SAVEPOINT savepoint_name;.

After rolling back to a savepoint, what is the state of the transaction?

A Transaction is committed

B Transaction is paused

C Transaction is aborted

D Transaction remains active

Rolling back to a savepoint keeps the transaction active for further operations.

Explain what a savepoint is and how it helps in managing transactions.

Think about how you can undo part of your work without losing everything.

Describe the steps and commands to create, rollback to, and release a savepoint in MySQL.

Remember the order: create, rollback, release.

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