What are different Modes of the Transactions in SQL Server?

Modes of the Transactions in SQL Server

SQL Server can operate 3 different transactions modes and these are:

1. Autocommit Transaction mode is the default transaction for the SQL Server. In this mode, each T-SQL statement is evaluated as a transaction and they are committed or rolled back according to their results. The successful statements are committed and the failed statements are rolled back immediately
2. Implicit transaction mode enables to SQL Server to start an implicit transaction for every DML statement but we need to use the commit or rolled back commands explicitly at the end of the statements
3. Explicit transaction mode provides to define a transaction exactly with the starting and ending points of the transaction

How to define an Implicit Transaction in SQL Server

In order to define an implicit transaction, we need to enable the IMPLICIT_TRANSACTIONS option. The following query illustrates an example of an implicit transaction.

• Tip: @@TRANCOUNT function returns the number of BEGIN TRANSACTION statements in the current session and we can use this function to count the open local transaction numbers in the examples

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SET IMPLICIT_TRANSACTIONS ON UPDATE     Person SET     Lastname = 'Sawyer',     Firstname = 'Tom' WHERE     PersonID = 2 SELECT     IIF(@@OPTIONS & 2 = 2,     'Implicit Transaction Mode ON',     'Implicit Transaction Mode OFF'     ) AS 'Transaction Mode' SELECT     @@TRANCOUNT AS OpenTransactions COMMIT TRAN SELECT     @@TRANCOUNT AS OpenTransactions

The COMMIT TRANSACTION statement applies the data changes to the database and the changed data will become permanent.

How to define an Explicit Transaction in SQL Server

In order to define an explicit transaction, we start to use the BEGIN TRANSACTION command because this statement identifies the starting point of the explicit transaction. It has the following syntax:

1 2	BEGIN TRANSACTION [ {transaction_name | @tran_name_variable }     [WITH MARK ['description']]]

• transaction_name option is used to assign a specific name to transactions
• @trans_var option is a user-defined variable that is used to hold the transaction name
• WITH MARK option enable to mark a particular transaction in the log file

After defining an explicit transaction through the BEGIN TRANSACTION command, the related resources acquired a lock depending on the isolation level of the transaction. For this reason as possible to use the shortest transaction will help to reduce lock issues. The following statement starts a transaction and then it will change the name of a particular row in the Person table.

1 2 3 4 5 6 7 8

BEGIN TRAN   UPDATE Person SET    Lastname = 'Lucky',         Firstname = 'Luke' WHERE  PersonID = 1   SELECT @@TRANCOUNT AS OpenTransactions

As we stated in the previous section COMMIT TRAN statement applies the data changes to the database and the changed data will become permanent. Now let’s complete the open transaction with a COMMIT TRAN statement.

1 2 3 4 5 6 7 8

BEGIN TRAN UPDATE Person SET    Lastname = 'Lucky',         Firstname = 'Luke' WHERE  PersonID = 1 SELECT @@TRANCOUNT AS OpenTransactions COMMIT TRAN SELECT @@TRANCOUNT AS OpenTransactions

On the other hand, the ROLLBACK TRANSACTION statement helps in undoing all data modifications that are applied by the transaction. In the following example, we will change a particular row but this data modification will not persist.

1 2 3 4 5 6 7 8 9 10 11

BEGIN TRAN UPDATE Person SET    Lastname = 'Donald',         Firstname = 'Duck'  WHERE PersonID=2     SELECT * FROM Person WHERE PersonID=2   ROLLBACK TRAN   SELECT * FROM Person WHERE PersonID=2

The following table illustrates the structure of the explicit transactions in SQL Server.

BEGIN TRANSACTION	The starting point of the transaction
SQL commands	DML and SELECT statements
COMMIT TRANSACTION or ROLLBACK TRANSACTION	Apply data changing to the database or Erase data changing to the database

Save Points in Transactions

Savepoints can be used to rollback any particular part of the transaction rather than the entire transaction. So that we can only rollback any portion of the transaction where between after the save point and before the rollback command. To define a save point in a transaction we use the SAVE TRANSACTION syntax and then we add a name to the save point. Now, let’s illustrates an example of savepoint usage. When we execute the following query, only the insert statement will be committed and the delete statement will be rolled back.

1 2 3 4 5 6 7 8 9

BEGIN TRANSACTION INSERT INTO Person VALUES('Mouse', 'Micky','500 South Buena Vista Street, Burbank','California',43) SAVE TRANSACTION InsertStatement DELETE Person WHERE PersonID=3 SELECT * FROM Person ROLLBACK TRANSACTION InsertStatement COMMIT SELECT * FROM Person

Auto Rollback transactions in SQL Server

Generally, the transactions include more than one query. In this manner, if one of the SQL statements returns an error all modifications are erased, and the remaining statements are not executed. This process is called Auto Rollback Transaction in SQL. Now let’s explain this principle with a very simple example.

1 2 3 4 5 6 7 8

BEGIN TRAN INSERT INTO Person VALUES('Bunny', 'Bugs','742 Evergreen Terrace','Springfield',54)      UPDATE Person SET Age='MiddleAge' WHERE PersonID=7 SELECT * FROM Person   COMMIT TRAN

As we can see from the above image, there was an error that occurred in the update statement due to the data type conversion issue. In this case, the inserted data is erased and the select statement did not execute.

Marked transactions in SQL Server

SQL Server allows us to mark and add a description to a specific transaction in the log files. In this way, we can generate a recovery point that is independent of the time. Such as, when an accidental data modification occurs in the database and we don’t know the exact time of the data modification, the data recovery effort can be taken a long time. For this reason, marked transactions can be a useful solution to find out the exact time of the data modifications. In order to create a marked transaction, we need to give a name to the transaction and we also need to add WITH MARK syntax. In the following query, we will delete some rows and we will also mark the modifications in the log file.

1 2 3 4

BEGIN TRAN DeletePerson WITH MARK 'MarkedTransactionDescription'     DELETE Person WHERE PersonID BETWEEN 3 AND 4          COMMIT TRAN DeletePerson

The logmarkhistory table stores details about each marked transactions that have been committed and it is placed in the msdb database.

1	SELECT * FROM msdb.dbo.logmarkhistory

As we can see in the above image the logmarkhistory gives all details about the marked transaction. The following two options help to use marked transactions as a recovery point.

• STOPATMARK rolls forward to the mark and includes the marked transaction in the roll forward
• STOPBEFOREMARK rolls forward to the mark and excludes the marked transaction from the roll forward

Ref: 

https://www.sqlshack.com/transactions-in-sql-server-for-beginners/