Esat Erkec

Transactions in SQL Server for beginners

February 10, 2021 by

In this article, we will talk about fundamental details of the transactions in SQL Server.

Introduction

A transaction is the logical work unit that performs a single activity or multiple activities in a database. Transactions may consist of a single read, write, delete, or update operations or a combination of these. Suppose that, when we want to withdraw money from the ATM, the ATM application will achieve this operation in three steps. As a first step, the application will check the balance of the account, and then it will deduct the money from the source account. Along with these two processes, it will keep the log of this money withdrawing activity. The following image basically illustrates the working principle of the transactions in the relational database systems.

Illustration of the transactions in SQL Server

The main idea of transactions is that when each of the statements returns an error, the entire modifications rollback to provide data integrity. On the other hand, if all statements are completed successfully the data modifications will become permanent on the database. As a result, if we experience any power outage or other problems during the withdrawal of money from an ATM, transactions guarantee our balance consistency. It would be the best method to perform all these steps through a transaction because the four main properties of the transactions enable all operations more accurate and consistent. All these properties are known as the ACID (atomicity, consistency, isolation, durability) in the relational database systems with the first letter of their names.

  • Atomicity: The entire of the operations that are included by the transaction performed successfully. Otherwise, all operations are canceled at the point of the failure and all the previous operations are rolled back
  • Consistency: This property ensures that all the data will be consistent after a transaction is completed according to the defined rules, constraints, cascades, and triggers
  • Isolation: All transactions are isolated from other transactions
  • Durable: The modification of the commited transactions becomes persist in the database

Pre-Requirements

In this article, we will create a sample table through the following query and will populate some sample data.

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

You can read the following articles to learn more details about recovering a database from the transaction log backups:

Conclusion

In this article, we have talked about the transaction in SQL Server statements. Transactions are a vital part of relational database systems because they provide integrity of the databases.

Esat Erkec
Locking, Monitoring

About Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert. Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn. View all posts by Esat Erkec

168 Views