Simplifying IT, Empowering Businesses

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

a. 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:

• Session 2 Query & Output:

b. 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:

• Session 2 Query & Output:

c. 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:

• Session 2 Query & Output:

d. 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:

• Session 2 Query & Output:

e. 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:

• Session 2 Query & Output: