SQL — Learn how to roll back queries in a transaction

Transactions are responsible for guaranteeing data validity despite errors and they are essential tools in your SQL-toolbox. All queries within a transaction either succeed or all fail; if the last one fails then the queries before get rolled back (gets undone). In this article you’ll learn how to use a transaction but first we’ll get into when to use a transaction:

0. When to use transactions

In some cases you need to perform two operations in your database that cannot be done at once (like with a MERGE INTO, UPDATE INTO, or DELETE INTO) but they still depend on each other. If either of them fails, none of them should succeed. Using transactions in SQL allow you to do just that; roll back previous transactions if one fails.

1. Setup

Lets illustrate this with an example. In our pizza company we have a PizzaMenu table and a PizzaPrices table. Each week we receive files for either table. Our goal is simple; we have to insert the weekly files into the table. The challenge is that we only to have pizza’s on the menu if there is a price in the database. Let’s create the tables:

2. Solving it the wrong way

Doing it the wrong way is to just insert data into both tables:

You’ll notice the error in the last record; we insert a string where only floats are allowed. If we execute the code above we’ll notice that the first insert succeeded and the second one failed. This is not what we want because we now have pizza’s on our menu that we don’t have prices for.

3. Transactions to the rescue!

We’ll get what we want when we use transactions and a try-catch-block. Let’s check out the code and then explain.

The code above starts with BEGIN TRAN. This starts a transaction. Transactions have two possible ways be executed: commit and rollback. Every command that is executed between BEGIN TRAN until either a COMMIT TRAN or a ROLLBACK TRAN is “remembered”. A commit confirms all commands that have been remembered. A rollback undoes all of the commands.

In our code above we wrap our insert statements in a try. If either of them fails we end up in the CATCH block. If we end up there we roll back all transactions. ✅ If we execute the code above both tables remain empty; neither of the inserts succeeds.

4. Throwing in a transaction

You’re code doesn’t have to fail to make use of the rollback in the transaction; you can decide for yourself:

In the code above we insert data into the PizzaMenu and the PizzaPrices. Then we check whether we have prices for all of the pizza’s on the menu. If this is not the case than we throw an error, triggering the ROLLBACK. Else we commit our transaction. Executing the code above will not insert any data because we’ve commented out line 18.

5. Keep your tables speedy

As soon as you begin a transaction the table you perform the operations on gets locked. Locked tables cannot be edited so in order to keep tables speedy it’s very important that once a transaction is started it should commit or rollback as soon as possible.

Conclusion

Transactions are a great new tool in your toolbelt just like these:

• DELETE INTO another table
• UPDATE INTO another table
• Insert, delete and update in ONE statement
• UPDATE SELECT a batch of records
• Version control your database

Happy coding!

— Mike

P.S: like what I’m doing? Follow me!