What are the various Isolation levels?

Question Posted / sundar

2 Answers
10754 Views
HP, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

What are the various Isolation levels?..

Answer / aaa

Isolation is a property that enables an operation to be
hidden, until its completion, from all the other similar
operations. This property is one of the Acid properties on
the Data Base Management System. However, isolation is the
most relaxed acid property. The management system has to
apply a lock of a limit to which the operation can be
accessed. These limits could be up to various levels. These
levels are called the isolation levels. There are 4 main
types of isolation levels: Serializable: at this level, all
the transactions occur in an isolated fashion; Repeatable
Read: when data cannot be changed; Read Committed: when
data can be modified by some other transaction; Read
Uncommitted: at this level, the changes made by one
transaction can be seen by another transaction. There are a
lot of weaknesses in the way each of the levels have been
defined.

Is This Answer Correct ?

8 Yes

0 No

What are the various Isolation levels?..

Answer / phani kiran sai

Isolation levels in SQL SERVER
1. READ UNCOMMITTED isolation level.
This kind of isolation level is used when we have the most
need for HIGHER CONCURRENCY.
Limitations:
Dirty pages
Lost updates
Phantom reads
Non-repeatable reads.

2. READ COMMITTED isolation level.
This is default level set in SQL SERVER. It prevents
transactions to read data if some other transaction is doing
some update operation on the data as a result eliminating
dirty reads.
It prevents reading of uncommitted data.
Limitations:
Low level of concurrency
Lost updates
Advantages:
Eliminates Dirty reads.

3. REPEATABLE READ isolation level.
It does not release the shared lock once the transaction
starts for reading data.
Transaction cannot read data that is modified by other
transaction and not committed.

Limitations:
Lower concurrency
Phantom reads

Advantages:
Eliminates Dirty Reads
Eliminates Lost updates
Eliminates Non-repeatable reads.

4. SERIALIZABLE isolation level.
This is the highest level of isolation, as a result a very
low concurrency rate.
Can not read data if other transaction are performing update
operations on data that is not committed yet.
No other transaction can perform update operation until
current transaction completes its read operation.
It performs RANGE LOCK based on filters used to get the data
i.e it locks current records and also the new records that
fall under current filter condition.
Limitation:
Low concurrency

Advantages:
Eliminates Dirty reads
Eliminates Lost updates
Eliminates Non-repeatable reads
Eliminates Phantom reads

5. SNAPSHOT isolation level.
It implements ROW VERSIONING to isolate data for each
transaction i.e it will keep separate version fo reach
modified row in tempdb.
Limitation:
Low performance due to versioning in tempdb.

Advantage:
Eliminates Dirty reads
Eliminates Lost updates
Eliminates Non-repeatable reads
Allows multiple updates by versioning.

6. CHAOS isolation level.
Same as READ UNCOMMITTED
Permits viewing uncommitted changes of other transaction
Rollback is not supported.

7. UNSPECIFIED isolation level.
When an isolation level of any transaction cannot be
determined, it is knows as unspecified isolation level.
ODBCTransaction, when an user forgets to set an isolation
level for such transactions, it will execute according to
isolation level associated by ODBC driver

Is This Answer Correct ?

2 Yes

0 No

Post New Answer