SQL Transaction Modes

There are different ways of starting transactions in SQL Server. Each transaction modes differ from one another in their own behavior and it is crucial to understand how they work.

SQL Transaction is a set of SQL commands that performed as single component of work unit. All SQL commands within the transaction are committed if executed successfully, or rolled back if any of the SQL command fail. It is used to provide reliable way to allow correct recovery of failure and keep database consistent.

Here are the list of different transaction modes in SQL Server,

- Autocommit transaction
- Implicit transaction
- Explicit transaction
- Batch-scoped transaction
- Distributed transaction

By default, SQL Server operates in autocommit transaction mode. All T-SQL statement is treated as a transaction and automatically committed or rolled back upon its completion. This behavior is changed when an implicit transaction is enabled or start of an explicit transaction.

Implicit transaction is enabled with SET IMPLICIT_TRANSACTIONS ON statement. SQL Server automatically start an implicit transaction on the next T-SQL statement (e.g. update, insert, alter). While the transaction is started automatically, COMMIT and ROLLBACK statement is used explicitly to end the transaction. To disable implicit transaction, use SET IMPLICIT_TRANSACTIONS OFF statement.

Explicit transaction is started with BEGIN TRANSACTION statement. The transaction is committed with COMMIT TRANSACTION or rolled back with ROLLBACK TRANSACTION statement.

Batch-scoped transaction only apply to MARS (Multiple Active Result Sets). Implicit or explicit transaction started in MARS is managed as batch-scoped transaction. This type of transaction is rolled back if the batch or stored procedure which started the transaction does not complete (commit/rollback) its transaction before the batch or stored procedure exits.

Distributed transaction is a type of transaction usually span over two or more servers. The transaction is managed by transaction manager such as Microsoft Distributed Transaction Coordinator (MS DTC). Transaction within single instance across multiple databases is considered as distributed transaction however managed locally and internally by the single instance. The distributed transaction is started with BEGIN DISTRIBUTED TRANSACTION statement. COMMIT TRANSACTION and ROLLBACK TRANSACTION are used to commit or rollback the distributed transaction. Local transaction is promoted to distributed transaction when SET REMOTE_PROC_TRANSACTIONS ON and a remote query are executed.

Distributed transaction is also known as two-phase commit (2PC). In the first phase, prepare phase,  transaction manager (MS DTC or local server instance for single instance-multiple databases transaction) receive the transaction commit request, and sends prepare command to all resource manager (the participating servers) to make the transaction durable and flush its respective data and log to disk. In the second phase, commit phase, once the transaction manager receives successful prepare from all resource manager, it issues commit command to all resource manager to complete the commit. After transaction manager receives complete commit, it sends notification back to the original requester and remove (forget) the transaction from its list.

Here are some tests with transaction log to show how these different type of transaction modes start and commit. Test setup,

CREATE DATABASE TEST;
GO

ALTER DATABASE TEST
SET RECOVERY SIMPLE;
GO

USE TEST;
GO

CREATE TABLE dbo.Table1
(
 ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 detail varchar(10)
);
GO

Autocommit transaction,

--Initial IAM, PFS Page Allocation and etc
INSERT INTO dbo.Table1 VALUES ('test0');
--Clear the transaction log
CHECKPOINT;

INSERT INTO dbo.Table1 VALUES ('test1');
INSERT INTO dbo.Table1 VALUES ('test2');

DBCC LOG (TEST, 0);

The transaction log shows each insert statement is treated as a transaction. The first insert statement begin its transaction at line 5 and commit the transaction at line 7. Same with second insert transaction.

 Implicit Transaction,

--Clear the table

TRUNCATE TABLE dbo.Table1;
GO

--Initial IAM, PFS Page Allocation and etc
INSERT INTO dbo.Table1 VALUES ('test0');
--Clear the transaction log
CHECKPOINT;

SET IMPLICIT_TRANSACTIONS ON;
INSERT INTO dbo.Table1 VALUES ('test1');
INSERT INTO dbo.Table1 VALUES ('test2');
COMMIT;
SET IMPLICIT_TRANSACTIONS OFF;

DBCC LOG (TEST, 0);

Due to implicit transaction mode is enabled, it begins a transaction on first insert transaction and only commit the transaction on the commit statement. Transaction log shows that both insert transactions are within the same transaction begin at line 5 and commit at line 8.

Explicit Transaction,

--Clear the table

TRUNCATE TABLE dbo.Table1;
GO

--Initial IAM, PFS Page Allocation and etc
INSERT INTO dbo.Table1 VALUES ('test0');
--Clear the transaction log
CHECKPOINT;

BEGIN TRAN
INSERT INTO dbo.Table1 VALUES ('test1');
INSERT INTO dbo.Table1 VALUES ('test2');
COMMIT TRAN;

DBCC LOG (TEST, 0);

The BEGIN TRAN starts an explicit transaction and commit on the COMMIT TRAN statement. The transaction start at line 5 and commit at line 8.

Batch-Scoped Transaction,

--Clear the table

TRUNCATE TABLE dbo.Table1;
GO

--Initial IAM, PFS Page Allocation and etc
INSERT INTO dbo.Table1 VALUES ('test0');
--Clear the transaction log
CHECKPOINT;

In this example, PowerShell is used to open a MARS connection and executes insert statements with an explicit transaction without commit/rollback statement. After the query completed, the transaction is automatically rollback due to the fact that SQL Server is treating it as batch-scoped transaction.

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = 'server=TESTSERVER;database=TEST;Trusted_Connection=True;MultipleActiveResultSets=True;'

$command = New-Object System.Data.SqlClient.SqlCommand
$command.Connection = $connection
$command.CommandText = 
"BEGIN TRAN " +
"INSERT INTO TEST.dbo.Table1 VALUES ('test1'); " +
"INSERT INTO TEST.dbo.Table1 VALUES ('test1'); "

$connection.Open()
$command.ExecuteNonQuery()
$connection.Close()

The transaction log shows that the transactions are rolled back.

Distributed Transaction,

--Create another database
CREATE DATABASE TEST1
GO

ALTER DATABASE TEST1
SET RECOVERY SIMPLE;
GO

USE TEST1;
GO

CREATE TABLE dbo.RemoteTable1
(
 ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 detail varchar(10)
);
GO

--Initial IAM, PFS Page Allocation and etc
INSERT INTO dbo.RemoteTable1 VALUES ('test0');
CHECKPOINT;
GO

--Back to TEST database
USE TEST;
GO

--Clear the table
TRUNCATE TABLE dbo.Table1;
GO

--Initial IAM, PFS Page Allocation and etc
INSERT INTO dbo.Table1 VALUES ('test0');
--Clear the transaction log
CHECKPOINT;

--Insert 2 transactions to table1 in TEST database, and 2 transactions to remotetable1 in TEST1 database
BEGIN DISTRIBUTED TRAN
INSERT INTO TEST.dbo.Table1 VALUES ('test1');
INSERT INTO TEST.dbo.Table1 VALUES ('test2');

INSERT INTO TEST1.dbo.RemoteTable1 VALUES ('test1');
INSERT INTO TEST1.dbo.RemoteTable1 VALUES ('test2');
COMMIT;

DBCC LOG (TEST, 0);
DBCC LOG (TEST1, 0);

The above test simulates distributed transaction across multiple databases in a single instance. From the log, the resource manager (server instance) first inserts the rows, when transaction manager (in this case, the same server instance as well) receive the commit command, it issues a prepare command (line 7) and then commit the transaction (line 9). Note that SQL Server will automatically promote this transaction as distributed transaction even with just a regular explicit transaction with BEGIN TRAN since the transaction involves multiple databases. As this is a local distributed transaction managed by the single instance, the transaction is removed (forget at line 10) after the successful commit.

Note that BEGIN TRANSACTION and BEGIN TRAN; COMMIT, COMMIT TRAN and COMMIT TRANSACTION; ROLLBACK, ROLLBACK TRAN and ROLLBACK TRANSACTION are interchangeable respectively in this post.