Rajendra Gupta
ACID properties and transaction states.

How to rollback using explicit SQL Server transactions

June 17, 2020 by

In this article, we will explore the process of rollback an explicit SQL Server transaction. We will also explore the difference between explicit and implicit transactions.

Introduction

A transaction in SQL Server is a single unit of work in a database. We perform many transactions daily. In the real world, consider a banking transaction. Suppose you withdraw money from your bank account, you expect that it should get successful once you get money in your account. In case, you try to withdraw money, but after you are done with all formalities, due to a technical error, the amount got deducted from your account, but it did not reach out to you. In this case, the transaction should be rolled back, and the amount should reappear in your account.

Similar to the above scenario, consider you are executing a script on the production database that updates data in the existing table. Your developer missed putting a WHERE clause and it might mess your data. In this article, we try to find answers to the following questions.

  • Do we have an option to rollback changes in SQL Server?
  • What precautions can we take before executing queries in a production environment?

Before we move further, you can refer to SQL Server Transaction Overview article, and go through ACID properties and transaction states.

ACID properties and transaction states.

Implicit and Explicit SQL Server transaction

Implicit SQL Server transaction:

SQL Server default behavior is Implicit transaction. It provides auto commits functionality, so you do not require to issue a COMMIT TRAN statement. It is a convenient solution, and we can avoid open transaction issues such as session holding resources, but it is not committed.

To view SQL Server default property, connect to a SQL instance in SSMS. Go to Tools-> Options -> search for keyword Query.

In the filtered menu, click on ANSI, and you see option SET IMPLICIT_TRANSACTION mode is off.

SET IMPLICIT_TRANSACTION

For example, we execute the below update statement and it auto commits data without asking for COMMIT or ROLLBACK statement.

Once SQL Server commits a transaction, you cannot run the ROLLBACK statement. Each rollback statement should have an association with the BEGIN Transaction statement.

Error due to no BEGIN TRANSACTION

Let’s go back to the SSMS option and in a new query window, use the option SET IMPLICIT_TRANSACTION ON before starting a transaction.

It commits a single row, and you get the output – 1 row affected. For my demo, this query uses the SPID 55.

IMPLICIT TRANSACTION

Now, check the locks held by session 55 using the sp_whoisactive stored procedure.

sp_whoisactive

In the output above, we see one open transaction for the SPID 55. Click on the hyperlink for locks, and you get currently held locks in an XML format.

Check lock XML details

Now, open a new query window and try to select the same records that we updated.

We did not commit update transactions yet, so the SELECT statement faces blocking.

Blocking SPID

Go back to the update session and commit the records to clear blocking. If we specify SET IMPLICIT_TRANSACTIONS ON in a transaction, SQL Server automatically starts a transaction for you and waits for your instructions to commit or rollback data.

You can check the status of an implicit transaction for a query session, using the below query.

Implicit transaction

Explicit SQL Server transaction

In this mode, each code block starts with a BEGIN TRANSACTION statement, and it requires an explicit COMMIT or ROLLBACK statement. It gives the flexibility to decide whether you want to save changes performed by query or not.

  • BEGIN TRANSACTION – It indicates the starting point of a transaction
  • ROLLBACK TRANSACTION -It starts the rollback process and reverts any changes performed by the transaction. It might take a long time depending upon the changes performed earlier
  • COMMIT TRANSACTION -It commits the changes in the database. Once we issued a commit transaction, it cannot be rolled back

We can achieve the transaction control using the explicit transactions similar to specifying SET IMPLICIT_TRANSACTIONS ON.

In the below query, we specify BEGIN TRAN, in the beginning, to specify an explicit transaction and roll back the updates.

We can define a name for the transaction as well as using explicit transactions. It helps us to commit or rollback a specific transaction when we have multiple transactions in a query.

In the below query, we have two transactions Demotran1 and Demotran2. We rollback the first transaction but commits the second transaction.

Explicit SQL Server transaction

Let’s explore a few useful examples for the explicit transactions.

IDENTITY columns behavior for explicit rollback

Suppose you started an explicit transaction that inserts a record in the demo table. Later, we want to rollback. This table has an identity column as well.

What would be the impact on identity column if we roll back a SQL Server transaction? Let’s explore it using an example. First, create the demo table with two columns [id] and [EmpName]. ID is an identity column for this table.

In the below query, we do the following tasks.

  • Check current identity value using the IDENT_CURRENT() function
  • It begins an explicit transaction using the BEGIN TRANSACTION statement
  • Inserts few records
  • Check the identity after inserts

We can see the first record in the demo table gets identity value 1, and after inserts, the identity value is 4.

IDENTITY columns

Let’s rollback this transaction and check the identity value. Once the rollback completes, SQL Server removes the data rows, but still, identity value is set to 4. If we insert a new row in the demo table, it gets the next identity value 5.

Rollback transaction

This demonstration shows that an explicit transaction rollbacks a transaction, but it cannot revert the identity value. It is the reason we see gaps in the identity value in a SQL table. Its value depends on the last identity value for that table. You can use the RESEED function to reset identity values in a table.

SQL Table variables and explicit SQL Server transaction

We use table variables as a particular table data type that you can use to perform intermediate tables similar to temporary tables. You can refer to An overview of the SQL table variable article, to know more about it.

In the below example, we do the following tasks.

  • Declare a table variable @Demo
  • Insert a record into it
  • Starts an explicit transaction using BEGIN TRANSACTION
  • Update the record in the table variable
  • Rollback transaction
  • Check the value of the record in the table variable.

Table variables

In the above screenshot, we can note that SQL Server does not rollback value held in a table variable using the explicit transaction. You need to consider this before using the table variable and rollback using an explicit transaction.

@@Trancount to keep track of open SQL Server transaction

We can use the global variable @@trancount to track the open transactions.

  • A BEGIN TRANSACTION increments the value for @@Trancount by 1
  • A COMMIT TRANSACTION decrements the value for @@Trancount by 1
  • @@Trancount value zero shows no outstanding transactions in the current session
  • A non-zero value for @@Trancount shows that data from the first BEGIN TRANSACTION is not committed yet
  • A ROLLBACK TRANSACTION command sets the value for @@Trancount to zero

In the below query, you can see how the value changes for the @@Trancount as per BEGIN TRANSACTION and COMMIT TRANSACTION statements for explicit SQL Server transactions.

@@Trancount global variable

Let’s look at another example. Execute the following query and note the value of @@trancount.

In the below screenshot, note the following points.

  1. Initially, we do not have a transaction, so @@trancount returns value 0
  2. The first BEGIN TRANSACTION statement increments the value of @@trancount by 1, so @@trancount returns 1 in the output
  3. The second BEGIN TRANSACTION increments value of @@trancount y 1, so it returns 2 in the output
  4. The first commit statement decrements the value of @@trancount y 1, so it returns 1 in the output
  5. In the end, we have a ROLLBACK TRANSACTION that resets the value of@@trancount to zero, so it rollbacks the entire transaction irrespective of the previous commit transaction

Check status of @@Trancount

We can use the @@trancount in the stored procedure to check the existing open transactions and commit transactions in the value of @@trancount is greater than 1. In the below stored procedure, we make error handling using the TRY CATCH block. It performs COMMIT or ROLLBACK depending upon the value of @@trancount.

Performance testing of implicit and explicit transactions

Let’s perform a quick performance comparison of the implicit and explicit SQL Server transactions. For this demo, we insert records into the demo table with implicit and explicit transactions. Both queries insert the same number of rows in the demo table.

Before running the query, enable the client statistics from the SSMS query-> Enable client statistics option.

First, I run the implicit transaction and later in the same query window, start the explicit transaction.

In the below screenshot, we can see explicit transaction runs faster in comparison with the implicit transaction. You see a difference in the client processing time, total execution time and the wait time on the server replies.

Comparison of implicit and explicit transactions

In the explicit transaction, SQL Server flushes only when the log blocks in memory are full. In the implicit transaction, it does log flush more often, so it requires more time for it to complete.

Conclusion

In this article, we discussed implicit and explicit SQL Server transactions. We can decide to commit or rollback a transaction in explicit mode. You should make use of explicit transaction, especially in the production environment where a minor mistake can lead to a significant issue. You should always have a proper data backup and recovery strategy before making production data changes.

Rajendra Gupta
218 Views