Isolation in SQL SERVER

ISOLATION: Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions.

 Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed.

•        Types of Isolation

Ø  Read Uncommitted

Ø  Read Committed

Ø  Repeatable read

Ø  Serializable

Ø  SNAPSHOT

READ UNCOMMITTED: In this type of isolation database engines does not issue shared locks, so uncommitted data could be read resulting into dirty read. Also data could be read effected by other transaction while the current transaction is running resulting into repeatable.

READ COMMITTED: A query in the current transaction cannot read data modified by another transaction that has not yet committed, thus preventing dirty reads. However, data can still be modified by other transactions between issuing statements within the current transaction, so non-repeatable reads and phantom reads are still possible. The isolation level uses shared locking or row versioning to prevent dirty reads, depending on whether the READ_COMMITTED_SNAPSHOT database option is enabled. Read Committed is the default isolation level for all SQL Server databases.

REPEATABLE READ: A query in the current transaction cannot read data modified by another transaction that has not yet committed, thus preventing dirty reads. In addition, no other transactions can modify data being read by the current transaction until it completes, eliminating non-repeatable reads. However, if another transaction inserts new rows that match the search condition in the current transaction, in between the current transaction accessing the same data twice, phantom rows can appear in the second read.

SERIALIZABLE: A query in the current transaction cannot read data modified by another transaction that has not yet committed. No other transaction can modify data being read by the current transaction until it completes, and no other transaction can insert new rows that would match the search condition in the current transaction until it completes. As a result, the Serializable isolation level prevents dirty reads, non-repeatable reads, and phantom reads. However, it can have the biggest impact on performance, compared to the other isolation levels.

SNAPSHOT: A statement can use data only if it will be in a consistent state throughout the transaction. If another transaction modifies data after the start of the current transaction, the data is not visible to the current transaction. The current transaction works with a snapshot of the data as it existed at the beginning of that transaction. Snapshot transactions do not request locks when reading data, nor do they block other transactions from writing data. In addition, other transactions writing data do not block the current transaction for reading data. As with the Serializable isolation level, the Snapshot level prevents dirty reads, non-repeatable reads and phantom reads.

	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	û	û	û
Read Committed	ü	û	û
Repeatable Read	ü	ü	û
Serializable	ü	ü	ü
Snapshot	ü	ü	ü

Example:

IF OBJECT_ID('Employee') is not null

BEGIN

DROP TABLE dbo.Employee

END

--Creating sample table

CREATE TABLE dbo.Employee(ID int,Name Varchar(50),Salary Int)

--Inserting data

INSERT INTO dbo.Employee(ID,Name,Salary) VALUES ( 1,'Daniel',1000)

INSERT INTO dbo.Employee(ID,Name,Salary) VALUES( 2,'Mike',2000)

INSERT INTO dbo.Employee(ID,Name,Salary) VALUES( 3,'Frank',3000)  

SELECT * FROM dbo.Employee

SELECT Salary FROM dbo.Employee WHERE ID=1

Links for videos to see the different isolations Link1, Link2

This article refers to in depth information of Robert Sheldon article