Using Isolation Level In SQL Transaction

Isolation is one of the properties of SQL Transaction. Isolating / separating transactions from each other to maintain Data Integrity in Database is called Isolation.

Before going to implementation part of isolation, we will understand why isolation is required in database ?

Why Is Isolation Level Required ?

While developing large enterprise/public networking kind of applications where a huge number of users access same Database, same Table and at the same Time, Data concurrency situation may occur. We will discuss this situation into 4 parts:

1. Loss of Data
2. Dirty Read
3. Phantom Read
4. Inconsistency Analysis

Loss of Data

Let's take an 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. What happened here is the last transaction made my User B overwrites the updated record of User A and User A lost his/her data in the table.

Dirty Read

This is otherwise known as Uncommitted Dependency. Let's take another example - Suppose, User A and User B are accessing a table row at the same time. User A wants to read and User B wants to update the row. In the friction of time difference, transactions are executed. So, when User B not yet updated the row (during the update process), User A reads that row and got the old record which may not be correct for his/her operation. This situation is known as Dirty Read.

Phantom Read

This is also known as Phantom Problem. Let's again take another example - Suppose User A is granted to insert a row but the same time User B inserted that row. Now, when User A tries to insert, he/she can't. Then, he/she will get angry and say- "Hey you committed that this is available for me to insert, but you cheated on me and granted someone else to do so !" . You may/might get this problem while the reservation of Train/Movie ticket.

Inconsistency Analysis

This is also known as Non-Repeatable Problem. Let's take the same example of User A and User B. Suppose, User A executes a transaction having three queries - a stored procedure or transaction or individual query with a batch. 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. As we know User B is not a gentleman he always spoils the intention of User A, he 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.

In the above-mentioned points, we understood that - as User A is a weak person, User B always plays with User A and forcefully does it's job by dominating him. But as a Database Administrator or Developer, we need to help the User A. Now, we have a weapon called "Isolation Level" by using which we can cooperate User A to maintain its integrity.

Isolation Levels

SQL Server provides 5 Isolation levels to implement with SQL Transaction to maintain data concurrency in the database.

Isolation level is nothing but locking the row while performing some task, so that other transaction can not access or will wait for the current transaction to finish its job.

Let's write a transaction without Isolation level.

BEGIN TRANSACTION MyTransaction
BEGIN TRY
UPDATE Account SET Debit=100 WHERE Name='John Cena'
UPDATE ContactInformation SET Mobile='1234567890' WHERE Name='The Rock'
COMMIT TRANSACTION MyTransaction
PRINT 'TRANSACTION SUCCESS'
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION MyTransaction
PRINT 'TRANSACTION FAILED'
END CATCH

In the above code snippet, we have not implemented Isolation Level. Therefore, there are chances of data concurrency. Now, we will go through all the five Isolation levels and find the solution for our situation.

1. Read Uncommitted
   When this level is set, the transaction can read uncommitted data resulting in the Dirty Read problem. With this isolation level, we allow a transaction to read the data which is being updated by other transaction and not yet committed. Suppose User A is trying to read a row which is being updated by User B. Here, we are allowing User A to read the un-updated/uncommitted data i.e old data.
   
   Example
   

   SET TRANSACTION ISOLATION LEVEL
   READ UNCOMMITTED
   BEGIN TRANSACTION MyTransaction
   BEGIN TRY
   UPDATE Account SET Debit=100 WHERE Name='John Cena'
   UPDATE ContactInformation SET Mobile='1234567890' WHERE Name='The Rock'
   COMMIT TRANSACTION MyTransaction
   PRINT 'TRANSACTION SUCCESS'
   END TRY
   BEGIN CATCH
   ROLLBACK TRANSACTION MyTransaction
   PRINT 'TRANSACTION FAILED'
   END CATCH

2. Read Committed
   This prevents Dirty Read. When this level is set, the transaction can not read the data that is being modified by the current transaction. This will force user to wait for the current transaction to finish up its job. Suppose User A is trying to read a row which is being updated by User B. Here, we are asking User A to wait for the User B to finish its update task, and giving the updated/correct data to User A. But the problem with this level is - it can't resolve Phantom Read or Inconsistency Analysis i.e it asks User A to wait for Read but not for update or insert.
   
   Example
   

   SET TRANSACTION ISOLATION LEVEL
   READ COMMITTED
   BEGIN TRANSACTION MyTransaction
   BEGIN TRY
   UPDATE Account SET Debit=100 WHERE Name='John Cena'
   UPDATE ContactInformation SET Mobile='1234567890' WHERE Name='The Rock'
   COMMIT TRANSACTION MyTransaction
   PRINT 'TRANSACTION SUCCESS'
   END TRY
   BEGIN CATCH
   ROLLBACK TRANSACTION MyTransaction
   PRINT 'TRANSACTION FAILED'
   END CATCH

3. Repeatable Read
   This level does every work that Read Committed does. but it has one additional benefit. User A will wait for the transaction being executed by User B to execute it's Update query as well, like Read Query. But Insert query doesn't wait, this also creates Phantom Read problem.
   
   Example
   

   SET TRANSACTION ISOLATION LEVEL
   REPEATABLE READ

4. Snapshot
   This level takes a snapshot of current data. Every transaction works on its own copy of data. 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
   

   SET TRANSACTION ISOLATION LEVEL
   SNAPSHOT

5. Serializable
   This is the maximum level of Isolation level provided by SQL Server transaction. We can prevent Phantom Read problem by implementing this level of isolation. It asks User A to wait for the current transaction for any kind of operation he wants to perform.
   
   Example
   

   SET TRANSACTION ISOLATION LEVEL
   SERIALIZABLE

   Isolation level also has a problem called "Dead Lock"- "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. We will discuss Dead-Lock in the later part of this topic.

Hope, I covered the necessary points that can help many of us to implement Isolation level in the project with the identification of real situations. Believe me friends, it is a good practice to implement Transaction with "Isolation Level" in every type of projects.