Isolation levels in SQL Server

ISOLATION LEVELS IN SQL

1. What is Isolation levels?

The isolation level that your transaction runs in determines how sensitive your application is to changes other users’ transactions make, and consequently, how long your transaction must hold locks to protect against these changes.

2. Types of Isolation level.

    1. READ COMMITTED
    2. READ UNCOMMITTED
    3. REPEATABLE READ
    4. SERIALIZABLE
    5. SNAPSHOT

3. Why we need isolation level?

When we are looking at ways of ensuring atomicity and consistency in your database by ensuring transactional integrity. When may want to avoid locks, or ensure problems such as lost updates or dirty reads cannot occur, or otherwise ensure the quality of your data. Transactional isolation is a narrow but somewhat complex subject that might be ideal for your needs.

4. Examples

Default Script for all the examples:

NOTE: Before executing each example, reset the Emp table values by executing the above script.

Isolation level in SQLa. Read committed

  • Default transaction isolation is the Read Committed.
  • In a select query, it will take only committed values of the table. If any transaction is opened and incomplete on the table in other sessions then the select query will wait until no transactions are pending on the same table.
  • Now start 2 session side by side and run below query.
  • Session 1 Query & Output:

Isolation level in SQL

  • Session 2 Query & Output:

Isolation level in SQLb. Read uncommitted

  • If any table is updated (insert or update or delete) under a transaction and the same transaction is not completed that is not committed or rollbacked then uncommitted values will display (as Dirty Read) in the select query of “Read Uncommitted” isolation transaction sessions. There will not be any delay in select query execution because this transaction level does not wait for committed values on the table.
  • Now start 2 session side by side and run below query.
  • Session 1 Query & Output:

Isolation level in SQL

  • Session 2 Query & Output:

Isolation level in SQLc. Repeatable read

  • Select query data of table that is used under a transaction of isolation level “Repeatable Read” cannot be modified from any other sessions until the transaction is completed.
  • Now start 2 session side by side and run below query.
  • Session 1 Query & Output:

Isolation level in SQL

  • Session 2 Query & Output:

Isolation level in SQLd. Serializable

  • Serializable Isolation is similar to Repeatable Read Isolation but the difference is it prevents Phantom Read. This works based on range lock. If the table has index then it locks records based on index range used in the WHERE clause (like where ID between 1 and 3). If the table does not have an index then it locks the complete table.
  • Now start 2 session side by side and run below query.
  • Session 1 Query & Output:

Isolation level in SQL

  • Session 2 Query & Output:

Isolation level in SQLe. Snapshot

  • Snapshot isolation is similar to Serializable isolation. The difference is Snapshot does not hold a lock on the table during the transaction so 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.
  • Now start 2 session side by side and run below query.
  • Session 1 Query & Output:

Isolation level in SQL

  • Session 2 Query & Output:

Isolation level in SQL

The following two tabs change content below.
Yatin Parmar
I am developing and managing projects at DEV IT. I like to explore database technology and provide necessary solutions as and when required.
Yatin Parmar

Latest posts by Yatin Parmar (see all)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>