How Implicit Transactions Work in SQL Server

There are four transaction modes in SQL Server. One of these is implicit mode.

In SQL Server, an implicit transaction is when a new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

This is not to be confused with autocommit mode, where the transaction is started and ended implicitly.

The Four Transaction Modes

SQL Server can operate in the following transaction modes:

Transaction mode	Description
Autocommit transaction	Each individual statement is a transaction.
Implicit transaction	A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed, typically with a `COMMIT` or `ROLLBACK` statement depending on the DBMS.
Explicit transaction	Explicitly started with a line such as `START TRANSACTION`, `BEGIN TRANSACTION` or similar, depending on the DBMS, and explicitly committed or rolled back with the relevant statements.
Batch-scoped transaction	Applicable only to multiple active result sets (MARS). An explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction.

Implicit Mode vs Autocommit

In SQL Server, certain statements start a transaction automatically when they run. It’s as if they were preceded by an invisible BEGIN TRANSACTION statement.

In most cases, these transactions are also implicitly committed, as if there was an invisible COMMIT TRANSACTION statement. Such transactions are said to be in autocommit mode.

In other cases, there’s no invisible COMMIT TRANSACTION to match the invisible BEGIN TRANSACTION statement. The transaction remains in progress until you explicitly commit it or roll it back with a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. In this case, the transaction is said to be in implicit mode.

Whether the transaction runs in implicit mode or autocommit mode depends on your IMPLICIT_TRANSACTIONS setting.

Statements that Start an Implicit Transaction

The following statements start an implicit transaction in SQL Server.

ALTER TABLE
BEGIN TRANSACTION
CREATE
DELETE
DROP
FETCH
GRANT
INSERT
OPEN
REVOKE
SELECT (except those that don’t select from a table, such as SELECT GETDATE() or SELECT 1*1)
TRUNCATE TABLE
UPDATE

Any time you run these T-SQL statements, you’re starting a transaction. Most of the time the transaction will be automatically committed. So you started and ended the transaction without having to explicitly do so.

However, depending on your IMPLICIT_TRANSACTIONS setting, you may need to commit the transaction explicitly.

When `IMPLICIT_TRANSACTIONS` is `OFF`

When your IMPLICIT_TRANSACTIONS setting is OFF, the above statements perform transactions in autocommit mode. That is, they start and end the transaction implicitly.

So it’s like having an invisible BEGIN TRANSACTION statement and an invisible COMMIT TRANSACTION statement, all from the one statement.

In this case, you do not need to do anything to commit or rollback the transaction. It was already done for you.

When `IMPLICIT_TRANSACTIONS` is `ON`

When your IMPLICIT_TRANSACTIONS setting is ON, the above statements behave slightly different.

When IMPLICIT_TRANSACTIONS setting is ON, the above statements get an invisible BEGIN TRANSACTION statement but they don’t get a corresponding COMMIT TRANSACTION statement.

This means that you need to explicitly commit or rollback the transaction yourself.

However, when the transaction mode is implicit, no invisible BEGIN TRANSACTION is issued if a transaction is already in progress.

Example

Here’s an example to demonstrate the concept.

SELECT @@TRANCOUNT AS TransactionCount;
SET IMPLICIT_TRANSACTIONS OFF;
SELECT TOP 1 ProductName, ProductPrice FROM Products;
SELECT @@TRANCOUNT AS TransactionCount;

Result:

+--------------------+
| TransactionCount   |
|--------------------|
| 0                  |
+--------------------+
(1 row affected)
Commands completed successfully.
+-------------------------+----------------+
| ProductName             | ProductPrice   |
|-------------------------+----------------|
| Left handed screwdriver | 25.99          |
+-------------------------+----------------+
(1 row affected)
+--------------------+
| TransactionCount   |
|--------------------|
| 0                  |
+--------------------+
(1 row affected)

In this case, I set IMPLICIT_TRANSACTIONS to OFF and run the SELECT statement. This meant that the SELECT statement ran in autocommit mode, and therefore, the transaction was started and ended implicitly.

@@TRANCOUNT returned 0, which means there were no transactions running at that point.

Here’s it is again, except this time we set IMPLICIT_TRANSACTIONS to ON.

SELECT @@TRANCOUNT AS TransactionCount;
SET IMPLICIT_TRANSACTIONS ON;
SELECT TOP 1 ProductName, ProductPrice FROM Products;
SELECT @@TRANCOUNT AS TransactionCount;

Result:

+--------------------+
| TransactionCount   |
|--------------------|
| 0                  |
+--------------------+
(1 row affected)
Commands completed successfully.
+-------------------------+----------------+
| ProductName             | ProductPrice   |
|-------------------------+----------------|
| Left handed screwdriver | 25.99          |
+-------------------------+----------------+
(1 row affected)
+--------------------+
| TransactionCount   |
|--------------------|
| 1                  |
+--------------------+
(1 row affected)

The last @@TRANCOUNT is returning a value of 1. This means that our transaction in still in progress.

@@TRANCOUNT returns the number of BEGIN TRANSACTION statements that have occurred on the current connection. We didn’t explicitly issue one, but one was issued implicitly.

So we actually need to commit this transaction (or roll it back) in order to decrement the @@TRANCOUNT down to 0.

COMMIT TRANSACTION;
SELECT @@TRANCOUNT AS TransactionCount;

Result:

+--------------------+
| TransactionCount   |
|--------------------|
| 0                  |
+--------------------+
(1 row affected)

So the code for our implicit transaction should have included the COMMIT statement:

SELECT @@TRANCOUNT AS TransactionCount;
SET IMPLICIT_TRANSACTIONS ON;
SELECT TOP 1 ProductName, ProductPrice FROM Products;
COMMIT TRANSACTION;
SELECT @@TRANCOUNT AS TransactionCount;

Result:

+--------------------+
| TransactionCount   |
|--------------------|
| 0                  |
+--------------------+
(1 row affected)
Commands completed successfully.
+-------------------------+----------------+
| ProductName             | ProductPrice   |
|-------------------------+----------------|
| Left handed screwdriver | 25.99          |
+-------------------------+----------------+
(1 row affected)
Commands completed successfully.
+--------------------+
| TransactionCount   |
|--------------------|
| 0                  |
+--------------------+
(1 row affected)

`ANSI_DEFAULTS`

If you find that implicit transactions are enabled unexpectedly, it could be because of the ANSI_DEFAULTS setting.

The Four Transaction Modes

Implicit Mode vs Autocommit

Statements that Start an Implicit Transaction

When IMPLICIT_TRANSACTIONS is OFF

When IMPLICIT_TRANSACTIONS is ON

Example

ANSI_DEFAULTS

When `IMPLICIT_TRANSACTIONS` is `OFF`

When `IMPLICIT_TRANSACTIONS` is `ON`

`ANSI_DEFAULTS`