Types of Transactions in SQL Server

Transactions are like safety nets for databases in SQL Server. They help keep data safe and consistent by making sure that when we make changes, they all happen together or none at all. If something goes wrong, transactions allow us to undo everything, avoiding half-done updates. They're essential for reliable database operations and prevent strange data issues. Transactions also let many people work on the database at the same time without messing things up.

In this article, I aim to showcase the types of transactions in SQL Server and explain how to apply them in various scenarios.

There are three main types of transactions in sql server:

1- Auto Commit Transaction Mode in SQL Server

2- Implicit Transaction Mode in SQL Server

3- Explicit Transaction Mode in SQL Server

I will use the following table to explain the various types of transactions in SQL Server.

CREATE TABLE Person
(
    Id INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
)        

1- Auto Commit Transaction Mode in SQL Server:

This is the default transaction mode in SQL Server. In this transaction mode, each SQL statement is treated as a separate transaction. In this Transaction Mode, as a developer, we are not responsible for either beginning the transaction (i.e. Begin Transaction) or ending a transaction (i.e. either Commit or Roll Back). Whenever we execute any DML statement, the SQL Server will automatically begin the transaction as well as end the transaction with a Commit or Rollback i.e. if the transaction is completed successfully, it is committed. If the transaction faces any error, it is rolled back. So the programmer does not have any control over them.

I'll provide an example for clarification:

INSERT INTO Person VALUES (1, ‘Nadim’, ‘Attar’)

INSERT INTO Person VALUES (1, ‘Belal’, ‘Attar’)

The first insert will execute successfully, but the second insert will throw the following exception: "Violation of Primary Key Constraint. Cannot insert duplicate key in object 'dbo.Person'"

2- Implicit Transaction Mode in SQL Server:

In the Implicit mode of transaction, the SQL Server is responsible for beginning the transaction implicitly before the execution of any DML statement and the developers are responsible to end the transaction with a commit or rollback. Once the transaction is ended ie. once the developer executes either the commit or rollback command, then automatically a new transaction will start by SQL Server. That means, in the case of implicit mode, a new transaction will start automatically by SQL Server after the current transaction is committed or rolled back by the programmer.

In order to use implicit transaction mode in SQL Server, first, we need to set the implicit transaction mode to ON using the SET IMPLICIT_TRANSACTIONS statement. The value for IMPLICIT_TRANSACTIONS can be ON or OFF. When the value for implicit transaction mode is set to ON then a new transaction is automatically started by SQL Server whenever we execute any SQL statement (Insert, Select, Delete, and Update).

I'll provide an example for clarification:

SET IMPLICIT_TRANSACTIONS ON

INSERT INTO Person VALUES (1, 'Nadim', 'Attar');
INSERT INTO Person VALUES (2, 'Belal', 'Attar');

COMMIT TRANSACTION

INSERT INTO Person VALUES (3, 'Hossam', 'Ekko');
UPDATE Person SET LastName = 'Ekko' WHERE Id = 2;
SELECT * FROM Person;        

When you execute the above statement and you will get the below data.

ROLLBACK TRANSACTION;        

Once you roll back the transaction, issue a select query against the person table and you will see the following data.

If you prefer not to use the implicit transaction mode, you can turn it off by using the following statement.

SET IMPLICIT_TRANSACTIONS OFF        

3- Explicit Transaction Mode in SQL Server:

In the Explicit mode of transaction, the developer is only responsible for beginning the transaction as well as ending the transaction. In other words, we can say that the transactions that have a START and END explicitly written by the programmer are called explicit transactions.

Here, every transaction should start with the BEGIN TRANSACTION statement and ends with either a ROLLBACK TRANSACTION statement (when the transaction does not complete successfully) or a COMMIT TRANSACTION statement (when the transaction completes successfully). The Explicit Transaction Mode is most commonly used in triggers, stored procedures, and application programs.

Explicit Transaction Mode Example using SQL Server Stored Procedure:

CREATE PROCEDURE SPAddPerson
AS
BEGIN
   BEGIN TRANSACTION
      INSERT INTO Person VALUES(3, 'Hossam', 'Ekko')
      INSERT INTO Person VALUES(4, 'Mothanna', 'Al Assar')
      IF(@@ERROR > 0)
      BEGIN
         ROLLBACK TRANSACTION
      END
      ELSE
      BEGIN
         COMMIT TRANSACTION
      END  
END        

In my next article, I'll talk about Nested Transactions in SQL Server using examples. For now, in this article, I'm explaining the different types of transactions (Auto Commit, Implicit, and Explicit) in SQL Server with examples. I hope you find this discussion on the various transaction types easy to understand and see the differences between Auto, Implicit, and Explicit modes in SQL Server.