SQL Savepoints and When to Use Them

Not many developers know about savepoints in relational databases. Even less of them know when to use them. It’s not their fault: I can’t remember seeing a good explanation of this feature. Let’s try to clarify this lesser-known functionality.

In this article I’m using MariaDB syntax. But the concepts are very similar for other transactional databases. To know the exact syntax you should use on a particular DBMS, please check its documentation.

Transactions: A Brief Tutorial

As you probably know, in the context of databases, a transaction is a series of instructions that will completely succeed or completely fail. Any data change that happens inside a transaction is only visible to other connections when the transaction succeeds or fails. The transaction succeeds when the COMMIT statement is succefully issued. And it fails when an error occurs (though there can be exceptions, depending on which database you use) or the ROLLBACK command is issued.

The SQL statements of a transaction usually look like this:

START TRANSACTION;
-- read or write some data
COMMIT;

Or:

START TRANSACTION;
-- read or write some data
ROLLBACK;

This can be confusing, at the beginning: why would one run ROLLBACK and make a transaction fail? Shouldn’t it fail automatically when an error occurs?

The reasons are:

• Some errors do not make the transaction fail automatically. While the last statement has failed, the transaction might still be open so that you can retry or ignore the failed operation. The details vary depending on the DBMS you’re using. On some DBMSs, this is the case for syntax errors. But your application “knows” that an SQL syntax error can only be caused by a bug, and the only safe thing to do is rolling back.
• You might detect a logical inconsistency. Sure, databases can have constraints that prevent some types of inconsistencies to be introduced in the data, like foreign keys or CHECK constraints. But it’s impossible to prevent all possible inconsistencies. When you read rows you might find an inconsistency and you might want to rollback. For example, you might find that a number is higher than its logical maximum, or a string is unexpectedly empty.
• Cancelling operations. The user might stop the operation by pressing CTRL-C or some button on your website. Or you might read rows, and find some reason why an operation must be cancelled. For example, the products that need to be sold aren’t in stack.

There is much more to say about transactions, but we can dig into this topic in another article. Let’s move on, to savepoints.

Using Savepoints

It’s worth stressing that a transaction must be atomic: it will entirely succeed or entirely fail. Still, it can contain savepoints. When the first part of a transaction succeed but then something failed, you might not want to rollback the entire transaction. Maybe you want to rollback to a savepoint (hopefully the last successful query) and try again the rest. Or maybe, just acknowledge that the rest of the transaction can’t succeed and give it up – there are cases when this makes sense.

Let’s see how it works. using once again MariaDB syntax:

START TRANSACTION;
-- write something successfully
SAVEPOINT orders_updated;
-- write something else successfully
SAVEPOINT inventory_updates;
-- try something that fails
ROLLBACK TO inventory_updates;
-- try again
COMMIT;

In this example we’re rolling back to the latest savepoint, inventory_updates. We can also rollback to previous one, or we can rollback the transaction completely.

When to Use Savepoints in Real Life

Now you know how to use savepoints. But when and why would you do that? It’s not obvious, and I know that it’s hard to find a good explanation.

You should think the instructions after a savepoint as an optional sub-transaction. The subtransaction will:

• Completely succeed or completely rollback.
• Rollback if the main transaction rolls back.
• See the same data as the same transaction. If you use the REPEATABLE READ isolation level, which is the default in MariaDB but not in most other databases.
• Produce changes that become visible to other connections only when the whole global transaction commits.

That said, here are some scenarios where savepoints (sub-transactions) will prove useful.

Optional or Experimental Features

The subtransaction is about an optional or experimental feature of the application. Maybe the user created a web page that is written into the database. Some other information might be written in the same transaction: a tag creation, a category creation, whatever.

Some SEO-related information should also be written. It’s useful to make this happen in the same transaction, so that the final result will become visible altogether. But the SEO-related information is written into two tables. One always exists, but the other only exist if a certain plugin was loaded.

Or maybe it’s still an experimental feature. It usually works, but you know that it may fail under certain circumstances. If the SEO information can’t be entirely written, you don’t want any of it to be written. But you still want the web page to be created.

Retries

Some applications have to do operations that fail relatively often, but they need to retry until successful. The reasons are usually a non-optimal way to handle concurrency, or dependence from an external technology that is not as reliable as it should be (external services or non-optimal microservices).

Some transactions include many statement, or slow statements. If you need to retry them many times, this can easily result in frequent row locks that reduce the application’s scalability.

This can be partly avoided if the transaction starts with some statements that usually succeed. After there “safe” statements, a savepoint should be set. Then the statement is likely to fail (or not produce the expected results) should follow. If more than one statement is likely to fail, you can set a savepoint just before each of them. In this way, in case of a failure (whether it is an error or a logical failure) you can rollback to the last savepoint and only retry the last statement. Your DBAs will thank you.

Releasing Locks

Maybe you’re updating big portions of rows from multiple tables. It is probably a batch operation run by a job. For example, maybe you’re updating the inventory. Occasionally there are Inventory discrepancies sometimes occur between your warehouse and database. They are caused by events that your software doesn’t know about, like damages, incorrect deliveries, thefts, and so on.

Your application receives a JSON file containing inventory data, then it selects the rows in your tables, and then it corrects the differences. This happens in a single transaction. You need to be sure that no one changes the products quantities while you’re performing these reconciliations, so you use SELECT ... FOR UPDATE to acquire exclusive locks on the rows.

But the reconciliation might take a long time, and locking all rows for the whole duration would cause too many problems. So, before reading each table, you should set a savepoint. If the table doesn’t need to be modified, you can rollback to the last savepoint, to allow the database to release locks acquired after the savepoint.

Conclusions

We discussed savepoints, a lesser-known SQL feature. Savepoints can greatly improve the handling of transactions that are likely to fail, especially the longer transactions.

If you have opinions or some experience with savepoints that you’d like to share, please comment! Your comments are valuable to us.

If you’d like to know more about optimising translactions and SQL queries, consider our training courses.

Federico Razzoli