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.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE Person (PersonID INT PRIMARY KEY, LastName VARCHAR(255), FirstName VARCHAR(255), Address VARCHAR(255), City VARCHAR(255), Age INT ); |
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.
|
1 2 3 4 5 6 7 8 9 |
INSERT INTO Person VALUES (1, 'Hayes', 'Corey', '123 Wern Ddu Lane', 'LUSTLEIGH', 23 ); |
When we try to execute the following query, SQL Server rollbacks the data modification due to a duplicate primary key error.
|
1 2 3 4 5 6 7 8 9 |
INSERT INTO Person VALUES (1, 'Macdonald', 'Charlie', '23 Peachfield Road', 'CEFN EINION', 45 ); |
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.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE EVENT SESSION TrackTransactions ON SERVER ADD EVENT sqlserver.sql_transaction ( ACTION (sqlserver.session_id,sqlserver.database_id,sqlserver.sql_text) WHERE (transaction_state =1 OR transaction_state =2) AND sqlserver.database_name = 'AdventureWorks2017' ) ADD TARGET package0.ring_buffer; GO |
After creating the extended event, it will be shown under the Extended Events folder of the SQL Server Management Studio (SSMS).
We can start it either manually or can start with the help of the below query:
|
1 |
ALTER EVENT SESSION TrackTransactions ON SERVER STATE=START; |
Now, we will execute the following queries, the first of them will be executed successfully and the second one will return an error.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
INSERT INTO Person VALUES (2, 'Townsend', 'Imogen ', '100 Shannon Way', 'CHIPPENHAM', 20); GO INSERT INTO Person VALUES (2, 'Khan', 'Jacob', '72 Ballifeary Road', 'BANCFFOSFELEN', 11 ); |
The following query will return the collected details that have been captured by the extended event.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SELECT * FROM(SELECT event.value('(event/@name)[1]', 'varchar(50)') AS event, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp], event.value('(event/action[@name="session_id"])[1]', 'int') AS session_id, event.value('(event/action[@name="database_id"])[1]', 'int') AS database_id, event.value('(event/data[@name="duration"])[1]', 'bigint') AS duration_microseconds, event.value('(event/data[@name="transaction_id"])[1]', 'bigint') AS transaction_id, event.value('(event/data[@name="transaction_state"]/text)[1]', 'nvarchar(max)') AS transaction_state, event.value('(event/data[@name="transaction_type"]/text)[1]', 'nvarchar(max)') AS transaction_type, event.value('(event/action[@name="sql_text"])[1]', 'nvarchar(max)') AS sql_text FROM ( SELECT n.query('.') AS event FROM ( SELECT CAST(target_data AS XML) AS target_data FROM sys.dm_xe_sessions AS s JOIN sys.dm_xe_session_targets AS t ON s.address = t.event_session_address WHERE s.name = 'TrackTransactions' AND t.target_name = 'ring_buffer' ) AS s CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(n) ) AS t) AS TMP_TBL WHERE TMP_TBL.session_id <> @@SPID; |
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.
|
1 |
SET IMPLICIT_TRANSACTIONS { ON | OFF } |
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.
|
1 2 3 4 5 6 7 8 |
SET IMPLICIT_TRANSACTIONS ON UPDATE Person SET Lastname = 'Sawyer', Firstname = 'Tom' WHERE PersonID = 2 |
We open another query window and execute the sp_WhoisActive procedure to monitor the locked objects.
|
1 2 |
EXEC sp_WhoisActive @get_locks = 1; |
We can find out more details about the locked objects when we click the locks column.
As the last step, we have to execute the COMMIT TRAN statement to commit the open transaction so the data changes will become permanent.
|
1 |
COMMIT TRAN |
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.
Find the ANSI option which is placed under the Query Execution tab and check the SET IMPLICIT TRANSACTIONS option.
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.
|
1 |
SELECT IIF(@@OPTIONS&2 = 0, 'Implicit Transaction Off', 'Implicit Transaction On') AS TranMode; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SET IMPLICIT_TRANSACTIONS OFF BEGIN TRAN; INSERT INTO Person VALUES (3, 'Bunny', 'Bugs', '742 Evergreen Terrace', 'Springfield', 54 ); COMMIT TRAN; SELECT * FROM Person |
When we rollback a transaction the data modifications will be undone.
|
1 2 3 4 5 6 7 8 |
SET IMPLICIT_TRANSACTIONS OFF BEGIN TRANSACTION INSERT INTO Person VALUES(4,'Mouse', 'Micky','500 South Buena Vista Street, Burbank','California',43) ROLLBACK TRAN SELECT * FROM Person WHERE PersonID=4 |
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.
Tip: Generally, it would be a logical option to use explicit transactions with TRY-CATCH blocks.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SET IMPLICIT_TRANSACTIONS OFF BEGIN TRY BEGIN TRANSACTION INSERT INTO Person VALUES(4,'Mouse', 'Micky','500 South Buena Vista Street, Burbank','California',43) COMMIT TRANSACTION END TRY BEGIN CATCH IF(@@TRANCOUNT > 0) ROLLBACK TRAN END CATCH |
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.
|
1 2 |
CREATE TABLE InsertSomeRecord (RowNumber INT, RowNumberString VARCHAR(10)) |
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
- Select the Path in the combobox and then type the log file path that we want to capture activities
-
Click the OK button
As a first step, we will execute the following query which will insert 1000 rows in auto-commit transaction mode.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @Counter INT= 1; WHILE @Counter <= 1000 BEGIN INSERT INTO InsertSomeRecord (RowNumber, RowNumberString ) VALUES (@Counter, 'RowNumber=' + CAST(@Counter AS NVARCHAR(7)) ); SET @Counter = @Counter + 1; END; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
BEGIN TRAN DECLARE @Counter INT= 1; WHILE @Counter <= 1000 BEGIN INSERT INTO InsertSomeRecord (RowNumber, RowNumberString ) VALUES (@Counter, 'RowNumber=' + CAST(@Counter AS NVARCHAR(7)) ); SET @Counter = @Counter + 1; END; COMMIT TRAN |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DECLARE @Counter INT= 1; WHILE @Counter <= 1000 BEGIN BEGIN TRAN INSERT INTO InsertSomeRecord (RowNumber, RowNumberString ) VALUES (@Counter, 'RowNumber=' + CAST(@Counter AS NVARCHAR(7)) ); SET @Counter = @Counter + 1; COMMIT TRAN END; |
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.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023