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

Explanation of the Implicit Transaction in SQL Server

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:

  • 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.

Find the open transactions

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.

How to commit a transaction

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.

How to rollback a transaction

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.

Explanation of the savepoints in SQL Server

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.

Auto rollback mechanisim in SQL Server

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.

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

WITH MARK syntax usage in transactions in SQL Server

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: