What are the various types of Transaction Isolation Levels in SQL Sever

Transaction Isolation Levels in SQL Sever

Isolation is one of the ACID(Atomicity, Consistency, Isolation and Durability) properties of a RDBMS(SQL Server). Isolation level is nothing but locking the rows of an Object while performing some task in a Transaction, so that other transaction can not access or will wait for the current transaction to finish its job.

The process of Isolating / separating transactions from each other to maintain Data Integrity in Database is called Isolation. It means that a transaction should take place in a system in such a way that it is the only transaction at that moment accessing the resources in a database system.

Isolation levels defines the degree to which a transaction must be isolated from the data modifications made by any other transaction in the database system. Isolation levels helps to isolate a resource and protect it from other transactions. This is achieved with the help of locking the objects but what locks are needed and how they can be established is decided on the isolation level set on the database level.

If low level of Isolation is set, it allows multiple users to access the resources concurrently(simultaneously) but it may result in many concurrency related problems like dirty reads, repeatable reads, phantom reads, etc. If higher levels of Isolation is set then it eliminate the concurrency related problems but it results in less number of concurrent access and it may result in data blocking.

While developing large enterprise/public networking kind of applications where a huge no. of users access same Database or a Database object like Table at the same Time, then Data concurrency situation may occur.

The following are the most common problems arises due to concurrent Transactions:

Dirty Reads:

This situation happens when a transaction tries to read a data from an object which is being modified by some other concurrent transaction which is not committed yet. There is a risk, that the other transaction may or may not be committed/rolled back, leaving the original transaction with wrong/old data.

Example:

Let’s say Transaction 1 updates a row and leaves it uncommitted, meanwhile Transaction 2 reads the updated row. If transaction 1 rolls back the change, transaction 2 will have read data that is considered never to have existed and no longer valid.

Lost Updates/Loss of Data:

It happens when more than one transaction tries to update the same data. In Lost update, a successfully updated value of a data written in the database is overrides accidentally by the some another transaction updating the same data concurrently. 

Example:

Suppose, there are 2 users accessing the same table, at the same moment, to update the same row. Each transaction is unaware of the other transaction. 

User A updates the row and then User B updates the same row. Now the last transaction made my User B overwrites the updated record of User A, then User A lost the update he made in the table.

Non-Repeatable Reads:

Non-Repeatable reads condition occurs when a Transaction 1 tries to read a data multiple times and between the two reads, another Transaction 2 modified that data. Therefore when the Transaction 1 tries to read that data second time, it find different value for that data. In other words, the original Transaction 1 reads two different values(fails to repeatable read) for the same data.

Phantom Reads:
This condition happen when a Transaction 1 needs to execute a same query twice(or multiple times) and it gets different set of rows in each execution. This happens if some another Transaction 2 add or delete rows between the two executions of the query (of Transaction 1) and these added/deleted rows are the part of the record set reruns by the execution of the query.

Phantom Read vs Non-Repeatable read:

A Non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads.

A Phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first.

Example:

Suppose User A runs the same query twice in a transaction. In between, User B runs an another transaction and commits.
Non-repeatable read: The User A will get a different value in the second time.
Phantom read: All the rows in the query have the same value before and after, but different rows are being selected (because B has deleted or inserted some).

Inconsistency Analysis
This is also known as Non-Repeatable read Problem. Let's take the same example of User A and User B. Suppose, User A executes a transaction having three queries ,1st query is to read a table row, the 2nd query is to update that, and the 3rd query is to read that again. By doing this, User A wants to generate the report.
In the meantime User B has accessed the table row in between the two Read queries of User A and did some operation like Delete ! Now, User A has already modified the data and when he wants to read it again, he is surprised ! He got inconsistency in data.

To avoid these problems in the Transactions, we need to set the Transaction Isolation Levels on a Transaction based on the requirement.

We can set the level of Isolation for a Transaction using the below SQL Statement. 
SET TRANSACTION ISOLATION LEVEL  

SQL Server provides 5 Isolation levels(from lower level to higher level) to implement with SQL Transactions to maintain Data Integrity.

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable
• Snapshot

Now we will discuss about each Isolation Level with examples. Suppose we have a Table as follows on which we performing the Transactions..
Select * From [dbo].[Sample] 

1) READ UNCOMMITED :
In this Isolation level, a transaction can read the data which is modified by some other transactions but still not committed. 

This Isolation level do not issue shared locked to prevent data modification which is read by some other transactions. Also it is not issue exclusive locks to prevents transactions from reading data which is modified from other transaction by not committed yet. 

It may results in problems like dirty reads, lost updates, phantom reads etc. It is the least restrictive Isolation level.

Example:

Suppose a there is Transaction is executing by User A in Session 1 :

Begin Transaction MyTrans_A
 Begin Try
  Update [dbo].[Sample] SET [RegionName]='South-West' Where Order_Id=112
  Update [dbo].[Sample] SET NetSales='6354' Where Order_Id=114 
  Waitfor Delay '00:00:15'
  Rollback Transaction MyTrans_A
  --Commit Transaction MyTrans_A
  PRINT 'Transaction Success' 
 End Try
Begin Catch 
 Rollback Transaction MyTrans_A
 Print 'Transaction Failed & Rolledback' 
End Catch
GO
This Transaction runs for 15 Sec., after that the Transaction is Rolledback.

In the Meantime, User B in Session 2 read the data from the same table

SET TRANSACTION ISOLATION LEVEL  READ UNCOMMITTED 
Select * From [dbo].[Sample] Where Order_Id In (112,114)

Since the Isolation Level set for the Transaction is READ UNCOMMITED, User B got the Un-Committed data(before 15 Sec.) as below, which is actually not exist in actual table as it is Rolledback by User A.

2) READ COMMITED :

It is the default Isolation Level set by the SQL Server for any Database. This prevents only the Dirty Reads. When this level is set, the transaction can not read the data that is being modified by the some other previous transaction. This will force user to wait for the previous transaction to finish up its job.

Suppose User B is trying to read a row which is being updated by User A, then the User B have to wait for the User A to finish its update task, and giving the updated/correct data to User A. The problem with this Isolation level is, it can't resolve the Phantom Read or Inconsistency Analysis i.e it makes User B to wait for Read but not for Update or Insert.

Example:

Suppose User A is in Session 1 updating 2 records in a Transaction

Begin Transaction MyTrans_A
 Begin Try
  Update [dbo].[Sample] SET [RegionName]='North-West' Where Order_Id=112
  Update [dbo].[Sample] SET NetSales='54321' Where Order_Id=114
  Waitfor Delay '00:00:15'
  Commit Transaction MyTrans_A
  PRINT 'Transaction Success' 
 End Try
Begin Catch 
 Rollback Transaction MyTrans_A
 Print 'Transaction Failed & Rolledback' 
End Catch

GO

This Transaction(by User A) runs for 15 Sec., after that it will Commit.
In the Meantime, User B in Session 2 is Updating 2 records and read the data from the same table.
Update will happen from User B before(or in meantime) the Transaction finishes by User A.
But the Select statement will wait for till(15 Sec.) the Transaction 1 in Session 1 finishes.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Update [dbo].[Sample] SET [RegionName]='South-East' Where Order_Id=111
Update [dbo].[Sample] SET NetSales='12345' Where Order_Id=113

Select * From [dbo].[Sample] Where Order_Id In (111,112,113,114)

3) REPEATABLE READ

This Isolation level does every work that Read Committed does and it has one additional benefit as well. User B will have to wait for executing a Read/Update query on the same table until the transaction being executed by User A Committed. But Insert query doesn't wait, this also creates Phantom Read problem.
Example:

SET TRANSACTION ISOLATION LEVEL  REPEATABLE READ
-- User B : Session 2
Insert Into [dbo].[Sample] Values (110,'Gillette-Razor', 'Asia-Inida','Q1-2019',96543)
Update [dbo].[Sample] SET [RegionName]='South-East' Where Order_Id=111

Update [dbo].[Sample] SET NetSales='12345' Where Order_Id=113 
Select * From [dbo].[Sample] Where Order_Id In (110,111,112,113,114)

4) SNAPSHOT:

This level takes a snapshot of current data as a version into Tempdb. Every transaction works on its own copy of data.The Snapshot does not hold lock on table during the transaction so that the table can be modified in other sessions. Snapshot isolation maintains versioning in Tempdb for old data in case of any data modification occurs in other sessions then existing transaction displays the old data from Tempdb.

In this Isolation level, a Transaction recognize only data which is committed(by any previous transaction) before the start of the current Transaction. Any modification of the data(by any previous transaction) after the transaction is begin,  is not visible to any statements of the currently executing transaction. It is like a snapshot of data, given to each transaction. It is done with the help of row version where a separate version of each modified row is maintain in the temp db database dedicated to the transactions. This  isolation level eliminates dirty reads, lost updates, repeatable reads and Phantom reads problem.

Suppose, when User A tries to update or insert or read anything, we ask him to re-verify the table row once again from the starting time of its execution, so that he can work on fresh data. With this level, we are not giving full faith to User A that he is going to work on fresh data but giving high-level changes of data integrity.

Example:

Suppose the User A in Session 1, in a Transaction, updating the 2 records then waiting for 05 Sec., then updating another 2 records.
Begin Transaction MyTrans_A
 Begin Try
  Update [dbo].[Sample] SET [RegionName]='South-East' Where Order_Id=111
  Update [dbo].[Sample] SET NetSales='12345' Where Order_Id=113 
  Commit Transaction MyTrans_A
  Waitfor Delay '00:00:05'
  Update [dbo].[Sample] SET [RegionName]='North-West' Where Order_Id=112
  Update [dbo].[Sample] SET NetSales='54321' Where Order_Id=114
  Commit Transaction MyTrans_A
  PRINT 'Transaction Success' 
 End Try
Begin Catch 
 Rollback Transaction MyTrans_A
 Print 'Transaction Failed & Rolledback' 
End Catch

GO

In the Meantime(in between), with in 5 Sec., User B in Session 2 performing an insert and then reading the data from the same table. As the User B is working on his own Snapshot of the data, he will not wait for finishing the User A transaction. 

By the time User B started his Transaction only 2 records were updated by User A, so that other updates will not be added to the Snapshot. So that User B will get only the 2 updates which are available to him by the time he started his transaction.

To use Snapshot Isolation, you must first enable the feature on Databse

ALTER DATABASE AnalyticsDB SET ALLOW_SNAPSHOT_ISOLATION ON

SET TRANSACTION ISOLATION LEVEL  SNAPSHOT
-- User B : Session 2 
Insert Into [dbo].[Sample] Values (110,'Gillette-Razor', 'Asia-Inida','Q1-2019',96543)
Select * From [dbo].[Sample] Where Order_Id In (110,111,112,113,114)

5) SERIALIZABLE

This is the maximum level of Isolation level provided by SQL Server. This Isolation level do not allow any transaction to read the data unless the other previous transactions completed their data modification operation.
Also it doesn't allow other transactions to modify the data until the current transaction completed its read operation. This isolation level allows a transaction to acquire a read lock (if only read operation) or write lock (for insert, delete, update) for the entire range of records that the transaction is going to affect.

Example :

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

If we are running a query "Select * from Tbl_Sales" then the transaction will acquire the read lock for whole table and no other transaction is allowed to add new rows or delete the rows until the current transaction releases its lock. 

Similarly, if the query is "Select * from Tbl_Sales where Region='Asia'", then the current transaction will acquire the read lock for all the records of the table where Region is Asia, and no other transaction is allowed to add or delete new rows until the current transaction releases its read lock. Thus, if we executes the same query twice , then each time it will get the same set of rows of a data and therefore it eliminates the Phantom read problem.

Warnings :

Isolation level also has a problem called "Dead Lock"- when both the transactions lock the object and waits for each other to finish up the job. Dead Lock is very dangerous because it decreases the concurrency and availability of database and the database object

To Know Transaction Isolation Level for Current Session in the SQL Server :

Select CASE Transaction_Isolation_Level
                    When 0 Then 'Unspecified'
                    When 1 Then 'Read Uncommitted'
                    When 2 Then 'Read Committed'
                    When 3 Then 'Repeatable Re'
                    When 4 Then 'Serializable'

                    When 5 Then 'Snapshot' 

            END AS Transaction_Isolation_Level

From sys.dm_exec_sessions
where session_id = @@SPID

To Know Transaction Isolation Level for Each Session in the SQL Server:

SELECT Session_Id, Start_Time, Status, Total_Elapsed_Time,
CASE Transaction_Isolation_Level
                When 1 Then 'Read Uncomitted'
                When 2 Then 'Read Committed'
                When 3 Then 'Repeatable Read'
                When 4 Then 'Serializable'
                When 5 Then 'Snapshot'

ELSE 'Unspecified' 

END AS Transaction_Isolation_level, sh.Text, ph.Query_Plan

FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sh

CROSS APPLY sys.dm_exec_query_plan(plan_handle) ph

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------