Introduction

While working with SQL databases, we often write queries without giving a second thought about how the query is going to read data from the underlying tables. As we are aware, databases are designed to follow the basic ACID properties (Atomicity, Consistency, Isolation, Durability), and each isolation property ensures the correct integrity is applied to the transaction that is being executed in the session. In simple words, it means that when a transaction is executed, the engine considers it to be the one and only transaction that is being executed in that session. This property of the database helps to maintain consistency across the system and also is an important factor in reading data from a source where multiple transactions within the same and different sessions run at the same time.

Why is Transaction Isolation Required?

Setting up the correct isolation level for your application is an extremely important step in designing database solutions. This not only helps to eliminate locks on the tables which are being read, but also helps the database engine to create a correct execution plan for the query, thus enhancing the overall performance.

Suppose you are working on an OLTP database that has tables consisting of around 50 million records. There are multiple services that connect to this database and read and modify data. Now, there is also a dashboard that reads data from this table and presents it to the workforce team to manage its operations. An important thing to note here will be any update or select running against such a table will incur some cost as the data is humongous. Let's consider a scenario where the service is in the process of updating records and in the meanwhile the dashboard query also triggers. In this case, if the isolation levels are not defined correctly, we might end up reading some data which perhaps doesn't exist at all. This will create misinterpretations within the ops team, and they might make incorrect decisions.

The Types of Transaction Isolation Available

SQL Server has 4 isolation levels available.

1. READ UNCOMMITTED: This says that a transaction within the current session can read data that has been modified or deleted by another transaction but not yet committed. This imposes the least restrictions of isolation levels as the database engine doesn't issue any shared locks. As a result of this, it is highly likely that the transaction will end up reading data that has been inserted, updated, or deleted but never committed to the database. Such a scenario is known as dirty reads.
2. READ COMMITTED: This is the default setting for most SQL Server queries. It defines that a transaction within the current session cannot read data that has been modified by another transaction. For this reason, dirty reads are prevented when this setting is turned on.
3. REPEATABLE READ: In this setting, a transaction not only can read data that is modified by another transaction that has been committed but also imposes a restriction that no other transaction can modify the data that is being read until the first transaction completes. This eliminates the condition of non-repeatable reads.
4. SERIALIZABLE: There are multiple properties that are being set by this isolation level. This isolation level is the most restrictive as compared to the others, thus there might be some performance issues with this. The properties are mentioned as below:
   1. The current transaction can only read data modified by other transaction that has been committed.
   2. Other transactions have to wait in queue until the execution of the first transaction is completed.
   3. No other transactions are allowed to insert data, which matches the condition of the current transaction.

Changing the Transaction Isolation Level in SSMS

There are two ways to set up transaction isolation in SSMS:

1. Using the GUI
2. Using the T-SQL commands

Using the GUI

1. Right-click on the Query window and select "Query Options".

   

2. Select "Advanced" under "Execution" on the left-hand panel.
3. Click the drop-down option next to "SET TRANSACTION ISOLATION LEVEL".

   

4. Select the appropriate isolation level as per the requirements from the dropdown.

Using T-SQL commands

There are separate commands for setting up each isolation level. I have mentioned all the commands in the screenshot below. This setting is applied to the current session and remains applied until changed explicitly.

Takeaway

Despite the advantages and disadvantages offered by transaction isolation levels, we should make use of this setting judiciously. Make sure that you have correctly understood your data before applying any isolation level directly. While the default isolation level set by SQL Server is READ COMMITTED, it is often necessary to alter this based on business requirements and also sometimes to enhance the performance of the database. For further information, please refer to the official documentation provided by Microsoft on transaction isolation levels.