SQL Server Isolation Levels

SQL Server Isolation Levels helps in concurrency & transaction management by preventing concurrency problems . In an environment where we have multiple users modifying and accessing the same information , it is essential that they all can work at the same time without any delays. They all should be able to read correct data and modify data at will.

Also see : SQL Server Concurrency & Transaction Management

 

Commit & Rollback

In transaction’s ACID Properties we talked about , how a transaction has to change the state of the database and complete it’s task and leave the system in a consistent state . What would happen , if system fails while a transaction is still under process? How will transaction leave the system in consistent state ?

For example , One transaction has these two tasks :

  1. Delete Rows from one table
  2. Update rows from another table.

You can assign as many tasks as possible to a transaction in SQL Server. How to do that is Topic for another post another day. So we have two tasks in a transaction. Now during it’s execution if task 1 is completed and while it was updating the database system fails. It is unable to update the data , so what now?

COMMIT

Commit in SQL Server is when a change is made permanent into database.

ROLLBACK

Rollback undo all the changes made into database upto a certain point.

Coming back to our question now.  When a transaction fails to complete , it will rollback the changes made by task one of the transaction. This way it manages its ACID properties . It will leave the database in a reliable and consistent state by undoing task 1.

If everything goes well and both tasks are completed by transaction. These changes will then be committed in database and then they can’t be reverted by  rollback.

Transaction Dependencies Problems For Concurrency

A transaction should always and always follow ACID Properties  , but sometimes a transaction may end up exhibit other behaviours . These behaviours are the concurrency problems . SQL Server use Isolation levels to provide control over them. Let’s take a look at the few of them

  1. Dirty Reads
  2. Lost Updates
  3. Repeatable Reads
  4. Phantom Reads

A little explanation would be nice before we dive into Isolation levels and then we will see which isolation levels manage these problems.

Dirty Reads

When a transaction tries to read a data which is being modified 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.

Consider an example of our University Management System , for example university management decides to check how many students belong to a Lahore city. They will run a SQL query to get the data. At the very same time a student A puts a request to change his city from Karachi to Lahore because he has moved .  Now think the result university management will get , should it contain that student A who has requested his city to change? Answer is Yes.

However , there is healthy chance Student A is not a part of the result university management is going to get from the SQL Query. This is a dirty read.

Lost Updates :

Occur when two transaction read the same data and both manipulate the data, changing its value and then both try to update the original data to the new value. The second process might overwrite the first update completely.

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:

When a transaction needs to execute a same query twice(or multiple times) and it gets different set of rows from the result of its first execution of the query. This happens if some another transaction adds or deletes rows between the two executions of the query and these change in rows becomes the the part of final result.

SQL Server Isolation Levels

SQL Server has six isolation levels .

  1. Read Uncommitted
  2. Read Committed
  3. Repeatable Read
  4. Serializable
  5. Snapshot

All these isolation levels helps in preventing one or more above problems . Remember Higher the level of isolation level you set the lesser will be the availability of data for other users. Trade off is the word you can use to describe this . To gain some you have to lose some. lovely!

Read Uncommitted

SQL Tips :-  When learning SQL Server or any other DBMS and database terms always focus on the term’s meaning. It will be self-explanatory.

This Isolation level allows other transactions to read data which is not committed yet. This means if Transaction A is writing the data and Transaction B comes in , it will be able to read the data changed by Transaction A.  Regardless of the fact that it may receive Rollback command and all the changes made by Transaction A will be reverted.

This can cause dirty reads, lost updates & phantom reads. This is Isolation level with least restrictions and more concurrency .

Read Committed Isolation Level

Read committed isolation level doesn’t allow Transaction B to read data changed by Transaction A until it is committed. This reduces concurrency but prevents dirty . Phantom reads and repeatable reads are still a possibility.

SQL Server Read Committed is the default isolation level in it’s settings. However , you can change that. This is second least isolation level.

Repeatable Read Isolation Level

This SQL Server Isolation level behaves like read committed and also it will prevent any other transaction to modify the data which another transaction is reading until it is completely free. This eliminates dirty reads and repeatable reads  . Phantom reads are still a possibility.

Serializable Isolation Level

SQL Server Serializable Isolation level is the strongest isolation level .  If Transaction A is changing data on table Students and Transaction B comes to read the data from Transaction A  . SQL Server will not allow transaction B to read the data until Transaction is not complete. Similarly , If Transaction A is reading the data from table Students . Transaction B come to change the data in table Student. SQL Server will make sure Transaction A complete itself before allowing Transaction A to do anything. It will completely remove phantom reads!

Snapshot Isolation Level

Highest level of isolation level and least concurrency .  A transaction recognise only data which is committed before the start of the transaction. Any modification of the data after the transaction has started ,  is not visible to any statements of the currently executing transaction.  Row versions are created for each transaction and stored in temp db which handles these transactions. This  isolation level eliminates dirty reads, lost updates, repeatable reads and Phantom reads problem.

 

In the next article on Locks , we will see how SQL Server is managing all these Isolation levels and how it keeps data clean and allow multiple transaction to work at the same time.

Share your insights and feedback in comments if there is more to add here. Do share this with your friends on Social Media and oh you can follow us on twitter , facebook as well.

See you with our next article!
Cheers!
Happy Learning!

 

Author: Awais

Professional Data Analyst and Business Intelligence Developer with experience of delivering industrial projects for Supply Chain and Insurance Industry . Sharing all my experience and insights in databases and data warehousing and open to learn from fellows ! Happy Reading and Learning!

Leave a Reply