SQL Server Types of Transaction

Types of Transaction

There are three types of transaction in SQL Server.

1. Automatic transaction
2. Explicit transaction
3. Implicit transaction

 Automatic Transaction

In SQL Server, automatic transactions refer to the default behavior where individual SQL statements are treated as transactions and are automatically committed or rolled back based on success or failure of each statement. Look at the following example.

CREATE TABLE Employees
(
	Id int ,
	first_name varchar(10) ,
	last_name varchar(10)
) 
Go

Now insert a record into the table.

INSERT INTO Employees VALUES (1, 'Amit', 'Kumar')

The above statement will be automatically committed as the statement is without error. Now insert another record into the table. This will throw error and statement will be automatically roll backed.

INSERT INTO Employees VALUES ('Ritu', 'Sinha')

Note that we are not using commit or rollback statement but it is executed automatically.

Explicit Transaction

In SQL Server, explicit transaction is used to group multiple statements as a unit of transaction. Without this, each statement will be a transaction. We use BEGIN TRANSACTION statement explicitly to create a block of statements as transaction. Look at the following example.

BEGIN TRANSACTION
INSERT INTO Employees VALUES (1, 'Amit', 'Kumar')
INSERT INTO Employees VALUES ('Ritu', 'Sinha') --error
INSERT INTO Employees VALUES (5, 'Rohit', 'Sinha')
IF @@ERROR>0 
	Rollback TRANS
ELSE
	Commit TRANS

Note that @@ERROR global variable is used to count the number of errors in current session.

Implicit Transaction

In SQL Server, we use SET IMPLICIT_TRANSACTIONS ON statement to activate implicit transaction. By default it is not activated. In SQL Server, implicit transaction is used to group multiple statements as a unit of transaction without using BEGIN TRANSACTION statement. These statements will be grouped based on the COMMIT or ROLLBACK statement. All the statements before COMMIT or ROLLBACK statement will be part of the transaction. Note that unlike automatic transaction we need to use COMMIT or ROLLBACK statement.

To understand implicit transaction, we use the following steps.

TRUNCATE TABLE Employees
SET IMPLICIT_TRANSACTIONS on
INSERT INTO Employees VALUES (1, 'Amit', 'Kumar')
INSERT INTO Employees VALUES (2, 'Ritu', 'Sinha')
SELECT * FROM Employees

Run the above script. We find that SELECT query returns two rows. Add the following code.

ROLLBACK TRANSACTION
SELECT * FROM Employees 

Run the above script. We find that SELECT query returns no row.

How to know whether SET IMPLICIT_TRANSACTIONS is On or OFF in SQL Server?

In SQL Server, you can check whether the IMPLICIT_TRANSACTIONS setting is ON or OFF by querying the options column in the sys.dm_exec_sessions dynamic management view. The IMPLICIT_TRANSACTIONS setting determines whether implicit transactions are enabled or disabled for the current session.

SELECT 
    CASE 
		-- 2 in decimal is 10 in binary
        WHEN ( @@OPTIONS & 2 = 2 ) THEN 'ON' 
        ELSE 'OFF' 
    END AS ImplicitTransactionsStatus
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID; -- @@SPID returns the current session ID

Continued in next post...