Home > SQL Server > SQL SERVER – Implicit Transactions – SET IMPLICIT_TRANSACTIONS – @SQLSERVER

SQL SERVER – Implicit Transactions – SET IMPLICIT_TRANSACTIONS – @SQLSERVER


Transaction is the section or block of work that user is currently working. User has two choice, the section can be commit (Store permanent) or rollback (erase) the current transaction changes.

SET IMPLICIT_TRANSACTIONS

Implicit transaction is defined as a new transaction that is implicitly started when the prior transaction completes, but each transaction must be explicitly COMMIT or ROLLBACK by the user. otherwise the transactions will be rolledback automatically when the connection is terminated.

when user set the current session with IMPLICIT_TRANSACTIONS ON then SQL Server changes the connection into implicit transaction mode. If we set this set statement OFF then it will set the current environment as autocommit mode (ie, all individual statements are committed if they complete successfully).

The following keywords will start a new transaction automatically in the implicit transaction mode.

pic1Usage

SET IMPLICIT_TRANSACTIONS  ON

or

SET IMPLICIT_TRANSACTIONS OFF

Example

1. when implicit_transactions is off, I am executing the following statement.

use DataLab
 GO
set implicit_transactions off
 GO
Create table Trans
 (
 Stmt varchar(50)
 )
 GO
rollback transaction
 GO
insert into Trans values('SQL SERVER RIDER')
 GO
Select * from Trans
 GO

Result and message

pic2Rollback statement failed because we did not open a transaction in this session. See the result now.

pic3

Table creation and Insert statement executed successfully.

2. when implicit_transactions is on, executing the above SQL statements. Drop the table Trans before execution the SQL statement.

pic4Line 16 and 19 failed because of the object (table) Trans does not exist. Create statement opens a new transaction and we have rolled-back the table creation statement in the next line. So, there is not a table called Trans for Insert and Select statement.

We can also get the count of open transaction using @@TRANCOUNT function. It helps user to get the transaction that are opened in the current session.

I hope you have understood the use of the IMPLICIT_TRANSACTIONS set statement.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: