Hi Friends,
It’s a well-known fact that READ UNCOMMITTED Isolation Level in SQL Server allows dirty reads. In other words, if a transaction is running under READ UNCOMMITTED Isolation Level, it does not honor any locks held by any other transaction. Did is say ‘any’? Well there is a small exception or twist to this.
Let’s take an example:
In connection 1, execute the following query:
In connection 2 (another query windows), execute the following query:
This query will wait. Note that this workload is running under READ UNCOMMITTED Isolation Level and thus should pass through but is being made to wait and no dirty read is possible. Why?
Because the previous query/transaction is holding schema modification lock on the object and the current (waiting query) needs Schema Stability lock.
See it for yourself. Run the following query in a new query window:
And check the output.
So, it is not completely true that READ UNCOMMITTED Isolation Level does not honor locks. It does, and this is just one of those scenarios.



Isolation Level

  • A degree to which one transaction is isolated from another transactions.
  • Lower the transaction, increases concurrency and decreases data correctness.
  • Total 5 type of Isolation Level
    • READ UNCOMMITTED
      • Lowest level 
      • does not issue SHARED LOCK - means other transactions can see the data 
      • are not blocked by EXCLUSIVE LOCK - means they can read uncommitted modifications, this is same as using NOLOCK
      • This level produces "dirty read"
    • READ COMMITTED
      • Default level in SQL Server
      • cannot read the data which has been modified but not committed
      • it prevents "dirty read"
      • it produces "non repeatable read"
    • REPEATABLE READ
      • contains features of READ COMMITTED+ cannot modify data that has been read by other transaction until it gets completed
    • SERIALIZABLE
      • strongest level
      • contains features of REPEATABLE READ + cannot insert data if a table is in use by some other transaction (read/update)
    • SNAPSHOT
      • introduced in 2005
  • here is the comparison of basic 4 types of isolation levels how it behaves differently for different DML Operation





Session 1
(Within Transaction)

Session 2



SELECT
UPDATE /
DELETE
INSERT
READ UNCOMMITTED
SELECT
Yes
Yes
Yes
UPDATE / DELETE
Yes
No
Yes
INSERT
Yes
No
Yes






READ COMMITTED
SELECT
Yes
Yes
Yes
UPDATE / DELETE
No
No
Yes
INSERT
No
No
Yes






REPEATABLE READ
SELECT
Yes
No
Yes
UPDATE
No
No
Yes
INSERT
No
No
Yes






SERIALIZABLE
SELECT
Yes
No
No
UPDATE
No
No
No
INSERT
No
No
Yes



  • SET TRANSACTION LEVEL REPEATABLE READ. This t-sql command sets different transactional level
  • only 1 isolation level can be set at any point of time
  • Any isolation level can be switched to any isolation level during a transaction (exception: SNAPSHOT)
  • If isolation level is set inside stored procedure, then at the end of execution of SP, isolation level will be set to original what it was before execution of SP.
For more info about Locks

http://www.sqlteam.com/article/introduction-to-locking-in-sql-server



Isolation Level of MS SQL Server

Introduction

Before understanding the Isolation Level of Microsoft SQL Server, we must recall the first class of Relational Database Management system (RDBAMS) and the ACID property of RDBMS.

A
Atomic
C
Consistency
I
Isolation
This property means that each transaction is executed in isolation from others, and that concurrent transactions do not affect the transaction.
D
Durability

Types of Isolation Level in MS SQL Server

There are five type of Isolation level in MS SQL Server

1.    Read Committed (The Default Isolation Level of MS SQL Server)
2.    Read Uncommitted
3.    Repeatable Read
4.    Serializable
5.    Snapshot

Before further proceed with Isolation level we have to clear understanding about two things

Dirty Reads

This is when we read uncommitted data, when doing this there is no guarantee that data read will ever be committed.

Phantom Reads

This is when data that we are working with has been changed by another transaction since you first read it in. This means subsequent reads of this data in the same transaction could well be different.

To Check the Current Isolation Level

DBCC useroptions



Read Committed Isolation Level

Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

-- Query-1 of Session -1
BEGIN TRAN 
     UPDATE tbl_Test SET Col1 = 2; 
     WAITFOR DELAY '00:00:10'; 
ROLLBACK

--Query-2 of Session -2
SELECT * FROM tbl_Test;

In the above example   the Query-2 is waiting for Query-1 to complete. When the Query-1 is completed then the Query-2 retrieves records.

Read Uncommitted Isolation Level

In this Isolation level, a transaction can read the data which is modified by some other transactions but still not committed. This Isolation level do not issue shared locked to prevent data modification which is read by some other transactions. Also it is not issue exclusive locks to prevents transactions from reading data which is modified from other transaction by not committed yet. It may results in problem like dirty read, lost updates, phantom reads etc. It is the least restrictive Isolation level.

-- Query-1  of Session - 1
BEGIN TRAN 
     UPDATE tbl_Tests SET Col1 = 2; 
     WAITFOR DELAY '00:00:10'; 
ROLLBACK

-- Query-2 of Session - 2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT * FROM tbl_Tests;

Here in this example Query-2 is not waiting for Query-1. Query-2 returns records without waiting for Query-1 my cause of Dirty data.

Repeatable Read Isolation Level

This isolation level is higher than the previous two mention isolation level and it does not allows any transaction to read a data that is being modified by some other transaction but not committed yet. Also it doesn't allow any transaction to modify a data if it is being read by some other transaction until the transaction reading that data complete its operation.  This way it eliminates the dirty read and Repeatable reads problem but it doesn't eliminates the Phantom reads.

 --Query - 1  of Session - 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
BEGIN TRAN 
     SELECT * FROM tbl_Tests; 
     WAITFOR DELAY '00:00:10'; 
     SELECT * FROM tbl_Tests;   
ROLLBACK
--Query - 2  of Session - 2
UPDATE tbl_Tests  SET Col1 = -1

In the above example Query-1 returns the same data for both selects even though you ran a query to modify the data before the second select ran. This is because the Update query (Query-2) was forced to wait for Query-1 to finish due to the exclusive locks that were opened as you specified Repeatable Read.
Serializable Isolation Level

This Isolation level do not allow any transaction to read the data unless the other transactions completed their data modification operation. Also it doesn't allow other transactions to modify the data until the current transaction completed its read operation. This isolation level allows a transaction to acquire a read lock (if only read operation) or write lock (for insert,delete,update) for the entire range of records that the transaction is going to affect.

--Query - 1 of Session -1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
BEGIN TRAN 
     SELECT * FROM tbl_Tests; 
     WAITFOR DELAY '00:00:10'; 
     SELECT * FROM tbl_Tests;
ROLLBACK

--Query - 2 of Session -2
INSERT INTO tbl_Tests(Col1,Col2,Col3) 
VALUES (100,100,100);

In the above example we will see that the insert in Query-2 waits for Query-1 to complete before it runs eradicating the chance of a phantom read.
If we change the isolation level in Query-1 to repeatable read, we will see the insert no longer gets blocked and the two seleclt statements in Query-1 return a different amount of rows.
Snapshot Isolation Level

In this isolation level, a transaction recognise only data which is committed before the start of the transaction. Any modification of the data after the transaction is begin,  is not visible to any statements of the currently executing transaction. It is like a snapshot of data, given to each transaction. It is done with the help of row version where a separate version of each modified row is maintain in the temp db database dedicated to the transactions. This  isolation level eliminates dirty reads, lost updates, repeatable reads and Phantom reads problem.

ALTER DATABASE myDb 
SET ALLOW_SNAPSHOT_ISOLATION ON;


Transaction Isolation Level


The isolation level is a very important part of SQL Server. This article gives you a scenario related to isolation level.
The problem of the wrong isolation level is mentioned bellow:
1.     Dirty Reads
Dirty reads occur when one transaction reads data written by another, uncommitted, transaction.
2.     Non-repeatable Reads
Non-reputable reads occur when one transaction attempts to access the same data twice and a second transaction modify the data between the first transaction's read attempts. This may cause the first transaction to read two different values for the same data, causing the original read to be non-repeatable
3.     Phantom Reads
Phantom reads occur when one transaction accesses a range of data more than once and a second transaction inserts or deletes rows that fall within that range between the first transaction's read attempts. This can cause "phantom" rows to appear or disappear from the first transaction's perspective.



Setting the transaction isolation level for a connection allows a user to specify how severely the user's transaction should be isolated from other transactions. For example, it allows you to specify whether transaction A is allowed to make changes to data that have been viewed by transaction B before transaction B has committed.
The five SQL Server isolation models are:
1.    Read Committed Isolation Model
this is SQL Server's default behavior. In this model, the database does not allow transactions to read data written to a table by an uncommitted transaction.
2.     Read Uncommitted Isolation Model

this model offers essentially no isolation between transactions. Any transaction can read data written by an uncommitted transaction.
3.    Repeatable Read Isolation ModelThis model goes a step further than the Read Committed model by preventing transactions from writing data that was read by another transaction until the reading transaction completes.
4.    Serializable Isolation Model

This model uses range locks to prevent transactions from inserting or deleting rows in a range being read by another transaction.
5.    Snapshot Isolation Model

This model also protects against all three concurrency problems, but does so in a different manner. It provides each transaction with a "snapshot" of the data it requests. The transaction may then access that snapshot for all future references, eliminating the need to return to the source table for potentially dirty data.
The isolation level and table/row level locking are mentioned bellow:


Isolation Level
Table-Level Locking
Row-Level Locking
Read Uncommitted Isolation Model 
Dirty reads, non-repeatable reads, and phantom reads possible
Dirty reads, non-repeatable reads, and phantom reads possible
Read Committed Isolation Model
Non-repeatable reads and phantom reads possible
Non-repeatable reads and phantom reads possible
Repeatable Read Isolation Model 
Phantom reads not possible because entire table is locked
Phantom reads possible
Serializable Isolation Model 
None
None





To set the isolation level

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SNAPSHOT





Hope the article is quite informative and thanking you to provide your valuable time on it.