---

In order to understand different isloation levels in SQL Server, we should first understand what is an Isolation level.

** As we must be aware that for any transaction in a database, 4 primary attributes are ensured which we remember as ACID (Atomicity, Consistency, Isolation, Durability) **

For every transaction in database, the database guarentees isolation. That means every transaction is isolated from each other and SQL server provides 5 diffferent levels of isolation for the transactions.

1. READ UNCOMMITED.
2. READ COMMITTED (locking).
3. READ COMMITTED (snapshot).
4. REPEATABLE READ.
5. SERIALIZATION.

You can change the level of isolation that a particular connection is operating in by using the SET TRANSACTION ISOLATION LEVEL command. Keep in mind that the SET command applies only to your current connection, and every time you make a new connection (or open a new window in the Query Analyzer), you’ll be back in the default isolation level.

---

 

READ UNCOMMITTED:

Uncommitted Read, or dirty read, lets a transaction read any data currently on a data page, whether or not that data has been committed. For example, although another user might have a transaction in progress that has updated data, and that transaction is holding exclusive locks on the data, your transaction can read the data anyway, and possibly take further actions based on the values you read. The other user might then decide to roll back his or her transaction, so logically, those changes never occurred. Although this scenario isn’t desirable, with Uncommitted Read you won’t get stuck waiting for a lock, nor will your reads acquire share locks that might affect others.

For example, if you open two instances of SSMS.

In instance 1, open a transaction and update some data in a table and do not commit the transaction.

Let say, the Employee with ID=1 had a salary of 500 initially and we update it to 1000 in a transaction without commiting that transaction.

BEGIN TRAN
UPDATE  dbo.Employee 
SET     EmpSalary = 1000
WHERE   EmpID = 1; -- In Instance 1

Now, if we read the salary in instance 2 , with transaction Isolation level as READ UNCOMMITED, we will be able to read 1000 instead of 500. Which is also called as dirty read as that update statement in a transaction is not committed yet.

If we rollback that transaction in instance 1, the actual value in the table will be 500, but in instance 2 we have read a dirty read with value 1000.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
GO
SELECT EmpID, EmpName, EmpSalary
FROM dbo.Employee
WHERE EmpID = 1 ; -- In Instance 2 <This will fetch Salary=1000 for us>.

---

 

READ COMMITTED (Snapshot):

Read Commited will read the last consistent row of the table we are quering. This ensures that dirty reads are not retreived. With Read committed (snapshot), if we update the salary to 1000 from 500 in a transaction in SSMS instance 1.

And try to read the value of salary for EMP with Id=1, it will fetch us the last consistent row version , which is 500.

ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET NOCOUNT ON
GO
BEGIN TRAN
UPDATE  dbo.Employee 
SET     EmpSalary = 1000
WHERE   EmpID = 1 ; --In Instance 1

Now in Instance 2, if we fetch the salary of employee with empId=1, It will return us 500 and not 1000.

SELECT  EmpID, EmpName, EmpSalary
FROM    dbo.Employee 
WHERE   EmpID = 1; --In Instance 2 < will fetch 500>

---

 

READ COMMITTED ( locking):

In Read commited (locking), if we do the same operation as above, it wil still prevent the dirty read by locking the select query untill the transaction is commited.

ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON
GO
BEGIN TRAN
UPDATE  dbo.Employee 
SET     EmpSalary = 1000
WHERE   EmpID = 1 ; --In Instance 1

In instance 2, if we read the Salary of empID=1, the query will hang and wait for the transaction to complete.

SELECT  EmpID, EmpName, EmpSalary
FROM    dbo.Employee WITH (READCOMMITTEDLOCK)
WHERE   EmpID = 1; -- In Instance 2< Read query will hang >

---

 

REPEATABLE READ:

If you want the read operations to be repeatable, choose this isolation level. The Repeatable Read isolation level adds to the properties of Committed Read by ensuring that if a transaction revisits data or if a query is reissued, the data will not have changed. In other words, issuing the same query twice within a transaction won’t pick up any changes to data values that another user’s transaction has made. No other user can modify the data that your transaction visits as long as you have not yet committed or rolled back your transaction.

In Read committed scenario , if we have two instances. In intance 1, we read same value (salary) twice from a table with a delay in execution in one transaction scope. In instance 2, we update that value(salary) in another transaction scope.

Note that, In instance 1 , we will be getting two different values of salary in the same transaction. One with the original value and another which is updated in second instance.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
SET NOCOUNT ON
GO
BEGIN TRAN
SELECT  EmpID, EmpName, EmpSalary
FROM    dbo.Employee 
WHERE   EmpID = 1; --fetches Salary=500
WAITFOR DELAY '00:00:15'
SELECT  EmpID, EmpName, EmpSalary
FROM    dbo.Employee 
WHERE   EmpID = 1; -- fetches Salary=1000
COMMIT

It fetched, Salary=1000 because we updated salary in Instance 2 as below within those 15 seconds for whcih Instance 1 was waiting.

BEGIN TRAN
UPDATE  dbo.Employee 
SET     EmpSalary = 1000
WHERE   EmpID = 1 ; --In Instance 2
COMMIT

Now to avoid these different reads in a transaction, we can use Repeatable Read.

With Repeatable Read , in a transaction it is ensured that consistent values are being fetched irrespective of updates in another transactions.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET NOCOUNT ON
GO
BEGIN TRAN
SELECT  EmpID, EmpName, EmpSalary
FROM    dbo.Employee 
WHERE   EmpID = 1;-- Instance 1 <fetches 500>
WAITFOR DELAY '00:00:15'
SELECT  EmpID, EmpName, EmpSalary
FROM    dbo.Employee 
WHERE   EmpID = 1;-- Instance 1 <fetches 500>
COMMIT

Now, within that delay period of 15 seconds , if in Instance 2 , we update the salary to 1000, the Instance 1 will still fetch the older consistent value of 500 instead of 1000 because of Repeatable Read.

BEGIN TRAN
UPDATE  dbo.Employee
SET     EmpSalary = 1000
WHERE   EmpID = 1;-- In Instance 2
COMMIT

 

** But if you insert a new row in Instance 2 , and do a select * from Employee two times with a delay, In first statement , it will read all the rows of the table and after the insert is done, it will also read all the rows including the newly inserted row. **

Hence , Serializable is the best way for Isolation which also prevents dirty read in case new rows are added.

---

SERIALIZABLE:

SERIALIZABLE has all the features of READ COMMITTED, REPEATABLE READ but also ensures concurrent transactions are treated as if they had been run in serial. This means guaranteed repeatable reads, and no phantom rows. Be warned, however, that this (and to some extent, the previous two isolation levels) can cause large performance losses as concurrent transactions are effectively queued. Here’s the phantom rows example used in the previous section again but this time using the SERIALIZABLE isolation level:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET NOCOUNT ON
GO
BEGIN TRAN
SELECT  EmpName
FROM    dbo.Employee ;-- Instance 1 <Returns all rows with count 5>
WAITFOR DELAY '00:00:15'
SELECT  EmpName
FROM    dbo.Employee ;-- Instance 1 <Returns all rows with count 5>
COMMIT

Now , if in the timeframe of 15 seconds if we insert a new row in the employee table as below in Instance 2, The Insert will wait for the transaction in Instance 1 to get committed and then will insert the row in the table.

BEGIN TRAN
INSERT INTO dbo.Employee VALUES (2, 'Phantom Employee 1', 2000)
COMMIT ;-- Instance 2 <creating a new record (row 6)>

 

---