Transactions are of various kinds in SQL Server such as Autocommit, Implicit, Explicit and Batch-scoped. I personally found this article quite helpful if you wish to understand Implicit Transactions.
When we talk about transaction, three statements BEGIN TRANSACTION, COMMIT TRANSACTION and ROLLBACK TRANSACTION immediately comes to our mind. You must be aware of these. If not then you can read more about it here.
The purpose of this article is not to talk about the transactions. Instead, to shed lights on some lesser-known facts about the transactions in SQL Server, as the topic name itself suggests.
An explicit transaction involves three stages as follows:
- Starting a transaction: This is the first stage of an explicit transaction. A transaction is started with BEGIN TRANSACTION (or, BEGIN TRAN) statement.
- Saving a transaction (or, setting a savepoint within a transaction): This is an optional, yet an important stage. SAVE TRANSACTION statement is used for setting a savepoint within a transaction. There is no way to manage nested transactions appropriately without SAVE TRANSACTION. It helps to set a savepoint within a transaction, that can be roll backed later (if required), using the same savepoint. You should have an active transaction in order to set a savepoint using SAVE TRANSACTION statement.
- Completing a transaction: A transaction is completed either with a commit, or rollback. Commit can be done using COMMIT TRANSACTION (or, COMMIT TRAN, or simply COMIT). Rollback can be done using ROLLBACK TRANSACTION (or, ROLLBACK TRAN, or simply ROLLBACK). But if a specific savepoint is to be roll backed then it has to be assigned along with the ROLLBACK command. For example ROLLBACK TRANSACTION <savepoint>.
Let us explore more of explicit transaction in the form of Q&A.
What commit really does?
Commit decrements the @@TRANCOUNT by 1, each time its executed. @@TRANCOUNT returns the count of active transactions. If the @@TRANCOUNT is more than 1, then the actual impact will happen when @@TRANCOUNT would become 1. It means the actual impact of commit will take place for the topmost transaction. Actual impact means – the changes becomes the permanent part of the database, and resources acquired (including locks) are released.
Lets see the following example.
BEGIN TRANSACTION ParentTran;
SELECT * FROM sys.dm_tran_active_transactions WHERE name = 'ParentTran';
SELECT @@TRANCOUNT AS [ParentTran_Begin_TranCount];
BEGIN TRANSACTION ChildTran;
SELECT * FROM sys.dm_tran_active_transactions WHERE name = 'ChildTran';
SELECT @@TRANCOUNT AS [ChildTran_Begin_TranCount];
COMMIT TRANSACTION ChildTran;
SELECT @@TRANCOUNT AS [ChildTran_Commit_TranCount];
SELECT * FROM sys.dm_tran_active_transactions WHERE name IN ('ParentTran', 'ChildTran');
COMMIT TRANSACTION ParentTran
SELECT @@TRANCOUNT AS [ParentTran_Commit_TranCount];
SELECT * FROM sys.dm_tran_active_transactions WHERE name IN ('ParentTran', 'ChildTran');
Output of the above code would be similar to as can be seen in the image below.
If you’ll notice the output then there is no transaction as such named ‘ChildTran’ in the sys.dm_tran_active_transactions DMV. However the @@TRANCOUNT becomes 2, the moment ‘BEGIN TRANSACTION ChildTran’ statement was executed. With each commit, the @@TRANCOUNT was decremented by 1. Finally in the last commit, the actual impact will take place.
Since we do not have any data modification request in our transaction, hence we can’t actually see the impact. But with the transactions, having data modifications requests using INSERT, UPDATE and DELETE, the actual impact will take place at the last commit. It is to be noted that you can have number of COMMIT TRANSACTION statements, equals to the BEGIN TRANSACTION statements.
Can there be multiple rollback statements, if there are multiple transactions?
No, there can’t be multiple rollback statement. You may have multiple BEGIN TRANSACTION statements, but can’t have multiple ROLLBACK TRANSACTION statements. Rollback statement has to be once. ROLLBACK TRANSACTION rollbacks all the active transaction within the parent transaction in a session (SPID).
Lets see the following example.
BEGIN TRANSACTION ParentTran;
SELECT * FROM sys.dm_tran_active_transactions WHERE name = 'ParentTran';
SELECT @@TRANCOUNT AS [ParentTran_Begin_TranCount];
BEGIN TRANSACTION ChildTran;
SELECT * FROM sys.dm_tran_active_transactions WHERE name = 'ChildTran';
SELECT @@TRANCOUNT AS [ChildTran_Begin_TranCount];
ROLLBACK TRANSACTION ChildTran;
SELECT @@TRANCOUNT AS [ChildTran_Commit_TranCount];
SELECT * FROM sys.dm_tran_active_transactions WHERE name IN ('ParentTran', 'ChildTran');
ROLLBACK TRANSACTION ParentTran
SELECT @@TRANCOUNT AS [ParentTran_Commit_TranCount];
SELECT * FROM sys.dm_tran_active_transactions WHERE name IN ('ParentTran', 'ChildTran');
However, you can have multiple ROLLBACK TRANSACTION statements, if you have used the SAVE TRANSACTION statement to set a savepoint. You can use the ROLLBACK TRANSACTION with the savepoint name to ROLLBACK a specific savepoint.
Output of the above query will look like similar to as shown in the image below.
However, there will be an error too, as can be seen in the image below. The error was thrown at the line number 9, which is “ROLLBACK TRANSACTION ChildTran;” in our code. The error came because there is no such transaction exists with the name ‘ChildTran’, as it can be seen in the sys.dm_tran_active_transactions DMV.
Let us see another example of rollback with savepoint.
DECLARE @Table TABLE
(
[ID] INT
, [Name] VARCHAR(50)
);
BEGIN TRANSACTION ParentTran;
INSERT INTO @Table (ID, Name) VALUES (1, NULL);
SELECT @@TRANCOUNT AS [ParentTran_Begin_TranCount];
SELECT * FROM @Table;
SAVE TRANSACTION ChildTran;
UPDATE @Table SET NAME = 'ChildTran' WHERE ID = 1;
SELECT @@TRANCOUNT AS [ChildTran_Begin_TranCount];
SELECT * FROM @Table;
ROLLBACK TRANSACTION ChildTran;
SELECT @@TRANCOUNT AS [ChildTran_Commit_TranCount];
SELECT * FROM @Table;
ROLLBACK TRANSACTION ParentTran;
SELECT @@TRANCOUNT AS [ParentTran_Commit_TranCount];
SELECT * FROM @Table;
Output of the above query will look like similar to as shown in the image below. If you’ll notice here, the ‘ROLLBACK TRANSACTION ChildTran;’ statement didn’t roll backed anything. Thinking of why?
This is the another important fact of transaction. Table variables and Identity are not reverted with rollback.
Let us try another example. We’ll extend the same earlier example, but instead of a table variable, we’ll use a temporary table. Here we go.
DROP TABLE IF EXISTS #Table;
CREATE TABLE #Table
(
[ID] INT
, [Name] VARCHAR(50)
);
BEGIN TRANSACTION ParentTran;
INSERT INTO #Table (ID, Name) VALUES (1, NULL);
SELECT @@TRANCOUNT AS [ParentTran_Begin_TranCount];
SELECT * FROM #Table;
SAVE TRANSACTION ChildTran;
UPDATE #Table SET NAME = 'ChildTran' WHERE ID = 1;
SELECT @@TRANCOUNT AS [ChildTran_Begin_TranCount];
SELECT * FROM #Table;
ROLLBACK TRANSACTION ChildTran;
SELECT @@TRANCOUNT AS [ChildTran_Commit_TranCount];
SELECT * FROM #Table;
ROLLBACK TRANSACTION ParentTran;
SELECT @@TRANCOUNT AS [ParentTran_Commit_TranCount];
SELECT * FROM #Table;
As can be seen in the image below, rollback worked with savepoint, and it reverted the savepoint too.
Does naming a transaction really matters?
No. It doesn’t really matter. If there are nested transaction, and each of them given a name. Only the first transaction (or, the parent transaction) can be found in the sys.dm_tran_active_transactions DMV with the name specified. Other transactions won’t reflect.
However, naming the transactions make your code readable.
Can we have nested transactions?
Nested transaction is a myth. You can have another transaction within a transaction, but you cannot specifically commit, or rollback it. So what’s the point having a transaction within another transaction?
Instead, you can set a savepoint within an active transaction, which you can rollback (if needed). But note, you cannot commit a specific savepoint. The commit actual takes place when when @@TRANCOUNT becomes 1. So technically the commit takes place when top most transaction is committed.
Can we directly commit, or rollback a transaction?
Yes, we can.
However, it is recommended to make use of @@TRANCOUNT global variable, and XACT_STATE() functions, to validate the number of active transaction, and whether the transaction is committable, before actually committing, or rolling back a transaction.
@@TRANCOUNT returns the count of active transactions, and XACT_STATE() function let us know, if a transaction is capable of committing. These features can save us from the errors that may occur due to unplanned situations, such as there is no active transaction, or the transaction exists, but can’t be committed.