Isolation levels in SQL Server

Isolation levels :- Isolation level is required to isolate a resource and protect it from other transactions. This is achieved with the help of locks 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 but it may result in many  concurrency related problems like phantom reads, dirty reads etc. If higher levels of Isolation is set then it eliminate the concurrency related problem but it results in less number of concurrent access and it may result in data blocking

Dirty reads:- This situation happens when a transaction tries to read a data by some other concurrent transaction which is not committed yet. There is a  risk, that this other transaction may never be committed,  leaving the original transaction with wrong data.

Lost updates:- It happen 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.

Repeatable reads:- Repeatable reads condition occur when a transaction tries to read a data multiple times and and between the two reads, another transaction modified that data. Therefore when the original transaction tries to read that data second time, it find different value for that data. In other words, the original transaction reads two different values for the same data.

Phantom reads:-This condition happen when a transaction needs to execute a same query twice(or multiple times) and it gets different set of rows from what it get earlier due to the execution of the query first time. This happens if some another transaction add or delete rows between the two executions of the query and these added/deleted rows are the part of the record set returns by the execution of the query.

There are the  five Isolation levels (from lower level to higher level) defined in the SQL Server.

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

Read Uncommitted:- 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 problem like dirty read, lost updates, phantom reads etc. It is the least restrictive Isolation level.

Read Committed:- It is the default Isolation set by the SQL Server for any Database. This Isolation level prevents the transaction from reading data which are modified by some other transactions but still are not committed yet. Thus it eliminates the problem of Dirty read. But it do not eliminates the occurrence of Phantom reads and Repeatable reads.

Repeatable Read:- This isolation level is higher than the previous two mention isolation level and it does not allows any transaction to read a data that is being modified by some other transaction but not committed yet. Also it doesn’t allow any transaction to modify a data if it is being read by some other transaction until the transaction reading that data complete its operation.  This way it eliminates the dirty read and Repeatable reads problem but it doesn’t eliminates the Phantom reads.

Serializable:- This Isolation level do not allow any transaction to read the data unless the other 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. For example, if the query is “Select * from employees” 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 Employee where country=’India”, then the current transaction will acquire the read lock for all the records of the table where country is India, 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.

Snapshot:-In this isolation level, a transaction recognise only data which is committed before the start of the transaction. Any modification of the data 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.

Isolation level can be set by using the following command:-

SET TRANSACTION ISOLATION LEVEL

 

DMCA.com

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in SQL Isolation Level, SQL Server and tagged , , . Bookmark the permalink.

33 Responses to Isolation levels in SQL Server

  1. Sunny Lamba says:

    good article

  2. A.S. Reddy says:

    Thanks Vivek..Nice article and easily understand the concept..

  3. Nice to read the isolation levels………….keep it up

  4. in your profile one mistake
    Vivek JohariI am currently working as a Analyst and have around 6 yeras of experience in database.

    6 yeras — wrong
    6 years– correct

    kindly do it bye tkc

  5. ANANTH says:

    Nice Article Vivek………..Easily understandable

  6. Gokul says:

    Understandable and helpful article

  7. Anonymous says:

    Thanks for the article.. very easy to understand

  8. Nice Post Vivek ….Cheers !!!!

  9. Thanks vivek for this article it is very easy to understand even for beginners also

  10. Thanks Vivek. It is nice article and easy to understand the concept

  11. Anonymous says:

    thanks vivek for this article………keep posting like this.

  12. thanks…. its very nice article..

  13. Anonymous says:

    Thanks Vivek..this article is easily understandable..but can you please explain Snapshot Isolation in much more detail with a simple example?

  14. Ashish Kalra says:

    Excellent and easy way of teaching the concept….Thank you so much

  15. Andrew says:

    You should go and update the post here as it’s based on an out of date version of this article but still ranks highly in Google: http://www.sqlservercentral.com/blogs/vivekssqlnotes/2012/08/19/isolation-levels-in-sql-server/#comments. The out of date article includes incorrect information about the Read Uncommitted isolation level and could be putting many people wrong.

  16. Pingback: สมัครบาคาร่า

  17. Pingback: 카지노사이트

  18. Pingback: pokermas

  19. Pingback: Entrepreneurship Mindset

  20. Pingback: Small business website designs and marketing

  21. Pingback: xembongda tructiep

  22. Pingback: w888

Leave a Reply