SQL Server ROLLBACK: Everything you need to know

In SQL Server, there are plenty of instances where you might want to undo a statement that was just ran.

Maybe you wrote an UPDATE statement that modified too many rows, or maybe an INSERT statement wasn’t quite right.

In these instances, it is great when the work is performed within a transaction and can therefore be undone.

Do you need to know how the ROLLBACK statement works? Do you need to learn a thing or two about error handling? You’ve come to the right place!

In this tutorial, we’ll teach you everything you need to know about the ROLLBACK statement. We’ll learn a few tricks and tools we can use to help identity problems in a transaction that would trigger a ROLLBACK to occur, so that our data remains consistent.

If you need a rundown on what a transaction is in the first place, check out my introduction to transactions here:

SQL Server Transactions: An Introduction for Beginners

Once you get the hang of transactions, you should also make sure you understand these 6 rules about transactions.

Here are the topics we will cover in this tutorial:

1. How to issue a ROLLBACK in an explicit transaction
2. Understanding @@ROWCOUNT and @@ERROR
3. Can I ROLLBACK a transaction that was already committed?
4. Tips and tricks

Everything in this tutorial is also discussed in the following FREE Ebook:

FREE Ebook on SQL Server Transactions!

This FREE Ebook discusses everything you need to know about creating and using transactions within SQL Server. It will definitely be a great resource for you to keep and reference throughout your career as a data professional. Make sure you download it today!

Let’s roll it over:

1. How to issue a ROLLBACK in an explicit transaction

In the introduction to transactions, we learned how to write an explicit transaction that can be either committed or rolled back. We’ll touch on that topic here, too.

Let’s go ahead and create a table and populate it with data for us to do some ROLLBACK tests with:

CREATE TABLE Books
(
BookID INT PRIMARY KEY IDENTITY,
Title VARCHAR(50),
Author VARCHAR(20),
Pages SMALLINT
)

INSERT INTO Books(Title, Author, Pages)
VALUES 
('As a man thinketh','Allen',45),
('From poverty to power','Allen',56),
('Eat that frog','Tracy',108),
('The war of art','Pressfield',165)

Here’s the data in our table:

So far, so good.

Let’s think about how to wrap a simple UPDATE statement around an explicit transaction. Let’s say I messed up the Pages value for the book “From Poverty to Power“, which is BookID # 2. I meant to give it a Pages value of 156.

If we want to perform an UPDATE statement within a transaction, it’s very easy. First, we put the words BEGIN TRANSACTION before the UPDATE statement, like this:

BEGIN TRANSACTION
UPDATE Books SET Pages = 156 where BookID = 2

You could abbreviate the word “TRANSACTION” and just say “BEGIN TRAN” if you want.

If we wanted to go ahead and COMMIT this transaction, we would just put the word COMMIT after the UPDATE statement, like this:

BEGIN TRANSACTION
UPDATE Books SET Pages = 156 where BookID = 2
COMMIT

You could also be very explicit and say “COMMIT TRANSACTION“, or “COMMIT TRAN“, or even “COMMIT WORK” if you wanted.

But what if you wanted to ROLLBACK?

Instead of committing the transaction, we can undo the UPDATE statement performed in the transaction by using the ROLLBACK keyword, like this:

BEGIN TRANSACTION
UPDATE Books SET Pages = 156 where BookID = 2
ROLLBACK

Similarly, you could be explicit and say “ROLLBACK TRANSACTION“, or just “ROLLBACK TRAN“, or even “ROLLBACK WORK“.

Either way, the effect is the same. The work done inside the transaction will be undone.

Let’s test it out. We’ll run that transaction with the ROLLBACK, then run a quick SELECT statement to see that the data is unchanged:

We see the change was basically undone and we’re back to where we started.

Folks, that’s basically all there is to it. When it comes to an explicit transaction (I.E. a transaction you create using the BEGIN TRANSACTION statement), the way you would undo any work performed inside that transaction is by issuing a ROLLBACK statement after the work.

2. Understanding @@ROWCOUNT and @@ERROR

The example in that last section was a bit silly because we knew we wanted to undo the work.

What if we want to make sure a ROLLBACK occurs if some unknown or unexpected error occurs in our transaction?

This is known as error handling. We want to protect our data in the event that some error occurs that we weren’t aware of.

We can use the extremely handy @@ROWCOUNT or @@ERROR system functions to help us incorporate error handling within our transactions.

Understanding @@ROWCOUNT

The @@ROWCOUNT system function simply tells us the number of rows effected by the last statement.

This can be extremely useful for writing bulletproof transactions. For example, if we want to run an UPDATE statement, and we want to make sure only one row gets updated, we can use the @@ROWCOUNT function to make sure only one row was updated.

Here’s the code, then we’ll discuss it:

BEGIN TRANSACTION
UPDATE Books SET Pages = 156 WHERE BookID = 2
IF (@@ROWCOUNT = 1)
COMMIT
ELSE
ROLLBACK

Remember, @@ROWCOUNT get’s the number of rows affected by the previous statement. The previous statement in our example is, of course, the UPDATE statement.

We want and expect our UPDATE statement to update only one row. But before we issue a COMMIT, we want to make sure only one row was updated.

We use the handy IF…ELSE block to see how many rows were affected by the UPDATE statement. If in fact we only updated one row, we know all is well and we issue the COMMIT.

Otherwise, if @@ROWCOUNT returns a number larger than 1, we know something went wrong and therefore issue a ROLLBACK.

Here’s our code executing the UPDATE statement and the COMMIT successfully:

Let’s see if we can force the ROLLBACK to run. We’ll do another UPDATE statement, but this time leave off the WHERE clause (which you should NEVER do):

Evidently our UPDATE statement modified more than one row, meaning @@ROWCOUNT was greater than 1.  Because we have the error handling in place, the accidental work was successfully rolled back.

All is well.

Understanding @@ERROR

The @@ERROR system function is another great function to use for error handling. We can use this function to see if an error occurred during the last execution of the most recent SQL statement.

This function will return the error number of the error if one occurred. Otherwise, if no error occurred in the previous statement, it returns 0.

The @@ERROR function, combined with an IF…ELSE block, is a great way to roll back the transaction if any bad thing happened.

Let’s take a look at an example. Here’s an example of an UPDATE statement that has a deliberate error in place:

BEGIN TRANSACTION
UPDATE Books SET Pages = 200 WHERE BookID = 4/0
IF (@@ERROR = 0)
COMMIT
ELSE
ROLLBACK

The error is in the WHERE clause, where I’m trying to divide an integer by 0. This is a mathematic violation and will give us an error.

In the IF block, if the value of @@ERROR is 0, we know there was no error generated by the UPDATE statement and it’s safe to COMMIT the transaction.

However, we would enter the ELSE block if an error did occur. In that case, @@ERROR would hold a value greater than 0. In that case, we ROLLBACK the transaction because something went wrong.

Let’s test it out:

We see that the error occurs. Now let’s check the data to see if anything changed:

Nope, looks good. But the most important thing to check is that we’re not in an open transaction. We use the handy @@TRANCOUNT system function for that. This function tells us how many open transactions exist in the current connection. Let’s make sure it’s zero:

Nice.

3. Can I ROLLBACK a transaction that was already committed?

I have a notion that some of you are reading this tutorial in the hope you can learn how to undo something you didn’t mean to do in SQL Server.

Maybe you ran and UPDATE statement and forgot to add a WHERE clause, or maybe the WHERE clause existed but it pulled more rows than you expected.

Or worse, you ran a DELETE statement without specifying a WHERE clause. Yikes!

Ok, here’s how we can check if your work can be undone:

1. Is the connection still open? It would be open if you didn’t close the query window or close SQL Server completely. If you’re still in the same connection, we can proceed to step 2.
2. Check the value of @@TRANCOUNT by running the following query: SELECT @@TRANCOUNT

If @@TRANCOUNT is greater than 0. We’re still in an open transaction. You should be able to just execute a ROLLBACK statement. After running that, if you check @@TRANCOUNT again, it will be 0 and the changes made in error should be undone.

But if @@TRANCOUNT is already 0, I’m afraid to say the changes are already committed to the database.

But fear not. If your database team is worth their sand, they will have prepared for something like this. They ought to have a backup plan for scenarios where bad SQL was executed. They would need to put the database back in a state close to what it was before the bad SQL was executed.

Things happen. It will be okay!

4. Tips and tricks

Here is a list of tips and tricks you should know when working with the ROLLBACK command:

Tip # 1: ROLLBACK will set @@TRANCOUNT to zero, regardless of what it was before.

If you have 35 open transactions, then you issue a single ROLLBACK, guess what, all the work done in all those transactions was just undone.

This is different from COMMIT which will just reduce @@TRANCOUNT by 1 each time it’s ran. So if you wanted to commit all 35 transactions instead, you would need to run COMMIT 35 times.

(By the way, none of the data is actually committed to the database until @@TRANCOUNT is reduced from 1 to 0. Running COMMIT once when there are 35 open transaction literally does nothing except reduce @@TRANCOUNT to 34)

Learn more about this factoid and others in this tutorial:

SQL Server Transactions: Do you understand these 6 rules?

Tip # 2: Before running an UPDATE or DELETE statement, run it as a SELECT statement first

If I’m updating data in a particularly important database, a trick I like to do is simply write my statement as a SELECT statement first.

That SELECT statement will likely have a WHERE clause. I’ll run that SELECT statement and verify the returned rows are the rows I expect. These are, of course, rows I intend to update or delete.

If everything checks out, I’ll use that same WHERE clause in my UPDATE or DELETE statement. That way, I can be confident the rows getting updated or deleted are those same rows that were returned in my SELECT statement.

This tip doesn’t exactly deal with performing a ROLLBACK, but it does deal with writing accurate DML (Data Manipulation Language) statements that hopefully won’t need to be rolled back.

Next Steps:

Leave a comment if you found this tutorial helpful!

Everything discussed in this tutorial can be found in the following FREE Ebook:

FREE Ebook on SQL Server Transactions!

This FREE Ebook discusses everything you need to know about creating and using transactions within SQL Server. It will definitely be a great resource for you to keep and reference throughout your career as a data professional. Make sure you download it today!

If you missed the introduction tutorial on SQL Server transactions, check it out here!

We discuss everything you need to know about transactions, including using ROLLBACK to undo work performed within a transaction.

Also, there are many important factoids about transactions many people don’t know. If you want to be a transactional master, you should read this tutorial too:

SQL Server Transactions: Do you understand these 6 rules?

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, please leave a comment. Or better yet, send me an email!