Isolation Levels in Sql Server
Different Isolation levels available in SQL Server are read uncommitted, read committed, snapshot, serializable and repetable read. Out of this read committed is the default isolation level. The isolation levels define the type of locks acquired on read operations. Read uncommitted is the lowest isolation level and serializable is the highest isolation available. Learn what is meant by Isolation level?
What is meant by Isolation level?
Isolation level determines the degree to which a data modification is isolated from the other.
For Eg : Assume there is a table Employee. The user A modified the 5th row of the employee table and the same time user B executes a SELECT statement against the table. Now what if user A transaction is reverted back and original value is reverted into row 5. Here the SELECT statement returned by user B will return bad data as data returned is not the actual data currently stored in row 5.
Thus isolation level helps to eliminate bad data. Before exploring those we will see what are the common concurrency issues that will results into bad data.
1. Dirty Read: Dirty read occurs when one transaction reads data that is written by another uncommitted transaction. The problem with dirty read is that transaction that modified the data never commits, leaving the original transaction with "dirty" data.
2. Non repeatable Reads: Non repeatable read occurs when one transaction reads the same data multiple times and a second transaction modifies the data in between first transaction read attempts. This will results first transaction to read different values for the same data, causing the original read to be non repeatable.
3. Phantom Reads: Phantom reads occurs when one transaction accesses a range of data more than once and a second transaction inserts or deleted the rows that falls within the range of first transaction read attempts. This result "Phantom" rows to appear or disappear from the first transaction perspective.
SQL server 5 different isolation models. They are
1. Read Uncommitted: This is the lowest isolation level. This isolation level isolates transactions and activities to ensure that physically corrupted data is never read. This level allows dirty read, nonrepeatable reads and phantom reads.
2. Read Committed: This is the default isolation level for Sql server. This isolation level does not permit dirty reads but it allows non repeatable reads and phantom reads.
3. Repeatable Read: This isolation level does not permit dirty read or non repeatable reads but it allows phantom reads.
4. Serializable: This is the highest isolation level. This level ensures that transactions and statements are completely isolated from each other. It does not allows dirty reads, non repeatable reads and phantom reads.
5. Snapshot: In SQL Server 2005 Microsoft implemented row versioning as a method of gaining access to data, known as Snapshot Isolation Level is a means for read transactions to not block write transactions. Snapshot isolation works by maintaining versions of modified data in a store (version store) in TEMPDB.
We can set the appropriate isolation level for an entire SQL Server session by using the SET TRANSACTION ISOLATION LEVEL statement. Here is the syntax:
SET TRANSACTION ISOLATION LEVEL
{
READ COMMITTED| READ UNCOMMITTED
| REPEATABLE READ | SERIALIZABLE
}
We can use DBCC USEROPTIONS command to determine the Transaction Isolation Level currently set. This command returns the set options that are active for the current connection. This is the example:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
DBCC USEROPTIONS
GO