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.
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
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.
- Deploying a Certificate for Encrypted Connection SQL Server - September 16, 2020
- How to Configure Database Mail in SQL Server - September 14, 2020
- Configuring AlwaysOn Availability Groups on SQL Server - September 3, 2020