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 Model 

This 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.

 

 

 

Posted by: MR. JOYDEEP DAS