Transaction Isolation Levels

30 Mar 2021 815 views 0 minutes to read Contributors

Introduction 

As we know that, in order to maintain consistency in a database, it follows ACID properties. Among these four properties (Atomicity, Consistency, Isolation and Durability) Isolation determines how transaction integrity is visible to other users and systems. It means that a transaction should take place in a system in such a way that it is the only transaction that is accessing the resources in a database system.

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

Why Is Isolation Level Required?

While developing large networking applications where a huge number of users access same Database, same Table and at the same Time, Data concurrency situation may occur, which leads to below problems.

  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.

Based on these phenomena, The SQL standard defines four isolation levels :

  1. Read Uncommitted :

Read Uncommitted is the lowest isolation level. In this level, one transaction may read not yet committed changes made by other transaction, thereby allowing dirty reads. In this level, transactions are not isolated from each other.

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

BEGIN TRY 

UPDATE Account SET Debit=100 WHERE Name='John Baird' 

UPDATE ContactInformation SET Mobile='1234567890' WHERE Name='Team Lead' 

COMMIT TRANSACTION

PRINT 'TRANSACTION SUCCESS' 

END TRY 

BEGIN CATCH 

ROLLBACK TRANSACTION   

PRINT 'TRANSACTION FAILED' 

END CATCH 


  1. Read Committed :

This isolation level guarantees that any data read is committed at the moment it is read. Thus it does not allows dirty read. The transaction holds a read or write lock on the current row, and thus prevent other transactions from reading, updating or deleting it.

 

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   

BEGIN TRY 

UPDATE Account SET Debit=100 WHERE Name='John Baird'

UPDATE ContactInformation SET Mobile='1234567890' WHERE Name='Team Lead'

COMMIT TRANSACTION   

PRINT 'TRANSACTION SUCCESS' 

END TRY 

BEGIN CATCH 

ROLLBACK TRANSACTION   

PRINT 'TRANSACTION FAILED' 

END CATCH 

 

 

  1. Repeatable Read :

This is the most restrictive isolation level. The transaction holds read locks on all rows it references and writes locks on all rows it inserts, updates, or deletes. Since other transaction cannot read, update or delete these rows, consequently it avoids non-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


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


  1. Serializable :

This is the Highest isolation level. A serializable execution is guaranteed to be serializable. Serializable execution is defined to be an execution of operations in which concurrently executing transactions appears to be serially executing.

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 

 

In this article