Using Transaction ROLLBACK in SQL Server

Introduction

Very recently, a colleague of mine came to me in desperation owning up that he had issued an update statement without a WHERE clause on a key application table. The implications on the front end would be dire, so he came to me directly because he urgently needed help with reversing the situation by any means before the emails and escalation started pouring in.

When we looked into the situation, we found that the changes have not been applied in the secondary database. In most cases, the lag between our primary and secondary databases is twenty minutes (we have a little staggering to avoid performance problems). Because my colleague asked for help immediately after realizing the error, we were able to recover the data from the secondary database. I described the value of such a delay in this article.

Scenario Review

The scenario I described above is not uncommon. One of the reasons why this happens to regular SQL Server users is that SQL Server uses what is called Implicit Transactions. Implicit Transactions are turned OFF by default, meaning SQL Server does not expect you to issue a COMMIT TRANSACTION statement at the end of each statement. In effect, each statement is committed automatically. This is convenient and helps avoid situations in which sessions that are yet to be committed end up locking resources and impacting performance. Brent Ozar gives more details on the performance implications of IMPLICIT TRANSACTIONS = ON.

However, a small downside to this configuration (IMPLICIT TRANSACTIONS = OFF) is that users don’t have an opportunity to rethink a statement and issue a ROLLBACK which is very common in Oracle. Fig. 1 shows the ANSI query options available in SQL Server Management Studio.

Fig. 1 ANSI Defaults in SQL Server Management Studio

Using Implicit Transactions

In essence, the problem we are faced with in this default configuration or our most desirable client tool is that we cannot ROLLBACK once we execute an SQL statement. We can circumvent this by enabling IMPLICIT TRANSACTIONS in our session. This will give us the opportunity to ROLLBACK transactions if we need to. Fig. 2 and Fig. 4 show us that we can have this setting turned on only for one session, even though this doesn’t take away the risk of the user session blocking others if a ROLLBACK or COMMIT is not issued.

Fig. 2 IMPLICIT TRANSACTIONS ON in One Session

-- Listing 1: UPDATE Table TAB2 with IMPLICIT_TRANSACTIONS ON
SET IMPLICIT_TRANSACTIONS ON

DECLARE @IMPLICIT_TRANSACTIONS VARCHAR(3) = 'OFF';  
IF ( (2 & @@OPTIONS) = 2 ) SET @IMPLICIT_TRANSACTIONS = 'ON';  
SELECT @IMPLICIT_TRANSACTIONS AS IMPLICIT_TRANSACTIONS;

USE KTrain
GO
SELECT * FROM Tab2;
GO

UPDATE TAB2
SET countryCode='SA'
-- WHERE fname='Joyce';
GO
SELECT * FROM Tab2;
GO

Fig. 3 All Rows Updated

To illustrate the workaround described here, let’s look at the SQL code in Listing 1. Let’s assume that a regular SQL Server user, a junior developer, has been given a set of scripts to execute under certain conditions. In the script, the WHERE clause has been commented out because it is expected that each time they execute this script, they should change the predicate. Of course, this is a simple use case and the risk can be addressed in a number of ways, but we just want to show the possibility of performing a ROLLBACK.

Recall that we have already turned on IMPLICIT TRANSACTION, so when we execute this statement, SQL Server will be expecting us to either COMMIT or ROLLBACK the transaction. The developer’s intention is to update Joyce Afam’s countryCode to ‘SA’ since she has immigrated to South Africa. Fig. 3 shows us that the developer while trying to do this, has accidentally updated all rows with the value SA as countryCode. They notice this and issue a ROLLBACK.

Fig. 4 Issuing ROLLBACK

Fig. 5 IMPLICIT TRANSACTIONS ON in Another Session

However, in the other session in which we have not turned on IMPLICIT TRANSACTIONS, we find that the developer is unable to recover from their error. They cannot successfully issue a ROLLBACK in this case. Recovery would then entail data restoration.

Fig. 6 ROLLBACK not Possible without IMPLICIT TRANSACTIONS ON

Using Explicit Transactions

Another approach to achieve the same effect is to enclose the DML in a transaction by explicitly stating BEGIN TRAN. Again, it is very important to complete the transaction – by using either COMMIT or ROLLBACK. In the context of this discussion, we issue a ROLLBACK since we realize that there is an error in the code.

-- Listing 2: UPDATE Table TAB2 with Explicit Transaction

BEGIN TRAN 
GO
USE KTrain
GO
SELECT * FROM Tab2;
GO

UPDATE TAB2
SET countryCode='GH'
-- WHERE fname='Joyce';
GO
SELECT * FROM Tab2;
GO

ROLLBACK;

SELECT * FROM Tab2;
GO

- Listing 3: Corrected UPDATE Statement

BEGIN TRAN 
GO
USE KTrain
GO
SELECT * FROM Tab2;
GO

UPDATE TAB2
SET countryCode='SA'
WHERE fname='Joyce';
GO
SELECT * FROM Tab2;
GO

Conclusion

In this article, we have touched briefly on a good workaround for creating opportunities for ROLLBACK and thus mitigating user errors resulting from wrong DML. We have also highlighted a key risk of this approach, which is inadvertent blocking. A DBA can begin investigations on the possible presence of this risk by querying sys.dm_tran_session_transactions, sys.dm_tran_locks, and similar dynamic management objects.

References

1. Fixing Data Loss Using Log Shipping with Delayed Recovery

2. Set Implicit Transactions

3. Set Implicit Transactions a Bad Idea

4. DMVs for Transactions