Blog

In SQL (almost) nothing happens unless it is within a transaction. A transaction is a single unit of work where all contained changes are either completed successfully or rolled back. Hence the term transactional consistency. There are three modes for transactions in MSSQL: autocommit, explicit and implicit.

Autocommit

The default for MSSQL is autocommit where every statement is its own transaction that is automatically committed if successful (or rolled back if not). Hence the following statements would be three separate auto committed transactions:

create table t1 (col1 int, col2 nvarchar(10));
insert into t1 values (1, 'abc');
insert into t1 values (2, 'def');

Explicit

The explicit transaction is commonly used to group statements that needs to be transactionally consistent. This means that either none or all of the statements need to be committed or rolled back. A middle ground where one statement fails and another succeeds is not allowed. (There are exceptions when using the “save transaction” statement, that will be discussed later). The following is an example of a single explicit transaction, note that it does the same as the previous example, but now the statements are in one transaction only and are therefore transactionally consistent:

begin transaction
	create table t1 (col1 int, col2 nvarchar(10));
	insert into t1 values (1, 'abc');
	insert into t1 values (2, 'def');
commit;

Implicit

Implicit transactions has to be set per session and will automatically open a new transaction if there is no open transaction and a statement is executed. MSSQL will implicitly insert a BEGIN TRAN in front of your statement. However, a COMMIT or ROLLBACK must be explicitly stated. For those of you that are familiar with Oracle, this might seem closer to what  you are accustomed too. However, due to the differences in how Oracle and MSSQL handle sessions and transactions the consequences will be very different. Even a SELECT statement would open a new transaction under implicit transaction mode , which means you would have to issue a commit or rollback to your select statements as well. Failing to do so would leave an open transaction. Open transactions in MSSQL effectively blocks transaction log backups and exclusive locks taken out by the statements in the transaction will be held for the duration of the transaction. Since the default isolation level in MSSQL is read committed, which enforces a pessimistic concurrency control, locks taken by the transaction may block writers and readers until the transaction ends (this can be somewhat remedied by RCSI, but more on that later)

I would strongly advice against using implicit transaction mode as the consequences of forgetting/omitting a COMMIT/ROLLBACK statement is so dire. To verify this behavior, you can run the below:

use master;
go

set implicit_transactions on;
go

/*A transaction is not implicitly started for selects not selecting from a table/view/system functions and the like, example: select getdate(), select @@trancount, select @@servername etc */
select 'Trancount before SELECT statement: ', @@trancount as trancount_before_select;

/*MSSQL will silently insert a begin tran at the beginnign of the statement selecting from INFORMATION_SCHEMA.tables */
select * from INFORMATION_SCHEMA.tables;

select 'Trancount after SELECT statement: ', @@trancount as trancount_after_select;
commit;

select 'Trancount after COMMIT statement: ',@@trancount as trancount_after_commit;
set implicit_transactions off;

If you still want to use implicit transactions you can activate it by issuing the SET IMPLICIT_TRANSACTIONS ON statement on a session level. After doing so, a complete transaction would look like this (note the lack of a BEGIN TRANSACTION statement):

create table t1 (col1 int, col2 nvarchar(10));
insert into t1 values (1, 'abc');
insert into t1 values (2, 'def');
commit;

Resources

I always like to include a link to the official documentation if I can, so here you go. Just in case the link goes dead here is a saved copy in the wayback machine.

Note: for the purposes of this post, batch scoped transactions have been purposefully omitted as they are only relevant for Multi Active Result Sets (MARS), which is way out of scope.