Esat Erkec
Implicit transaction in SQL Server

Modes of Transactions in SQL Server

February 17, 2021 by

In this article, we are going to talk about the modes of transactions in SQL Server.

Introduction

A transaction is the smallest work unit that is executed in the database and transactions also meet the properties of the ACID (atomicity, consistency, isolation and durability). SQL Server can operate 3 different transactions modes and these are:

  • Auto-commit transactions
  • Implicit transactions 
  • Explicit transactions

In the following sections, we will tackle these transactions’ features, similarities, and differences.

Auto-commit transactions in SQL Server

The auto-commit transaction mode is the default transaction mode of the SQL Server. In this mode, each SQL statement is evaluated as a transaction by the storage engine. In this context, if any SQL statement completes its execution successfully it is committed and the data modification will become permanent in the database. On the other hand, if any statement faces any error it will be rolled back. In this transaction mode, we don’t try to manage transactions and all operations are managed by the SQL Server.

In the example below, after creating a table, we will insert one row into it.

When we execute the following query, SQL Server will automatically start a transaction and then it commits the transaction because this insert statement will not return any error.

When we try to execute the following query, SQL Server rollbacks the data modification due to a duplicate primary key error.

Auto-commit transactions in SQL Server

Now, we will look at behind the scenes of auto-commit transactions in SQL Server with the help of the extended events. Extended Events helps to capture the activities of the SQL Server and it’s a very beneficial feature to collect and monitor different events occurred behind the scene.

Through the following query, we will create an extended event that captures the committed and rollback transactions in SQL Server.

After creating the extended event, it will be shown under the Extended Events folder of the SQL Server Management Studio (SSMS).

Extended Event folder in SQL Server Management Studio

We can start it either manually or can start with the help of the below query:

Now, we will execute the following queries, the first of them will be executed successfully and the second one will return an error.

The duplicate primary key error in SQL Server

The following query will return the collected details that have been captured by the extended event.

Monitoring extended events captured details with a query

As we can see in the image, the transaction_type column shows the System value for our queries and it means that these transactions are operated by the SQL Server in the auto-commit transaction mode.

Implicit transaction mode in SQL Server

In the implicit transaction mode, SQL Server takes the responsibility for beginning the transactions implicitly but it waits for the commit or rollback commands from the user. In the implicit transaction mode, the database objects involved in the transaction will remain locked until the commit or rollback commands are executed. In order to use the implicit transaction mode, we need to set implicit transaction mode to ON. We can use the following syntax to enable or disable the implicit transaction mode.

The following keywords start a new transaction in the implicit transaction mode if it’s enabled.

  • ALTER TABLE
  • BEGIN TRANSACTION
  • CREATE
  • DELETE
  • DROP
  • FETCH
  • GRANT
  • INSERT
  • OPEN
  • REVOKE
  • SELECT
  • TRUNCATE TABLE
  • UPDATE

Now, let’s update any row of the Person table to using the implicit transaction in SQL Server. At first, we will enable the implicit transaction and then update the row.

We open another query window and execute the sp_WhoisActive procedure to monitor the locked objects.

Get lock to using sp_whoisactive for the transactions in SQL Server

We can find out more details about the locked objects when we click the locks column.

XML details of the locked objects

As the last step, we have to execute the COMMIT TRAN statement to commit the open transaction so the data changes will become permanent.

Implicit transaction in SQL Server

The implicit transaction is a connection-level setting and we can set this setting when connecting to the SQL Server. On the SQL Server Management Studio, we can determine the implicit transaction as a default transactions mode for our connections. At first, we click the Tools menu and select the Options.

Set implicit transaction mode as default in SQL Server Management Studio

Find the ANSI option which is placed under the Query Execution tab and check the SET IMPLICIT TRANSACTIONS option.

SET IMPLICIT TRANSACTIONS option on SSMS

After changing this setting in SSMS, the new connection’s default transaction modes will be the implicit transaction. The following query helps to find out the connections transaction mode.

Determine transaction mode in SQL Server

Explicit transaction mode in SQL Server

In the explicit transaction mode, we have to define the starting and ending points of the transactions. It means that all transactions must start with the BEGIN TRANSACTION statement and end with either COMMIT TRANSACTION or ROLLBACK TRANSACTION statements. We can use explicit transactions in SQL Server in its simplest form as below. After committing the transaction the data modification will be persisted in the database.

Explicit transaction mode in SQL Server

When we rollback a transaction the data modifications will be undone.

Rollback a transaction in SQL Server

When we retrieve data from the extended events, we can see the above insert operations which were executed by us. These insert statements transaction_type will show as User. It means these transactions are managed by the session user instead of the SQL Server.

Monitoring transaction in SQL Server with extended events

Tip: Generally, it would be a logical option to use explicit transactions with TRY-CATCH blocks.

Differences between the auto-commit and explicit transactions in SQL Server

In this section, we will observe the differences of the auto-commit transaction mode against the explicit transaction mode. At first, we briefly take a glance at the log buffer flush mechanism of the SQL Server. SQL Server writes all modifications into the log buffer and this buffered data is sent into the log file when the following conditions are met:

  • When a transaction is committed
  • The log buffer size reaches 60 KB
  • sys.sp_flush_log procedure is executed
  • CHECKPOINT process is completed

In order to monitor the difference between two transaction modes in SQL Server, we will use a tool, Process Monitor. Process Monitor is a tool that helps to monitor all activities of processes in the windows based operating systems. At first, we will create the following table and insert this table with 100.000 records.

After creating the table, we will launch the Process Monitor and filter the SQL Server engine process and the log file path that the test is performed.

  • Click the Ctrl + L key combination in order to open the filtering options
  • Select the Process Name in the combobox and then type sqlservr.exe to capture the only SQL Server process

    Filter SQL server process in the Process monitor.

  • Select the Path in the combobox and then type the log file path that we want to capture activities
  • Click the OK button

    Filter transaction log file process in the Process monitor.

As a first step, we will execute the following query which will insert 1000 rows in auto-commit transaction mode.

Analyze the log file activity in auto-commit transaction mode

As we have seen that in the auto-commit transaction mode the log flush process has occurred 1000 times in random sized chunks. For the explicit transaction mode, we will clear the captured process and execute the following query:

Analyze the log file activity in explicit transaction mode

In the explicit transaction mode, the log buffer size has reached the maximum size and then it flushes into the log file.

Result of the Benchmark: In this benchmark, we have seen how explicit transactions and auto-commit transactions affect the log file activity differently. According to our application and business requirements, we can decide which transaction mode is suitable for us. However, the main point in this comparison, we manage all inserts in one transaction for the explicit mode. In this way, we have decreased the log file activity. When we format the same query like the below, we can not see the same effect on log activity.

Conclusion

In this article, we have learned the modes of transactions in SQL Server. Understanding transaction modes are very important because they directly affect data integrity and different transaction modes have different characteristics.

Esat Erkec
Latest posts by Esat Erkec (see all)
22,035 Views