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.
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.
For example, we execute the below update statement and it auto commits data without asking for COMMIT or ROLLBACK statement.
|
1 2 3 4 5 |
UPDATE [SQLShack].[dbo].[SalesData] SET UnitPrice = 15.9 WHERE CustomerID = 10776 AND subtotal = 200; |
Once SQL Server commits a transaction, you cannot run the ROLLBACK statement. Each rollback statement should have an association with the BEGIN Transaction statement.
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.
|
1 2 3 4 5 6 |
SET IMPLICIT_TRANSACTIONS ON UPDATE [SQLShack].[dbo].[SalesData] SET UnitPrice = 15.9 WHERE CustomerID = 10776 AND subtotal = 200; |
It commits a single row, and you get the output – 1 row affected. For my demo, this query uses the SPID 55.
Now, check the locks held by session 55 using the sp_whoisactive stored procedure.
|
1 |
sp_whoisactive @get_locks=1 |
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.
Now, open a new query window and try to select the same records that we updated.
|
1 2 3 4 |
select * from [SQLShack].[dbo].[SalesData] where CustomerID = 10776 AND subtotal = 200; |
We did not commit update transactions yet, so the SELECT statement faces blocking.
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.
|
1 2 3 |
DECLARE @IMPLICIT_TRANSACTIONS VARCHAR(3) = 'OFF'; IF ( (2 & @@OPTIONS) = 2 ) SET @IMPLICIT_TRANSACTIONS = 'ON'; SELECT @IMPLICIT_TRANSACTIONS AS IMPLICIT_TRANSACTIONS; |
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.
|
1 2 3 4 5 6 7 |
BEGIN TRAN UPDATE [SQLShack].[dbo].[SalesData] SET UnitPrice = 15.9 WHERE CustomerID = 10776 AND subtotal = 200; ROLLBACK TRAN |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE @TransactionName VARCHAR(20)= 'Demotran1'; BEGIN TRAN @TransactionName; INSERT INTO Demo VALUES(1), (2); ROLLBACK TRAN @TransactionName; SELECT * FROM demo; DECLARE @TransactionName1 VARCHAR(20)= 'Demotran2'; BEGIN TRAN @TransactionName; INSERT INTO Demo VALUES(1), (2); COMMIT TRAN @TransactionName1; SELECT * FROM demo; |
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.
|
1 |
CREATE TABLE demo(id INT IDENTITY(1, 1),EmpName varchar(50)); |
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
|
1 2 3 4 5 6 7 |
SELECT IDENT_CURRENT('demo') as [IdentityBeforeInsert]; BEGIN TRANSACTION INSERT INTO dbo.demo ( EmpName ) VALUES('Raj'); INSERT INTO dbo.demo ( EmpName ) VALUES('Sonu'); INSERT INTO dbo.demo ( EmpName ) VALUES('Hari'); INSERT INTO dbo.demo ( EmpName ) VALUES('Cheena'); SELECT IDENT_CURRENT('demo') as [IdentityAfterInsert]; |
We can see the first record in the demo table gets identity value 1, and after inserts, the identity value is 4.
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.
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.
|
1 2 3 4 5 6 7 8 |
DECLARE @Demo TABLE ( id INT ); INSERT @Demo ( id ) VALUES( 11 ); SELECT id AS 'Before BEGIN TRAN statement' FROM @Demo; BEGIN TRANSACTION UPDATE @Demo SET id = 20; SELECT id AS 'After BEGIN TRAN statement' FROM @Demo; ROLLBACK TRAN SELECT id AS 'After explicit ROLLBACK TRAN' FROM @Demo; |
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.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT @@TRANCOUNT AS [First trancount]; BEGIN TRANSACTION; SELECT @@TRANCOUNT AS [Second trancount]; INSERT INTO demo(EmpName) VALUES('Hari'); INSERT INTO demo(EmpName) VALUES('Cheena'); COMMIT TRANSACTION; SELECT @@TRANCOUNT AS [Third trancount]; |
Let’s look at another example. Execute the following query and note the value of @@trancount.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Let's look at another example. Execute the following query and note the value of @@trancount. SELECT @@TRANCOUNT AS [First trancount]; BEGIN TRANSACTION; SELECT @@TRANCOUNT AS [Second trancount]; INSERT INTO demo(EmpName) VALUES('Hari'); BEGIN TRANSACTION; SELECT @@TRANCOUNT AS [Third trancount]; INSERT INTO demo(EmpName) VALUES('MOHAN'); COMMIT TRANSACTION; SELECT @@TRANCOUNT AS [Fourth trancount]; ROLLBACK TRANSACTION; SELECT @@TRANCOUNT AS [Fifth trancount]; |
In the below screenshot, note the following points.
- Initially, we do not have a transaction, so @@trancount returns value 0
- The first BEGIN TRANSACTION statement increments the value of @@trancount by 1, so @@trancount returns 1 in the output
- The second BEGIN TRANSACTION increments value of @@trancount y 1, so it returns 2 in the output
- The first commit statement decrements the value of @@trancount y 1, so it returns 1 in the output
- 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
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE PROCEDURE SP_UpdateData @subtotal INT AS BEGIN BEGIN TRY BEGIN TRANSACTION; UPDATE [SQLShack].[dbo].[SalesData] SET UnitPrice = 15.9 WHERE CustomerID = 10776 AND subtotal = @subtotal; IF @@TRANCOUNT > 0 COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; SELECT ERROR_NUMBER() AS ErrorNumber; SELECT ERROR_MESSAGE() AS ErrorMessage; END CATCH; END; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
--Implicit transaction SET NOCOUNT on DECLARE @i int SET @i =1 WHILE (@i < 100000) begin insert into Demo VALUES ('Performance test') set @i=@i+1 end --Explicit transaction SET NOCOUNT on DECLARE @i int SET @i =1 BEGIN tran WHILE (@i < 100000) begin insert into Demo VALUES ('Performance test') set @i=@i+1 end commit tran |
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.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023