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.

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.

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