Isolation levels in SQL Server

I will try to explain Isolation Levels in simple language in this blog.

Isolation level defines how a transaction must be isolated from resources or data modifications made by other transactions.

Before we talk about different isolation levels we will learn few basic concepts

  • Dirty read: Reading some which is not committed yet. This uncommitted data may be committed or rolled back.
  • Non-repeatable read: When a transaction tried to read data multiple times and if the data is updated by different transaction between these reads this scenario will occur. Query can be different;
    • Example: When I read availability of a ticket in first read and fails to allocate if it is already allocated by a different transaction.
  • Phantom reads: This is similar to non-repeatable read but the query is same but returned data will be different.
    • Here the data being pulled may not change but there may be additional data which satisfy the same query resulting difference in end result.

 

Read Uncommitted: In this Isolation level allows transaction to read the data which is not yet committed. This may result in some uncommitted data which may not be accurate

  • Read Committed: This is the default isolation level and this Isolation level prevents the transaction from reading data which are modified by some other transactions but still not committed. This addresses Dirty reads.
  • Repeatable Read: In addition to Read-Committed this isolation does not allow any transaction to modify the data which was read by some other transaction until reading the data completes its operations. Thisaddresses non-repeatable reads.
  • Serializable: In addition to repeatable read, it also acquires read lock on entire range of records and does not allow any insert and delete operations. This addresses phantom reads.
  • Snapshot: This transactions reads the data which is committed before the begin of transaction. Any modification to data after the transaction begin does not visible to the transaction and it is maintained using row-versioning.

 

My personnel choice would be to use the default isolation level Read-Committed in regular situations and use Serializable when data needs to be very accurate but we can afford minor performance glitch.
Advertisements
This entry was posted in knowledge, SQL, SQL Server, Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s