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.