Transaction Isolation Level in SQL Server

# Transaction Isolation Level in SQL Server

Transaction Isolation levels in SQL Server controls how locks are will be placed
on data while running different concurrent sessions of queries. This concept is
very important, as it directly influences how and what data will be returned and
saved on different parallel operations, typically row versioning.

Before drilling into isolation level, first lets check what is the present
isolation level, use "DBCC useroptions" and check "isolation level". By default
"Read Committed" is the isolation level for SQL Server. We can always change
this level to different one using "SET TRANSACTION ISOLATION LEVEL isolation
name" for a particular connection/transaction.

There are different types of isolation level as narrated below:

- [READ UNCOMMITTED](#read-uncommitted)
- [READ COMMITTED](#read-committed)
- [REPEATABLE READ](#repeatable-read)
- [SERIALIZABLE](#serializable)
- [SNAPSHOT](#snapshot)

### READ UNCOMMITTED

These transactions do not use Shared locks on the data, other transaction can go
for exclusive locks and can change data. Along side these transactions are not
blocked from reading data which is being changed by other transactions (with exclusive locks).

### READ COMMITTED

These transactions issue Shared locks on the data, other transaction cannot go
for exclusive locks and cannot change data. Along side these transactions are
blocked from reading data which is being changed by other transactions(with
exclusive locks).

### REPEATABLE READ

These transactions return same data for same query if used multiple times in the
same transaction. That means, Shared locks will be placed on the data through
out the transaction till it completes. It differs from read committed
transaction level, in which we cannot guarantee same data for same query(if used
multiple times) in a transaction. One important thing needs to be remembered is
that this isolation level never prevents new data inserts, but it only restricts
any new modifications/deletions on the existing data.

### SERIALIZABLE

This isolation level extends repeatable read, so that no new data inserts are
allowed until the present transaction completes. This is accomplished by placing
range locks on the data under select statements.

### SNAPSHOT

These transaction creates a snapshot of data which is being read and then use
the same snapshot for consecutive reads. In this way, new data can be
inserted/modified during a snapshot isolation by other transactions.

---

[Source](http://www.intstrings.com/ramivemula/articles/transaction-isolation-level-in-sql-server/)