IMPLICIT transaction & BEGIN TRANSACTION in Sql Server

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:

Scenario 1

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.

Implicit_Tran1

   

Scenario 2:

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.

Implicit_Tran2

Hope you like this post folks.

 

Regards,

Kapil Singh Kumawat

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook 

Follow me on Twitter

 

   

About Kapil Singh Kumawat

Kapil Singh Kumawat has been working with SQL Server since last 5 years. He is from Jaipur, Rajasthan, India and currently working with Cognizant Technology Solutions as SQL Server Developer. He has good experience in performance tuning, SSIS, data migration and data designing. Apart from database he has interest in travelling, watching football and listening music.

View all posts by Kapil Singh Kumawat →

3 Comments on “IMPLICIT transaction & BEGIN TRANSACTION in Sql Server”

  1. Really useful.. Good topic with detailed example.Please keep post in sequence for more learning with more examples..

  2. 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.

Leave a Reply

Your email address will not be published.