SET IMPLICIT_TRANSACTIONS (Transact-SQL)
Sets implicit transaction mode for the connection.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
SET IMPLICIT_TRANSACTIONS { ON | OFF }
Remarks
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit transaction mode. When OFF, it returns the connection to autocommit transaction mode. Implicit transactions may unexpectedly be on due to ANSI defaults. When IMPLICIT_TRANSACTIONS = ON an explicit BEGIN TRANSACTION will start two nested transactions.
When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:
ALTER TABLE |
FETCH |
REVOKE |
BEGIN TRANSACTION |
GRANT |
SELECT (See exception below.) |
CREATE |
INSERT |
TRUNCATE TABLE |
DELETE |
OPEN |
UPDATE |
DROP |
If the connection is already in an open transaction, the statements do not start a new transaction.
Transactions that are automatically opened as the result of this setting being ON must be explicitly committed or rolled back by the user at the end of the transaction. Otherwise, the transaction and all of the data changes it contains are rolled back when the user disconnects. After a transaction is committed, executing one of the statements above starts a new transaction.
Implicit transaction mode remains in effect until the connection executes a SET IMPLICIT_TRANSACTIONS OFF statement, which returns the connection to autocommit mode. In autocommit mode, all individual statements are committed if they complete successfully.
The SQL Server Native Client OLE DB Provider for SQL Server and the SQL Server Native Client ODBC driver automatically set IMPLICIT_TRANSACTIONS to OFF when connecting. SET IMPLICIT_TRANSACTIONS defaults to OFF for connections with the SQLClient managed provider, and for SOAP requests received through HTTP endpoints.
When SET ANSI_DEFAULTS is ON, SET IMPLICIT_TRANSACTIONS is ON.
Executing a BEGIN TRANSACTION statement when SET IMPLICIT_TRANSACTIONS is ON causes two nested transactions to open. BEGIN_TRANSACTION increments @@trancount whenever a transaction is active.
The setting of SET IMPLICIT_TRANSACTIONS is set at execute or run time and not at parse time.
SELECT statements that do not select from a table do not start implicit transactions. For example SELECT GETDATE(); or SELECT 1, 'ABC'; do not require transactions.
To view the current setting for IMPLICIT_TRANSACTIONS, run the following query.
DECLARE @IMPLICIT_TRANSACTIONS VARCHAR(3) = 'OFF';
IF ( (2 & @@OPTIONS) = 2 ) SET @IMPLICIT_TRANSACTIONS = 'ON';
SELECT @IMPLICIT_TRANSACTIONS AS IMPLICIT_TRANSACTIONS;
Examples
The following example demonstrates transactions that are started explicitly and implicitly with the IMPLICIT_TRANSACTIONS set ON. It uses the @@TRANCOUNT function to demonstrate open and closed transactions.
SET NOCOUNT ON;
GO
SET IMPLICIT_TRANSACTIONS OFF;
GO
PRINT N'Tran count at start = '
+ CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
IF OBJECT_ID(N'dbo.t1',N'U') IS NOT NULL
DROP TABLE dbo.t1;
GO
CREATE table dbo.t1 (a int);
GO
INSERT INTO dbo.t1 VALUES (1);
GO
PRINT N'Use explicit transaction.';
BEGIN TRANSACTION;
GO
INSERT INTO dbo.t1 VALUES (2);
GO
PRINT N'Tran count in explicit transaction = '
+ CAST(@@TRANCOUNT AS NVARCHAR(10));
COMMIT TRANSACTION;
GO
PRINT N'Tran count after explicit transaction = '
+ CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
PRINT N'Setting IMPLICIT_TRANSACTIONS ON.';
GO
SET IMPLICIT_TRANSACTIONS ON;
GO
PRINT N'Use implicit transactions.';
GO
-- No BEGIN TRAN needed here.
INSERT INTO dbo.t1 VALUES (4);
GO
PRINT N'Tran count in implicit transaction = '
+ CAST(@@TRANCOUNT AS NVARCHAR(10));
COMMIT TRANSACTION;
PRINT N'Tran count after implicit transaction = '
+ CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
PRINT N'Nest an explicit transaction with IMPLICIT_TRANSACTIONS ON.';
GO
PRINT N'Tran count before nested explicit transaction = '
+ CAST(@@TRANCOUNT AS NVARCHAR(10));
BEGIN TRANSACTION;
PRINT N'Tran count after nested BEGIN TRAN in implicit transaction = '
+ CAST(@@TRANCOUNT AS NVARCHAR(10));
INSERT INTO dbo.t1 VALUES (5);
COMMIT TRANSACTION;
PRINT N'Tran count after nested explicit transaction = '
+ CAST(@@TRANCOUNT AS NVARCHAR(10));
GO
-- Commit outstanding transaction.
COMMIT TRANSACTION;
GO
Here is the result set.
Tran count at start = 2
Use explicit transaction.
Tran count in explicit transaction = 3
Tran count after explicit transaction = 2
Setting IMPLICIT_TRANSACTIONS ON.
Use implicit transactions.
Tran count in implicit transaction = 2
Tran count after implicit transaction = 1
Nest an explicit transaction with IMPLICIT_TRANSACTIONS ON.
Tran count before nested explicit transaction = 1
Tran count after nested BEGIN TRAN in implicit transaction = 2
Tran count after nested explicit transaction = 1
See Also
Reference
BEGIN TRANSACTION (Transact-SQL)