Transaction Modes in SQL Server

TYPES Of Transactions in SQL Server

Autocommit Transaction

This is the default mode of transaction in SQL Server, Every command we issue in SQL server is an Implicit transaction, this applies to all SELECT, INSERT, UPDATE, and DELETE.

With this Transaction mode, you need to be very careful as a user can by mistake miss the where clause OR a user can delete all rows from the entire table.

Please note that there are no BEGIN / COMMIT commands in Auto Commit. every statement you run is Auto Committed.

UPDATE TABLE EMPLOYEES SET SALARY = 100--WHERE EMPLOYEEID = 25;
DELETE FROM EMPLOYEES;

Implicit Transaction

To Start the Implicit Transaction we need to issue the command “SET IMPLICIT_TRANSACTIONS ON”, Once all the Statements are executed we can do a COMMIT or ROLLBACK.

SET IMPLICIT_TRANSACTIONS ON;
SELECT * FROM [AdventureWorks2019].[dbo].[EMPLOYEES];
UPDATE [AdventureWorks2019].[dbo].[EMPLOYEES] SET SALARY=1000;
--WHERE ID=1; MISSED THE WHERE CLAUSE
ROLLBACK; --THIS TRANSACTION CAN BE ROLLED BACK

Use the below command to turn off the Implicit transaction

SET IMPLICIT_TRANSACTIONS OFF;

Explicit Transaction

In an Explicit transaction, we specify a BEGIN Transaction and a COMMIT / ROLLBACK Transaction.

BEGIN TRANSACTION;
SELECT * FROM [AdventureWorks2019].[dbo].[EMPLOYEES];
UPDATE [AdventureWorks2019].[dbo].[EMPLOYEES] SET SALARY=1000;
--WHERE ID=1; MISSED THE WHERE CLAUSE
ROLLBACK; --THIS TRANSACTION CAN BE ROLLED BACK TRANSACTION;

Below are two ways where you can check for any open transactions.

DBCC OPENTRAN;

SELECT TOP 10 SESSION_ID,open_transaction_count 
FROM SYS.dm_exec_sessions 
ORDER BY last_request_start_time DESC