Hi friends, while working a scenario I found some interesting fact about @@TRANCOUNT when we used IMPLICIT transaction & BEGIN TRANSACTION in Sql Server.
I will create some scenarios and we will look how things get changed.
As we know that @@TRANCOUNT function is used to find the level of transaction. When @@TRANCOUNT > 0 it indicates that there is an active transaction.
When Implicit transaction mode is ON, @@TRANCOUNT value increments when we issue any DDL or DML statement or a SELECT statement. We don’t need to specify BEGIN TRAN explicitly to begin transaction in Implicit transaction mode but we must issue COMMIT or ROLLBACK to finish the active transaction.
To work in Implicit transaction mode we need to issue following statement:
SET IMPLICIT_TRANSACTIONS ON;
Let’s make some scenarios now:
SET IMPLICIT_TRANSACTIONS ON PRINT @@TRANCOUNT --------- Value is 0 BEGIN TRANSACTION PRINT @@TRANCOUNT ---------Value is 2 SET IMPLICIT_TRANSACTIONS OFF COMMIT TRANSACTION PRINT @@TRANCOUNT --------- Value is 1
When we start SET IMPLICIT_TRANSACTIONS ON no transaction gets started so value of @@TRANCOUNT will be 0. When we issue BEGIN TRAN statement the value of @@TRANCOUNT changed to 2 because when we execute a BEGIN TRAN when SET IMPLICIT_TRANSACTION is ON it will open two nested transactions.
Now after putting IMPLICIT_TRANSACTIONS to OFF there will be no change in value of @@TRANCOUNT. After issuing COMMIT the value of @@TRANCOUNT decreased to 1 as COMMIT TRANSACTION decrements @@TRANCOUNT only by 1.
BEGIN TRANSACTION PRINT @@TRANCOUNT ------Value is 1 SET IMPLICIT_TRANSACTIONS ON PRINT @@TRANCOUNT ------ Value is 1 COMMIT TRANSACTION PRINT @@TRANCOUNT ------ Value is 0 SET IMPLICIT_TRANSACTIONS OFF
Here, value of first @@TRANCOUNT will 1 as we have issued it after BEGIN TRANSACTION. But value of second @@TRANCOUNT is still 1 after issuing SET IMPLICIT_TRANSACTIONS to ON because this will not affect current transaction and so there will be no change in value of @@TRANCOUNT .
After issuing COMMIT TRANSACTION value of @@TRANCOUNT decreased by 1 and value becomes 0.
Hope you like this post folks.
Kapil Singh Kumawat
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
3 Comments on “IMPLICIT transaction & BEGIN TRANSACTION in Sql Server”
Really useful.. Good topic with detailed example.Please keep post in sequence for more learning with more examples..
Learning never ends.it’s really useful to be productive as SQL designer. Seriously this post helped me a lot to implement the things in my current assignment.
Keep up this spirit Kapil. Really appreciated.