Although my day job has lately been filled primarily with MySQL and PHP development – which I absolutely enjoy – I still make spare time for MySQL and Python hacking; particularly in the MySQL Shell. For whatever reason, I am drawn back to this specific environment. SAVEPOINT
, COMMIT
, and START TRANSACTION
are integral commands for working within a database transaction. MySQL Shell fully supports these commands. We can even execute them in Python mode! Continue reading to see them in action…

OS, Software, and DB used:
- OpenSuse Leap 15.1
- MySQL 8.0.19
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
MySQL Shell Session Information
We need some session information for working in the shell with TRANSACTION
‘s. The following transaction methods are all part of the Session class that is utilized in the example queries that follow:
start_transaction()
commit()
rollback()
rollback_to()
– Requires a string argument of a named savepoint.release_savepoint()
– Requires a string argument of a named savepoint to release.set_savepoint()
– Requires a string argument of a savepoint to initiate.
I’ll store session connection information for easier processing in a variable, ‘my_sess’:
1 | MySQL localhost:33060+ ssl learning Py > my_sess = db.get_session() |
I am working in the ‘learning’ schema (or database), having 3 tables:
1 2 3 4 5 6 7 8 9 | MySQL localhost:33060+ ssl learning Py > my_sess.get_current_schema() <schema:learning> MySQL localhost:33060+ ssl learning Py > db.get_tables() [ <table:file_names>, <table:friends>, <table:name_dates> ] |
The target table for these examples is a fictitious ‘friends’ table with this present data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | MySQL localhost:33060+ ssl learning Py > friend = db.get_table('friends') MySQL localhost:33060+ ssl learning Py > friend.select() +---------+------------+-----------+-------+--------------+------------+-----+ | country | first_name | last_name | state | phone_num | birthday | age | +---------+------------+-----------+-------+--------------+------------+-----+ | USA | Max | Maxer | K | 398-392-5656 | 1975-01-23 | 44 | | CAN | Mary | Murphy | K | 212-543-9420 | 1978-03-23 | 41 | | USA | Charlie | Charles | K | 888-767-2323 | 1971-08-22 | 48 | | USA | Humpty | Dumpty | K | 118-257-7344 | 1971-11-22 | 48 | | USA | Roger | Dodger | K | 234-767-3983 | 1975-08-22 | 44 | | USA | Jim | Russ | K | 424-060-3875 | 1975-05-05 | 44 | | USA | Jupyter | Moonbeam | M | 198-654-2827 | 1978-07-22 | 41 | +---------+------------+-----------+-------+--------------+------------+-----+ 7 rows in set (0.1207 sec) |
I want to redo that…
Just like you execute START TRANSACTION;
in a regular MySQL command-line session, you use the same-named start_transaction()
method in Py
mode, calling it on the (current) session. Below I use the ‘my_sess’ object variable to initiate a TRANSACTION
:
1 2 | MySQL localhost:33060+ ssl learning Py > my_sess.start_transaction() Query OK, 0 rows affected (0.0005 sec) |
Next, I execute an UPDATE
on the ‘state’ column row value(s):
1 2 3 4 | MySQL localhost:33060+ ssl learning Py > friend.update().set('state','L').execute() Query OK, 7 items affected (0.0011 sec) Rows matched: 7 Changed: 7 Warnings: 0 |
(Tip: Read, MySQL Shell CRUD With Python: Update – with examples, to learn more about this staple CRUD operation in MySQL Shell Python mode.)
There was no WHERE
clause present in the previous statement. Therefore, all ‘state’ column row values have been modified, shown in this select()
method call result set:
1 2 3 4 5 6 7 8 9 10 11 12 13 | MySQL localhost:33060+ ssl learning Py > friend.select() +---------+------------+-----------+-------+--------------+------------+-----+ | country | first_name | last_name | state | phone_num | birthday | age | +---------+------------+-----------+-------+--------------+------------+-----+ | USA | Max | Maxer | L | 398-392-5656 | 1975-01-23 | 44 | | CAN | Mary | Murphy | L | 212-543-9420 | 1978-03-23 | 41 | | USA | Charlie | Charles | L | 888-767-2323 | 1971-08-22 | 48 | | USA | Humpty | Dumpty | L | 118-257-7344 | 1971-11-22 | 48 | | USA | Roger | Dodger | L | 234-767-3983 | 1975-08-22 | 44 | | USA | Jim | Russ | L | 424-060-3875 | 1975-05-05 | 44 | | USA | Jupyter | Moonbeam | L | 198-654-2827 | 1978-07-22 | 41 | +---------+------------+-----------+-------+--------------+------------+-----+ 7 rows in set (0.0007 sec) |
Uh-oh. What if I meant to actually only target rows with an ‘age’ column value of 41? Oops…
But, I am in luck (this time at least)!. Since I am working within a TRANSACTION
, I can undo those changes by calling the rollback()
method:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | MySQL localhost:33060+ ssl learning Py > my_sess.rollback() Query OK, 0 rows affected (0.4368 sec) MySQL localhost:33060+ ssl learning Py > friend.select() +---------+------------+-----------+-------+--------------+------------+-----+ | country | first_name | last_name | state | phone_num | birthday | age | +---------+------------+-----------+-------+--------------+------------+-----+ | USA | Max | Maxer | K | 398-392-5656 | 1975-01-23 | 44 | | CAN | Mary | Murphy | K | 212-543-9420 | 1978-03-23 | 41 | | USA | Charlie | Charles | K | 888-767-2323 | 1971-08-22 | 48 | | USA | Humpty | Dumpty | K | 118-257-7344 | 1971-11-22 | 48 | | USA | Roger | Dodger | K | 234-767-3983 | 1975-08-22 | 44 | | USA | Jim | Russ | K | 424-060-3875 | 1975-05-05 | 44 | | USA | Jupyter | Moonbeam | M | 198-654-2827 | 1978-07-22 | 41 | +---------+------------+-----------+-------+--------------+------------+-----+ 7 rows in set (0.0008 sec) |
Since no commit()
was issued in the TRANSACTION
, calling rollback()
restored the ‘state’ column row values back to ‘K’ – their previous value.
Go ahead and store that…
Suppose as you perform work in the shell, you make changes to data that you wish to keep. But, you are working within a TRANSACTION
. What do you do?
Call the commit()
method:
1 2 3 4 5 6 | MySQL localhost:33060+ ssl learning Py > my_sess.start_transaction() Query OK, 0 rows affected (0.0004 sec) MySQL localhost:33060+ ssl learning Py > friend.update().set('state','L').execute() Query OK, 7 items affected (0.0010 sec) Rows matched: 7 Changed: 7 Warnings: 0 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | MySQL localhost:33060+ ssl learning Py > my_sess.commit() Query OK, 0 rows affected (0.1860 sec) MySQL localhost:33060+ ssl learning Py > friend.select() +---------+------------+-----------+-------+--------------+------------+-----+ | country | first_name | last_name | state | phone_num | birthday | age | +---------+------------+-----------+-------+--------------+------------+-----+ | USA | Max | Maxer | L | 398-392-5656 | 1975-01-23 | 44 | | CAN | Mary | Murphy | L | 212-543-9420 | 1978-03-23 | 41 | | USA | Charlie | Charles | L | 888-767-2323 | 1971-08-22 | 48 | | USA | Humpty | Dumpty | L | 118-257-7344 | 1971-11-22 | 48 | | USA | Roger | Dodger | L | 234-767-3983 | 1975-08-22 | 44 | | USA | Jim | Russ | L | 424-060-3875 | 1975-05-05 | 44 | | USA | Jupyter | Moonbeam | L | 198-654-2827 | 1978-07-22 | 41 | +---------+------------+-----------+-------+--------------+------------+-----+ 7 rows in set (0.0008 sec) |
Be mindful that once commit()
is executed, there is no undo. Those changes are now written to disk, for better or worse.
Named marker…
A SAVEPOINT
is a named point in time, so to speak, that you can restore any changes back to while you work in a TRANSACTION
. However, you must create a SAVEPOINT
. SAVEPOINT
‘s are not available unless you establish one.
How do you create a SAVEPOINT
in MySQL Shell? With the set_savepoint()
method. Unlike several of the other Session class methods we have seen so far, you must provide a string argument for the SAVEPOINT
‘s name when executing set_savepoint()
.
I’ll make another modification to some data in the ‘friends’ table:
1 2 3 4 5 6 | MySQL localhost:33060+ ssl learning Py > my_sess.start_transaction() Query OK, 0 rows affected (0.0005 sec) MySQL localhost:33060+ ssl learning Py > friend.update().set('state','P').execute() Query OK, 7 items affected (0.0010 sec) Rows matched: 7 Changed: 7 Warnings: 0 |
I am content with that UPDATE
and want to store those changes to disk. Yet, I still have more work to do. By creating the below ‘my_save’ SAVEPOINT
, if I make other edits and need to revert the changes, I can basically restore the data to its state, at the named ‘my_save’ SAVEPOINT
:
1 2 | MySQL localhost:33060+ ssl learning Py > my_sess.set_savepoint('my_save') my_save |
1 2 3 4 | MySQL localhost:33060+ ssl learning Py > friend.update().set('state','A').execute() Query OK, 7 items affected (0.0005 sec) Rows matched: 7 Changed: 7 Warnings: 0 |
1 2 3 4 5 6 7 8 9 10 11 12 13 | MySQL localhost:33060+ ssl learning Py > friend.select() +---------+------------+-----------+-------+--------------+------------+-----+ | country | first_name | last_name | state | phone_num | birthday | age | +---------+------------+-----------+-------+--------------+------------+-----+ | USA | Max | Maxer | A | 398-392-5656 | 1975-01-23 | 44 | | CAN | Mary | Murphy | A | 212-543-9420 | 1978-03-23 | 41 | | USA | Charlie | Charles | A | 888-767-2323 | 1971-08-22 | 48 | | USA | Humpty | Dumpty | A | 118-257-7344 | 1971-11-22 | 48 | | USA | Roger | Dodger | A | 234-767-3983 | 1975-08-22 | 44 | | USA | Jim | Russ | A | 424-060-3875 | 1975-05-05 | 44 | | USA | Jupyter | Moonbeam | A | 198-654-2827 | 1978-07-22 | 41 | +---------+------------+-----------+-------+--------------+------------+-----+ 7 rows in set (0.0009 sec) |
Recall in the previous update()
method call, all row ‘state’ column values were set to ‘A’. Calling the rollback_to()
method, all those values have been reset to ‘P’ (the column values at the ‘my_save’ SAVEPOINT
state):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | MySQL localhost:33060+ ssl learning Py > my_sess.rollback_to('my_save') MySQL localhost:33060+ ssl learning Py > friend.select() +---------+------------+-----------+-------+--------------+------------+-----+ | country | first_name | last_name | state | phone_num | birthday | age | +---------+------------+-----------+-------+--------------+------------+-----+ | USA | Max | Maxer | P | 398-392-5656 | 1975-01-23 | 44 | | CAN | Mary | Murphy | P | 212-543-9420 | 1978-03-23 | 41 | | USA | Charlie | Charles | P | 888-767-2323 | 1971-08-22 | 48 | | USA | Humpty | Dumpty | P | 118-257-7344 | 1971-11-22 | 48 | | USA | Roger | Dodger | P | 234-767-3983 | 1975-08-22 | 44 | | USA | Jim | Russ | P | 424-060-3875 | 1975-05-05 | 44 | | USA | Jupyter | Moonbeam | P | 198-654-2827 | 1978-07-22 | 41 | +---------+------------+-----------+-------+--------------+------------+-----+ 7 rows in set (0.0008 sec) |
Finishing up, we release (or remove) the SAVEPOINT
and free up any resources, by calling the release_savepoint()
method:
1 | MySQL localhost:33060+ ssl learning Py > my_sess.release_savepoint('my_save') |
Recommended Reading
Visit the listed resources below for more information and insight into SAVEPOINT
‘s and transactions in MySQL:
- Provided by yours truly – SAVEPOINT in MySQL – with examples
- From the official documentation – 13.3.4 SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Statements
- MySQL Transaction
Bonus: I highly recommend the book, Introducing MySQL Shell: Administration Made Easy with Python by Charles Bell (You can find it on Apress or Amazon). The book is simply fantastic and brings you up to speed on the MySQL Shell in general, but with more of a focus on Python. I continue to learn so much from this book. Pick it up!
I have said before but it is worth repeating: MySQL Shell in Python mode is a fantastic environment to work in with your database data. Give it a try!
Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!
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.