Uncategorized

Effect of @@trancount with transactions 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

Follow me on Twitter

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s