SAVEPOINT in MySQL – with examples.

DML or Data Manipulation Commands should be handled with utmost care in SQL for the simple reason they modify the targeted data in some way. Typically, this is via UPDATE or DELETE statements. In this blog post, we will learn how to better protect the data (and ourselves) when running these types of commands by utilizing a SAVEPOINT within a TRANSACTION.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

OS and DB used:

• Xubuntu Linux 16.04.5 LTS (Xenial Xerus)
• MySQL 8.0.13

By default, MySQL automatically commits all statements when they are run. Any changes, through DML commands, are written to disk and therefore, are permanent.

However, you can (temporarily) change this ‘written in stone’ behavior by issuing a START TRANSACTION command. Once enabled, to place MySQL back in autocommit mode you must run either a ROLLBACK or COMMIT command essentially, ending or completing the TRANSACTION.

To make things even better, also available within a TRANSACTION is something called a SAVEPOINT. You just have to use it because it is not there by default.

By creating a custom-named SAVEPOINT any active TRANSACTION can be rolled back via ROLLBACK TO save_point_name syntax to that specific location within the TRANSACTION. I like to envision it as a singular ‘point-in-time’ within the TRANSACTION as a whole.

Any data modifications that have been carried out during the TRANSACTION, after the established SAVEPOINT, are reverted back to their original state (prior to the SAVEPOINT‘s state).

To better understand, let’s visit a simple example and solidify the concept. I have this table and data:

Next, I initialize a TRANSACTION:

Establish a SAVEPOINT named ‘initial_save’:

Then perform an UPDATE on a row in the table:

And see the reflected changes:

Let’s revert that change by issuing a ROLLBACK to the created SAVEPOINT:

And view the undone (original) data for that row:

A super-important passage from the SAVEPOINT documentation is provided below, quoted directly from that source:

“The ROLLBACK TO SAVEPOINT statement rolls back a transaction to the named savepoint without terminating the transaction. Modifications that the current transaction made to rows after the savepoint was set are undone in the rollback, but InnoDB does not release the row locks that were stored in memory after the savepoint. (For a new inserted row, the lock information is carried by the transaction ID stored in the row; the lock is not separately stored in memory. In this case, the row lock is released in the undo.)

“

Finally, to make these changes permanent, we must issue COMMIT:

You can see that since COMMIThas been issued, we are no longer inside a TRANSACTION and SAVEPOINT ‘initial_save’ does not exists:

Now that we are finished, or outside, of the TRANSACTION, the data remains in its original state:

So is this full-proof? Well, not exactly.

Although you can have multiple individual SAVEPOINTS within a single TRANSACTION there are some limitations to what type of commands are ‘allowed or eligible’ for a ROLLBACK. As there are too many to list them all, a couple of the more common commands would be: ALTER TABLE, CREATE VIEW, DROP FUNCTION, etc…

For the full list, visit Section 13.3.3 Statements That Cause an Implicit Commit in the official documentation.

TRANSACTION‘s and SAVEPOINT‘s are great additions for anyone dealing with DML commands and their data so do check them out!

Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!

Explore the official MySQL 8.0 Online Manual for more information.

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.