Transaction Isolation levels in SQL Server explained.
# 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/)